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?
"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?
> 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:
> > 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?
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?
> > 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:
> > > 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?
"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?
> > > 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:
> > > > 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?
|
