Ask Your Question

text string as a variable of Array in formulas

asked 2015-09-16 12:47:04 +0200

plumabokkie gravatar image

updated 2015-09-16 19:32:27 +0200

Alex Kemp gravatar image

Version: - any Language - Calc Windows - any Language

My question is how can we use this text string as a variable ?

i.e. in a formula like: =VLOOKUP(SearchCriterion; Array; Index; SortOrder)

Array could be a referanced cell like D1... where D1 contains the text "='file:///FileName.ods'#$Sheet1.A1"

thus making it possible to change the "Database / Array" globally in ... cell D1... without having to change each formula.

How could this could apply to "sheet name" or to "range NAMES" i.e. change the range NAMES in ... cell D1 ... and all VLOOKUP formulas will point to a new sheet or range

Thanks, Sergio

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2015-09-16 16:04:41 +0200

pierre-yves samyn gravatar image


Have a look at the INDIRECT function


edit flag offensive delete link more

answered 2015-09-16 19:44:51 +0200

plumabokkie gravatar image

Brilliant Thank you ...

for others who may find this helpfull the formula I used was ...

=VLOOKUP($B12;INDIRECT("'" & $AH$1 & "'.$A$3:$O$45");6)

where $AH$1 has a sheet name ... kept the range same on all future sheets that will have data as $A$3:$O$45

used a data validity to store sheet names ... so next time a sheet name is added & changed in $AH$1 ... the data displayed will chnage to that reference.

Thank you ! Sérgio

edit flag offensive delete link more

answered 2015-09-17 07:12:16 +0200

karolus gravatar image

updated 2015-09-17 07:13:30 +0200


....and additional the DDE-funktion

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-09-16 12:47:04 +0200

Seen: 206 times

Last updated: Sep 17 '15