I get to assist my Agents in their job this time! yuuhuu.
In order to provide a mechanism from within Libreoffice Calc for Agents to access helper texts (copy and paste notes for replying to customers), I need to organise what is a huge excel file with no formatting.
It occurred to me to build a small and easily maintainable interface to this huge file by using a combination of SUBSTITUTE with drop down menus and named ranges.
So when using 1 variable it works fine:
=SUBSTITUTE(D4;E3;A2) returns the expected built string "'#$'Inbound Admin Tasks'.[subjectarea]" with the variable [inboundoutbound] being replaced by "Inbound Admin Tasks"
...however all I get is "Err:504" when attempting to replace 2 variables to access not only the sheet but location in sheet....
=SUBSTITUTE(D4;E3;A2;D4;F3;B4) returns Err:504
Im expecting to be virtually slapped for badly missusing this function
Agent steps:
(Drop Down Menu) Choose Inbound or Outbound and Area (A2)
(Drop Down Menu) Choose Subject/Sub-Area (B2)
(SUBSTITUTE handles selections from drop down and accesses page and names range) (C2)
..I just cant get it to accept the second variable which accesses the names range in the document, which leads me to the next problem that linking to the names range from the drop down would mean using the "actual named range" which is ugly (i.e. IAdisputedreads = Inbound Admin + Disputed Reads)...and anyway, Calc wont allow me to use the named range "IAdisputed reads".....
Perhaps Im wrong to use named ranges and should be using cell references? Or prehaps Im abusing SUBSTITUTE and should be using another function?
..any help appreciated.
Cheers,
P.
):