Consolidate

**shadad** · 01-12-2010, 11:51 AM

Hello everyone, my first time post. I am using Excel's Consolidate command. I understand how excel's consolidate by category works using labels. however, in most excel books it states if you want to consolidate by position dont check the Top Row and Left Column label boxes. Can anyone tell me why since it doesnt make any difference to the results and it saves me from having to type the labels on my summary sheet?

**Richard Buttrey** · 01-12-2010, 12:48 PM

Hi,

If you're consolidating by position then by definition all the column / row labels should be in the same relative positions, hence there's no point in checking the row/column check box. Excel assume they are all the same.

It would make a difference if your column/row labels whilst being the same are not in the same position relative to each other. e.g. column labels Jan, Feb, Mar in one sheet and Mar, Feb, Jan in another would require you to check the boxes.

HTH

**shadad** · 01-12-2010, 01:02 PM

yes, but i still have to type the labels onto my summary worksheet when excel can do this for me by selecting the row and column labels. also how do i update with the consolidate command. Eg my source sheets have numeric data in cells C2:C7 on all worksheets. When i consolidate i reference range C2:C20 in case i want to add data to the source worksheets. Why doesnt my summary worksheet automatically update when I add figures to cell C8?

**Richard Buttrey** · 01-12-2010, 01:21 PM

There are two sorts of consolidation. One consolidation consolidates values and the other creates links to the original data.

You don't need to type labels on the summary sheet if you have ticked one of the Top row or Left column boxes. However if you're consolidating values and you've added or changed data then you'll have to refresh the consolidation because this consolidation is not dynamic. Refresh the consolidation by selecting Data Consolidation OK. This will then pick up new data in say C8.

You can also consolidate with links to the original data if you tick the 'Create Links to Source Data' box. Doing this will create a different 'look' where the Excel Outlining and Grouping functionality comes into play.

All this is perhaps better explained by MS themselves at http://technet.microsoft.com/en-us/l.../cc750889.aspx

HTH

**shadad** · 01-12-2010, 01:36 PM

Originally Posted by Richard Buttrey

There are two sorts of consolidation. One consolidation consolidates values and the other creates links to the original data.

You don't need to type labels on the summary sheet if you have ticked one of the Top row or Left column boxes. However if you're consolidating values and you've added or changed data then you'll have to refresh the consolidation because this consolidation is not dynamic. Refresh the consolidation by selecting Data Consolidation OK. This will then pick up new data in say C8.

You can also consolidate with links to the original data if you tick the 'Create Links to Source Data' box. Doing this will create a different 'look' where the Excel Outlining and Grouping functionality comes into play.

All this is perhaps better explained by MS themselves at http://technet.microsoft.com/en-us/l.../cc750889.aspx

HTH

I am creating links to the source data and thought if i add data to cell C8 the destination worksheet would automatically update without me having to do Data/Consolidate/OK