typ formula as text and use afterwards as function

[Metwie]

Hi all,

I want to make a dynamic section in a formula/link.

for example, I want a Vlookup/ datalink to this file: C:\Users\*****\Documents\[January.xlsx]Sheet2'!$C$27

but I want to have the name of the user + the month variable to easy change ( I dont want to go through edit links, as this is not function anymore)

so what I would like to have is to have the destination in a separate cell with the user & month linked to dropdown lists, all this I know how to do to display the text as I want to have the location.

but when I would like to really use this link it doesn't work. for example the text with dynamic function C:\Users\*****\Documents\[January.xlsx]Sheet2'!$C$27 is typed in cell A1 and in cell A2 I really want to have the value, so I tought to type =A1 or ='A1'. but all this doesn't work.

does somebody has an idea how this can be solved?

thanks!

[npatridge]

I'm not sure I exactly follow what you're asking, but try:

=INDIRECT(A1)

[Metwie]

Thanks Npatridge,

to make it more clear I would like to type this in A1 -> 'C:\Users\*****\Documents\[January.xlsx]Sheet2'!$C$27 and in A2 I just want to have the real link to that cell so it should take forumula ='C:\Users\*****\Documents\[January.xlsx]Sheet2'!$C$27 but linked to the text in A1 so I can make dynamic fields in this.

when I try the indirect formula I have #ref! error

[npatridge]

Can you post a sample sheet for me?

[Jonmo1]

Unfortunately, INDIRECT doesn't work on closed workbooks.

You'd have to install the Morefunc.dll addin (search google and sift through a bunch of junk to find it)

[Metwie]

Yes that's the problem formula works only when all files are open.

attached is the sample file ( with just 1 simple variable).

is there any other option than downloading the additional freeware?

thanks