Referencing External files with slightly different names

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
 
J

Jacob Skaria

Try the below

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

If this post helps click Yes
 
J

Jacob Skaria

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...
 
P

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 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...
 
J

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

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Referencing an External Cell 1
Is a combination of INDIRECT with INDIRECT.EXT, a component ofmorefunc.xll by Laurent Longre, valid? 1
Excel - Wich Function should I use? 1
Vlookup and return sheet name also 2
External cell reference between two excel worksheets both in One Drive 0
Help with Address(), Indirect(), Offset 2
Embedded Excel into Word with Tables 0
Using SUM(INDIRECT()) 2

Top