Autocalculate does not work

[Guest]

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?

 

[excelthoughts]

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