INDIRECT and ADDRESS functions:Autocalculate does not work

Hi mdeanda

You probably need to make use of the INDIRECT and ADDRESS functions. If
you enter the following in Column B in place of ="='"&A40&"'!"&"G4", it
should work.

=INDIRECT(ADDRESS(ROW(G4),COLUMN(G4),,,A40))

The ADDRESS function creates the address of the cell, and the INDIRECT
retrieves the value of the cell.

Regards
Andrew
excelthoughts.com

I have over 100 excel files with 20-30 worksheets in each. Using ASAP
utilites I have created an index on a "summary worksheet" tab. This index
contains the entire list of tab names for the file. Each tab name is a
persons name.

Cell G4 (one of 400 cells) of each tab contains data that I want
consolidated into the "Summary worksheet". I create formulas that I will
copy into all the files

Index Data
Column A Column B
Alig, Paula ="='"&A40&"'!"&"G4" This is the formula that I
created

Column A Column B
Alig, Paula ='Alig, Paula'!G4 This is the data that
Excel returns

Column A Column B I use copy special for "values
only"
Alig, Paula ='Alig, Paula'!G4 and place this in a new
cell
Excel does not
return Data from cell
G4 in the
"Alig, Paula" tab

Column A Column B I have to use the F2 key
and then press
Alig, Paula Resource Specialist "enter" before the field
re-calculates

Again I have over 100 files with 30 rows and 24 columns. I do not have time
to hit F2 followed by "enter" for all cells and Files. The automatic options
is turned On. Why won't Excel autocalculate?

Hi mdeanda

You probably need to make use of the INDIRECT and ADDRESS functions. If
you enter the following in Column B in place of ="='"&A40&"'!"&"G4", it
should work.

=INDIRECT(ADDRESS(ROW(G4),COLUMN(G4),,,A40))

The ADDRESS function creates the address of the cell, and the INDIRECT
retrieves the value of the cell.

Regards
Andrew
excelthoughts.com