INDIRECT function

  • Thread starter Thread starter Warren
  • Start date Start date
W

Warren

Can someone please help with the function INDIRECT?
I am using INDIRECT to compose a Summary sheet, which pulls data from the
data sheets.
For example:
I have, say 50 sheets, in a workbook. The sheets are named Summary, AAA,
BBB, CCC..... (the data sheets are AAA, BBB....) in the workbook.
On the Summary sheet
Column A contains the names of all the data sheets in the workbook.
For example:
A1 has 'AAA'
A2 has 'BBB'
A3 has 'CCC'

In the other cells of the Summary sheet, I have the formula like
In B1 =indirect($A1&"!C3"), in C1 =indirect($A1&"!T4")
In B2 =indirect($A2&"!C3"), in C2 =indirect($A2&"!T4")
and so on, to pull the data from the data sheets

This works fine until I add columns or make changes to the data sheets.
Since the INDIRECT formula take text as its input, if I add a column on
sheet AAA, where column C becomes column D, and T becomes U, then the
summary sheet would be summarizing the wrong items. i.e., the data I want
are now in Column D and U, not C and T.

It is easy if I only have few sheets in the workbook and I only want few
data items. I can just manually type in the formula =AAA!C3. However, if I
have 50 sheets in the
workbook and I want 50 different data items from each sheet, it would be
very difficult to enter the formula manually. I would like to have a
generic formula, like INDIRECT, that are flexible enough to allow me to
compose a Summary sheet and be able to add columns to the data sheets and
still reference to the right cells.

Can anyone help?

Thanks

wachen
 
I would like to have a
generic formula, like INDIRECT, that are flexible enough to allow me to
compose a Summary sheet and be able to add columns to the data sheets and
still reference to the right cells.
Click to expand...

Use INDIRECT in conjunction with ADDRESS.

=INDIRECT($A1 & "!" & ADDRESS(ROW(Sheet2!F10),COLUMN(Sheet2!F10))

When you add columns to sheet2, it will bump over the Sheet2!F10 to the
appropriate column.
however it only works if you add the same column to all sheets.

If your sheets have different columns, but the columns have names, you can
use one of the LOOKUP and/or INDEX functions.
 
Use INDIRECT in conjunction with ADDRESS.

=INDIRECT($A1 & "!" & ADDRESS(ROW(Sheet2!F10),COLUMN(Sheet2!F10))
Click to expand...

Ugh! *ALWAYS* (and I *DO* mean **ALWAYS**) include single quotes around
worksheet names when constructing textrefs inside INDIRECT calls. The result is
far more robust since without them worksheet names that contain spaces or
exclamation points (along with a few others that require single quote
delimiters) will choke INDIRECT so that it returns #REF!.Even if the worksheet
name doesn't require single quote delimiters, no harm done adding them.

Then there's also the avoidable redundancy of INDIRECT(..ADDRESS(.)..).

=INDIRECT("'"$A1&"'!"&CELL("Address",Sheet2!F10))

The same result could be achieved with

=INDIRECT(ADDRESS(ROW(Sheet2!F10),COLUMN(Sheet2!F10),,,$A1))

but 2 function calls almost always beats 4.
 
Back
Top