Referencing External files with slightly different names

[PeteJ]

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

[Jacob Skaria]

Try the below

=INDIRECT("'[Bookings" & $A$1 &"]Sheet1'!" & CELL("address",A2))

If this post helps click Yes
---------------
Jacob Skaria

[Jacob Skaria]

Missed the extension...

=INDIRECT("'[Bookings" & $A$1 &".xls]Sheet1'!" & CELL("address",A2))

If this post helps click Yes
---------------
Jacob Skaria

[PeteJ]

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]

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