| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing External files with slightly different names
I have a spreadsheet that colates data from other spreadsheets. Each quarter,
a new spreadsheet is created, and I want to reference that new datasheet from within the top level. The file names change slightly, such as "BookingsQ109" vs. "BookingsQ209". I've tried using the INDIRECT function to create a text string for the name, putting the current quarter in a cell that the INDIRECT calls. Such as: A1 contains: Q109 =INDIRECT("'[Bookings" & A1 &".xls]Sheet1!A2") The problem with this is that the cell reference is inside the text string, and thus I can't copy this formula and have it automatically adjust the cell reference for me. Is there a way I can accomplish this such that I can get the cell reference changed as I copy the formula? Thanks, Pete |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing External files with slightly different names
Try the below
=INDIRECT("'[Bookings" & $A$1 &"]Sheet1'!" & CELL("address",A2)) If this post helps click Yes --------------- Jacob Skaria "PeteJ" wrote: I have a spreadsheet that colates data from other spreadsheets. Each quarter, a new spreadsheet is created, and I want to reference that new datasheet from within the top level. The file names change slightly, such as "BookingsQ109" vs. "BookingsQ209". I've tried using the INDIRECT function to create a text string for the name, putting the current quarter in a cell that the INDIRECT calls. Such as: A1 contains: Q109 =INDIRECT("'[Bookings" & A1 &".xls]Sheet1!A2") The problem with this is that the cell reference is inside the text string, and thus I can't copy this formula and have it automatically adjust the cell reference for me. Is there a way I can accomplish this such that I can get the cell reference changed as I copy the formula? Thanks, Pete |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing External files with slightly different names
Missed the extension...
=INDIRECT("'[Bookings" & $A$1 &".xls]Sheet1'!" & CELL("address",A2)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below =INDIRECT("'[Bookings" & $A$1 &"]Sheet1'!" & CELL("address",A2)) If this post helps click Yes --------------- Jacob Skaria "PeteJ" wrote: I have a spreadsheet that colates data from other spreadsheets. Each quarter, a new spreadsheet is created, and I want to reference that new datasheet from within the top level. The file names change slightly, such as "BookingsQ109" vs. "BookingsQ209". I've tried using the INDIRECT function to create a text string for the name, putting the current quarter in a cell that the INDIRECT calls. Such as: A1 contains: Q109 =INDIRECT("'[Bookings" & A1 &".xls]Sheet1!A2") The problem with this is that the cell reference is inside the text string, and thus I can't copy this formula and have it automatically adjust the cell reference for me. Is there a way I can accomplish this such that I can get the cell reference changed as I copy the formula? Thanks, Pete |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing External files with slightly different names
Thank you Jacob. I haven't tried this yet, but I'm unsure how it works. When
this formula is copied, does the A2 get incremented on each subsequent row or column? Thus if I copy it to the next row, will A2 become A3? Thanks Pete "Jacob Skaria" wrote: Missed the extension... =INDIRECT("'[Bookings" & $A$1 &".xls]Sheet1'!" & CELL("address",A2)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below =INDIRECT("'[Bookings" & $A$1 &"]Sheet1'!" & CELL("address",A2)) If this post helps click Yes --------------- Jacob Skaria "PeteJ" wrote: I have a spreadsheet that colates data from other spreadsheets. Each quarter, a new spreadsheet is created, and I want to reference that new datasheet from within the top level. The file names change slightly, such as "BookingsQ109" vs. "BookingsQ209". I've tried using the INDIRECT function to create a text string for the name, putting the current quarter in a cell that the INDIRECT calls. Such as: A1 contains: Q109 =INDIRECT("'[Bookings" & A1 &".xls]Sheet1!A2") The problem with this is that the cell reference is inside the text string, and thus I can't copy this formula and have it automatically adjust the cell reference for me. Is there a way I can accomplish this such that I can get the cell reference changed as I copy the formula? Thanks, Pete |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing External files with slightly different names
A2 will change to A3 if copied down..and If copied across will change to B2.
You can use absolute referencing such as $A2 is you dont want the formula to change while copied across..OR as A$2 if you dont want the row to change If this post helps click Yes --------------- Jacob Skaria "PeteJ" wrote: Thank you Jacob. I haven't tried this yet, but I'm unsure how it works. When this formula is copied, does the A2 get incremented on each subsequent row or column? Thus if I copy it to the next row, will A2 become A3? Thanks Pete "Jacob Skaria" wrote: Missed the extension... =INDIRECT("'[Bookings" & $A$1 &".xls]Sheet1'!" & CELL("address",A2)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below =INDIRECT("'[Bookings" & $A$1 &"]Sheet1'!" & CELL("address",A2)) If this post helps click Yes --------------- Jacob Skaria "PeteJ" wrote: I have a spreadsheet that colates data from other spreadsheets. Each quarter, a new spreadsheet is created, and I want to reference that new datasheet from within the top level. The file names change slightly, such as "BookingsQ109" vs. "BookingsQ209". I've tried using the INDIRECT function to create a text string for the name, putting the current quarter in a cell that the INDIRECT calls. Such as: A1 contains: Q109 =INDIRECT("'[Bookings" & A1 &".xls]Sheet1!A2") The problem with this is that the cell reference is inside the text string, and thus I can't copy this formula and have it automatically adjust the cell reference for me. Is there a way I can accomplish this such that I can get the cell reference changed as I copy the formula? Thanks, Pete |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing External Data | Excel Discussion (Misc queries) | |||
| Indirect referencing of external spreadsheets | Excel Discussion (Misc queries) | |||
| External References - Forcing Relative File Referencing | Excel Worksheet Functions | |||
| External links referencing URL with parameter | Excel Discussion (Misc queries) | |||
| Referencing worksheet names | Excel Worksheet Functions | |||
Linear Mode
