General discussion

Locked

Excel Data Consolidation

By cmorus ·
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?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Data Consolidation

by C_M_S In reply to Excel Data Consolidation

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

Collapse -

Excel Data Consolidation

by cmorus In reply to Excel Data Consolidation

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

Collapse -

Excel Data Consolidation

by C_M_S In reply to Excel Data Consolidation

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

Collapse -

Excel Data Consolidation

by cmorus In reply to Excel Data Consolidation

Poster rated this answer

Collapse -

Excel Data Consolidation

by cmorus In reply to Excel Data Consolidation

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Forums