Fuctions across multiple identical sheets

[Tore Aa]

I have a large Excel model with est 60 identical sheets
(each containing a set of P/L-statement for individual
companies.) Each Sheet is named with the company name on
the bottom Tab.

I use the 'cross sum' functions to add together data for
each cell - thru all the individual sheets. So I can add
together data for 60 companies in a fast and easy manner

Now I want to extract data for one individual cell in all
the 60 sheets (for instance Gross Revenue for 2002), and
the paste the content into a 'flat table' - preferrably
with the value in one column (B), and the sheet
name/company name in another (A)... How do I do that?

An alternative approach could be to use a second cell (per
sheet) as the company name - since each sheet has the name
of the company in cell A1.

But when I select one cell in multiple sheets, I do not
have any possibility to past that content...??

Any suggestions??

Tore Aarønæs
Norway

 

[Frank Kabel]

hi Tore
one way would be the use of INDIRECT. e.g. in your example the company
name in column A is identical with the sheet name. Try the following
formula in B1:
=INDIRECT("'" & A1 & "'!C5")
note the multiple apostrophes:
" ' " at the beginning of the function call
" ' in the middle of the function call

copy this formula down and you get the values for cell C5 for all
sheets

HTH
Frank