Software

Ask something
Discussion Locked

Excel Data Consolidation

0 helpful
5 replies

by cmorus

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have about 100 worksheets with 4 columns and 20 rows. I want to consolidate all the info into one "Totals" worksheet. I start out at the Totals worksheet, Data + Consolidate, then click the button to take me to the first worksheet, select the cells, back to the consolidate box, click add and then have to go back to each worksheet and Add. Is there a way I can type in a range? I've tried: 'A&P:ck benton'!$C$4:$G$4, but it tells me that it cannot open consolidation source file 'ck benton' (or whatever last file name I give) and does not consolidate. I hate to go through 100 worksheets. Any ideas?

5 replies

by c_m_s

Hi,
It appears "data + consolidation" won't accept a range, as you have noted. Each sheet you want to include has to be added individually.
If what you are wanting is a total then do a sum on the "totals" worksheet which does accept a range and will drill down through the workbook. Example: the "Totals" worksheet formula would be
"=Sum("A&P:ck benton'!$C$4:$G$4)"
As a bonus you can put this formula in any cell of the "Totals" worksheet that you would like. You can also use some of the other functions like max, min, avg, etc. if you wanted.

Hope this helps,
Mike

by cmorus

This was perfect, thanks. Worked so much better than Data + consolidate.

by c_m_s

Hi,
It appears "data + consolidation" won't accept a range, as you have noted. Each sheet you want to include has to be added individually.
If what you are wanting is a total then do a sum on the "totals" worksheet which does accept a range and will drill down through the workbook. Example: the "Totals" worksheet formula would be
"=Sum("A&P:ck benton'!$C$4:$G$4)"
As a bonus you can put this formula in any cell of the "Totals" worksheet that you would like. You can also use some of the other functions like max, min, avg, etc. if you wanted.

Hope this helps,
Mike

This discussion is closed to new replies.