If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Fuctions across multiple identical sheets
I have a large Excel model with est 60 identical sheets=20
(each containing a set of P/L-statement for individual=20 companies.) Each Sheet is named with the company name on=20 the bottom Tab. I use the 'cross sum' functions to add together data for=20 each cell - thru all the individual sheets. So I can add=20 together data for 60 companies in a fast and easy manner:-) Now I want to extract data for one individual cell in all=20 the 60 sheets (for instance Gross Revenue for 2002), and=20 the paste the content into a 'flat table' - preferrably=20 with the value in one column (B), and the sheet=20 name/company name in another (A)... How do I do that? =20 An alternative approach could be to use a second cell (per=20 sheet) as the company name - since each sheet has the name=20 of the company in cell A1. But when I select one cell in multiple sheets, I do not=20 have any possibility to past that content...?? Any suggestions?? Tore Aar=F8n=E6s Norway |
#2
|
|||
|
|||
Fuctions across multiple identical sheets
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 Tore Aa wrote: 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 |
Thread Tools | |
Display Modes | |
|
|