Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down List - insert row? | Excel Discussion (Misc queries) | |||
Insert Graphics File into Worksheet via Formula | Excel Worksheet Functions | |||
Insert Name Paste List | Excel Discussion (Misc queries) | |||
Insert Row between items in list ... | Excel Discussion (Misc queries) | |||
how do i automatically insert a file name into a formula from a ta | Excel Discussion (Misc queries) |