I'm having an issue and I can't figure out how to look it up to get a
proper answer, nor can I find an answer in the books that I have, so I
figured I'd try here -- I have a spreadsheet that I am trying to link
to other spreadsheets. In one column, I have data that tells me what
the other spreadsheets are that need to be linked to. I want to look
up a few different cell values from those other spreadsheets to
manipulate, and I want to use it by using the variable in the column
to look up the data.

For instance, the column is F, and if the value for F3 is "File01" I
want to make J3 do the following function, but with the "File01"
dynamic based on what is in column F. The function is:

=[File01.xlsx]Template!$M$2

Any help would be appreciated!

Try using the indirect function. If you can use simple text formulas
to get (for example) this text in cell A1:

[File01.xlsx]Template!$M$2

Then in another cell enter =indirect(a1) and that should return the
value in File01 cell m2.

David Sauder

On Nov 19, 10:39 am, wrote:
I'm having an issue and I can't figure out how to look it up to get a
proper answer, nor can I find an answer in the books that I have, so I
figured I'd try here -- I have a spreadsheet that I am trying to link
to other spreadsheets. In one column, I have data that tells me what
the other spreadsheets are that need to be linked to. I want to look
up a few different cell values from those other spreadsheets to
manipulate, and I want to use it by using the variable in the column
to look up the data.

For instance, the column is F, and if the value for F3 is "File01" I
want to make J3 do the following function, but with the "File01"
dynamic based on what is in column F. The function is:

=[File01.xlsx]Template!$M$2

Any help would be appreciated!

On Nov 19, 1:11 pm, David Sauder wrote:
Try using the indirect function. If you can use simple text formulas
to get (for example) this text in cell A1:

[File01.xlsx]Template!$M$2

Then in another cell enter =indirect(a1) and that should return the
value in File01 cell m2.

David Sauder

I came up with a similar solution to the above idea that I thought I
would post in case anyone is interested and sees this thread. In this
case, I knew that I would be calling from data in a decent number of
other templates, so I set up another spreadsheet in it called "Key"
which lists the data that I could be referencing in the main one.

I then did an INDEX / MATCH formula to match the data in my cell
(File01) to the key, which then has the data that I want in it
( [File01.xlsx]Template!M!2 ). In such a way, I can quickly call upon
the data for everything that I want by using this formula:

=INDEX(Key!$A$1:$T$101, MATCH($F3,Key!$A$1:$A$100, 0), MATCH(J$2, Key!
$A$1:$T$1, 0))

Although it was a little more cumbersome than I originally hoped it
would be, the "Key" spreadsheet will allow me to be able to manipulate
it to get more data that I might use off it, so it might be worthwhile
after all!

Thanks for the suggestion!