Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variations on referencing a cell in another workbook - continued
Mike,
I'm afraid this solution has a couple of practical problems, even though it produces the right result. The main limitation is that when copied, the entire string functions as an absolute. The specific cell reference at the end of the formula !$H6 is written so that when copied, the column (H) won't change but the row will. The spreadsheet has 100 rows for each of the 13 columns. I would normally create the formula in the first row, and then copy to all subsequent rows in that column, with the row number increasing by 1 as the formula is pasted down the column. Using the INDIRECT function that no longer works. Do have any other ideas about this? Thanks, Eric "Mike H" wrote: Hi, Is this wat you mean =INDIRECT("'[sector " & D4 & "]2008'!H6") Where D4 contains a 1 Mike ORIGINAL QUESTION: I have a simple problem. I have a master spreadsheet with about 1300 cells referencing 13 other workbooks. The 13 workbooks all have the same name except for the number they end with. All the cells referencing one of the 13 workbooks are in the same column and that column is headed with the unique number for that workbook. It is an easy matter therefore to create the unique name of each workbook by appending the number at the top of each column using concatenation. A simplified example of a reference to a given cell in a given external workbook is ='[Sector 1.xls]2008 '!$H6 . I have found that breaking up the reference with a concatenation operator (&): ='[Sector &D4&.xls]2008 '!$H6, where cell D5 contains the number 1, does not work as a cell reference, i.e., it doesn't bring back the value in the remote cell. I have tried to create the complete string, which exists in the formula between single quotes, in a different cell, and then bring that string into the cell attempting to execute the reference: ="'[Sector "&D4&".xls]2008" referenced from the original cell and then concatenated to the last part of the formula: &!$H6 , but that hasn't worked, even though the string created in the external cell is correct. I have tried ending and re-starting the quotes within the file reference before and after the concatenation, but that hasn't helped. In other words, it seems that the syntax of the external cell reference string can't be disrupted. Does anyone have any insight into this problem, and can anyone offer a solution? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variations on referencing a cell in another workbook | Excel Discussion (Misc queries) | |||
Protecting a 2007 Workbook continued... Bob Phillips you out there | Excel Discussion (Misc queries) | |||
Protecting a 2007 Workbook continued... Bob Phillips you out there | Excel Discussion (Misc queries) | |||
Spell Check in Protected Worksheet & Shared Workbook continued | Excel Discussion (Misc queries) | |||
Cross workbook referencing based on cell input | Excel Worksheet Functions |