[SOLVED] SUBSTITUTE drop downs x2 variables (nested?)

Discuss the spreadsheet application

[SOLVED] SUBSTITUTE drop downs x2 variables (nested?)

Postby pherriot » Fri May 31, 2019 10:37 am

Hi People.
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.
Attachments
Helper Interface 1.1.ods
(16.52 KiB) Downloaded 17 times
Interface 1.1.JPG
Last edited by robleyd on Thu Jul 11, 2019 2:32 pm, edited 2 times in total.
Reason: Add green tick
OpenOffice 3.1 on Windows Vista
pherriot
 
Posts: 54
Joined: Wed Mar 27, 2019 1:06 pm

Re: SUBSTITUTE drop downs x2 variables (nested?)

Postby keme » Fri May 31, 2019 11:23 am

Perhaps this works for you (nested, as you suggested without doing ;-) ):
Code: Select all   Expand viewCollapse view
=SUBSTITUTE(SUBSTITUTE(D4;E3;A2);F3;B4)
User avatar
keme
Volunteer
 
Posts: 3284
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: SUBSTITUTE drop downs x2 variables (nested?)

Postby pherriot » Fri May 31, 2019 12:31 pm

Hi.
Unless Im missing something it only returns the first variable......

=SUBSTITUTE(SUBSTITUTE(D4;E3;A2);F3;B4) returns '#$Inbound Admin Tasks'.
...expected return was data from Sheet "Inbound Admin Tasks" at named range "Disputed Reads" and so built function would be '#$Inbound Admin Tasks'.IAdisputedreads
OpenOffice 3.1 on Windows Vista
pherriot
 
Posts: 54
Joined: Wed Mar 27, 2019 1:06 pm

Re: SUBSTITUTE drop downs x2 variables (nested?)

Postby pherriot » Fri May 31, 2019 12:43 pm

Hi Keme.
Sorry, the last cell reference was bad. B4 becomes B2.
Howewever there is still a problem........
The second variable does not build the link to access the cell data like I hoped it would......here is the resulting string '#$Inbound Admin Tasks'.(Iadisputedreads)

...that should access the Inbound Admin Tasks sheet at named range Iadisputedreads
...and I hoped to see the follwing dislayed:
"Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info Info "

...any help appreciated people.
Cheers.
P
Attachments
Helper Interface 1.1.ods
(16.59 KiB) Downloaded 16 times
OpenOffice 3.1 on Windows Vista
pherriot
 
Posts: 54
Joined: Wed Mar 27, 2019 1:06 pm

Re: SUBSTITUTE drop downs x2 variables (nested?)

Postby pherriot » Fri May 31, 2019 1:51 pm

.....please help me its almost there.

Ive resigned to using only one variable, the requested data is all called in to the correct cell but it also displays all in the drop down too (see screenshot):

...this is obviously not desired.
Can I stop the data being loaded into the drow down?
Attachments
Helper Interface 1.1.ods
Udated version
(22.03 KiB) Downloaded 17 times
Interface 1.3.JPG
Info displays in drop down and Display cells
OpenOffice 3.1 on Windows Vista
pherriot
 
Posts: 54
Joined: Wed Mar 27, 2019 1:06 pm

Re: SUBSTITUTE drop downs x2 variables (nested?)

Postby pherriot » Thu Jul 11, 2019 2:23 pm

Solved via other topic:
viewtopic.php?f=9&t=98271
OpenOffice 3.1 on Windows Vista
pherriot
 
Posts: 54
Joined: Wed Mar 27, 2019 1:06 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests