Insert file and tab name from list into formula
 

File1 has multiple tabs labeled as Eastside, Westside, Southside, Northside,
etc.

File2 has the same tabs with data covering a different time period

In File3, column A will list a file name and column B will list a tab name.

I want to create a formula that will go to a specified cell in one of those
file / tab combinations, based upon the variables listed in columns A & B. I
also want to have specific cell references, such as D10, but havent decided
if I want to enter that reference in a cell in File3, or just enter it the
first formula and then copy.

If I were typing the formula directly, or creating it by linking, it would
look like:
='[File1.xls]Eastside'!D10

But, I cant figure out how to insert the Column A (file name) and Column B
(tab name) values into a formula without it coming out as a text entry rather
than a working formula.

Can this be done?

(For what its worth, Im building this in EXCEL 2007, so I have access to
any new functionality that may assist €“ just dont know what or where)

Insert file and tab name from list into formula
 

This function is called INDIRECT, it lets you build up a cell reference
a piece at a time and concatenate them together piecemeal.

*='[File1.xls]Eastside'!D10
*
If the filename is in A10 and Tabname is in B10 and the cell reference
is in C10, the INDIRECT formula would be:
*
=INDIRECT("'["&A10&"]"&B10&"'!"&C10)

*Or, you can leave out the reference to C10 and encode the rest
directly into the formula:

*=INDIRECT("'["&A10&"]"&B10&"'!D10")*


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45178

Insert file and tab name from list into formula
 

You ROCK!!!! I just finished reviewing the entire list of functions and
never compehended what this one did.

Thank you very much.

"JBeaucaire" wrote:


This function is called INDIRECT, it lets you build up a cell reference
a piece at a time and concatenate them together piecemeal.

*='[File1.xls]Eastside'!D10
*
If the filename is in A10 and Tabname is in B10 and the cell reference
is in C10, the INDIRECT formula would be:
*
=INDIRECT("'["&A10&"]"&B10&"'!"&C10)

*Or, you can leave out the reference to C10 and encode the rest
directly into the formula:

*=INDIRECT("'["&A10&"]"&B10&"'!D10")*


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45178

Insert file and tab name from list into formula
 

Excel rocks...we are all but awestruck fans...

Glad it worked out!


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45178