Using cell data to open other templates?
 

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!

Using cell data to open other templates?
 

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!

Using cell data to open other templates?
 

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!