How do I bring totals from one worksheet onto the second worksheet

[Shazza]

I have an Excel document with 2 worksheets.
The first worksheet has 20 columns with headings and then dollar figures in
each cell and a total of each column at the bottom.
The 2nd worksheet has the column headings listed virtically on the page. I
would like the TOTAL of each column on the first worksheet to go into the
appropriate cell on the second worksheet. How do I do this?

 

[Biff]

Hi!

Assume the headers are in order on both sheets:

Sheet1:

Header1;Header2;Header3, etc

Sheet2:

Header1
Header2
Header3
etc

In Sheet2 in the cell to the right of Header1 enter this formula and copy
down as needed:

=INDEX(Sheet1!A$11:F$11,ROWS($1:1))

Sheet1!A$11:F$11 is the range of the totals. Adjust to suit.

Biff

 

[Shazza]

Hi Biff,
Thanks for your answer, I don't completely understand how to do the formula,
so can you please try to help me again.

Worksheet 1 - Expenses ( 2 columns)

Supplies Entertainment
100 50
200 60

300 110

Worksheet 2 - Monthly Totals

Supplies
Entertainment

How do I get the totals on my Expenses worksheet to go directly into my
Monthly Totals worksheet? Supplies, into column b row 1 and entertainment
into column b, row 2?

I'm really new at this and this calculation has me baffled. Thanks for your
help!

Shazza

 

[Biff]

Hi!

Worksheet 1 - Expenses ( 2 columns)

Supplies Entertainment
100 50
200 60

300 110

That would look like this:

................A.........................B...............
1.......Supplies............Entertainment......
2..........100........................50..............
3..........200........................60..............
4..........................................................
5..........300.......................110.............

Sheet2:

................A........................B................
1.....Monthly Totals..............................
2..........................................................
3..........Supplies.............Formula(...)....
4.....Entertainment .........Formula(...)....

In B3 of sheet2 enter this formula and copy down as needed:

=INDEX(Sheet1!A$5:B$5,ROWS($1:1))

Now, the above table will look like this (if done properly):

................A........................B................
1.....Monthly Totals..............................
2..........................................................
3..........Supplies................300.............
4.....Entertainment ............110.............

A more complicated way to do it (or, if the headers are not in the same
order, this method should be used):

Enter this dormula in B3 of sheet2:

=INDEX(Sheet1!A$5:B$5,MATCH(A3,Sheet1!A$1:B$1,0))

The result will be the same.

Biff