Text string as a variable of Array in formulas

[plumabokkie]

Version: 5.0.1.2 - 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

[PYS]

Hi

Have a look at the INDIRECT function

Regards

[plumabokkie]

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

[karolus]

Hallo

…and additional the DDE-funktion