Excel 2000 populate info from many sheets into one sheet 2

[MJamison07]

I have a file with many sheets. I want to link the subtotals in each indiv sheet into a "summary" sheet. The subtotals are in a column on the individual sheets and must be in rows on the summary sheet.

Example:

Indiv Sheets would look like this:
A 5
B 2
C 7

Summary Sheet should look like this:

Sheet Name A B C
Sheet1 5 2 7
Sheet2
Sheet3

I know I could put an = then click on linked cell. I have about 20 sheets & it would take a while. Is there an easier way?

Thank you,
MJamison

 

[Zaph]

A possibility could be the Transpose formula. This is an array formula so takes a little bit of work, it is designed to take a column of figures and turn it into a row.
On the front sheet you would highlight a row of cells equivalent to the number of cells in the column on the first sheet of figures. You then write the formula into the active cell of the highlighted range, it would be
=Transpose(<cell range>)
To finish the formula, though, you do Ctrl + Shift + Enter. This produces the array formula result, where the same formula is entered into each cell, but produces a different result in each. Hope this helps.

 

[xlbo]

An alternative is - if you have In Col G for example

G1 5
G2 2
G3 7

you can use the Column() function to dynamically reference different rows

In your summary sheet, in B2, with the sheet name in A2 you could enter

=INDIRECT($A2&"!G"&COLUMN()-1)
and copy across

the indirect function converts a string to a range reference so you are creating a string like

$A2 = "Sheet1"
"!G"
COLUMN()-1 = 1 (when you are in col2 - B, 2 when you are in col3 - C etc etc)

so when you drag it across, you are creating these strings
Sheet1!G1
Sheet1!G2
Sheet1!G3

etc etc
All you need to do is figure out what the offset to the column number is ie if you have the formula in Col J, you would need to use
COLUMN()-9
to reference row 1
As you drag across, the column increments and therefore the row will increment in the formula...

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MJamison07]

Thank you Geoff & Zaph. I was able to make both suggestions work, but Geoff I was able to fill across & down with yours which is great for this project. I see a lot of possibilities with the transpose formula for other projects.

Is there a formula that will put the sheet name in a cell?

Life is good.

Thanks again,
MJamison

 

[xlbo]

have a look in the FAQs section. I have one in there that showas how to do that - along with other things

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question