Referencing External files with slightly different names

  • Thread starter Thread starter PeteJ
  • Start date Start date
P

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
 
Try the below

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

If this post helps click Yes
 
Missed the extension...

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

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


Jacob Skaria said:
Try the below

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

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


PeteJ said:
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
Click to expand...
Click to expand...
 
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 said:
Missed the extension...

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

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


Jacob Skaria said:
Try the below

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

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


PeteJ said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Missed the extension...

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

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


Jacob Skaria said:
Try the below

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

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top