Fuctions across multiple identical sheets

  • Thread starter Thread starter Tore Aa
  • Start date Start date
T

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
 
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
 
Back
Top