Hello Friend;
Good morning
In workbook attached below, I have North, East, West, South and TOTAL worksheets .
In order to do consolidation , On TOTAL worksheet I clicked on "Consolidate" in Data Tools group on Data Tab, also, in consolidate window,
checked Top row, Left column, and create links to source Data , then, on "West" worksheet , entered $2,000 for Product - A for Oct. and on
"South" worksheet, entered $3,000 for Product - A for Oct. Now on "North" worksheet , for Product - A for Oct. , there is $1,730, on "East"
worksheet , for Product - A for Oct. , there is $1,234. If we add all 4 amounts , we get $7,964, and on "TOTAL" worksheet in 2nd outline, for
Product - A Oct. amount is $2,964 , how can I get $7,964 for Oct for Product - A ?
Also, How can I remove outline 1, 3, 4, 5 ? How can I keep Outline 2 only ?
Thank you very much.
Have a great day.
Sincerely;
Mitul.
Hello Mitul,
Seems to me your data is not aligned, meaning that you probably have tried different scenarios or whatever could be the reason.
In TOTAL sheet, if you open up outline 5, you will see that for October you have a new set of consolidated data starting from January.
So, what I did was to delete the TOTAL sheet, created a new sheet and created a new consolidated list. Check attached file.
Br,
Anders
Thank you very much, Anders (my friend);
Also, How can I delete 2nd outline on worksheet you gave me ?
Thank you.
Have a great day.
Hello Mitul,
Both 1st and 2nd outlines are needed, if you want to have the data grouped. Click 2nd outline to open up the groups to see the source data. Click 1st outline to close the groups to only see the consolidated data.
If you do not want to group the data then just make sure the option for source data link is unchecked when you create a new consolidated list, you will then get an ordinary list with the consolidated data only.
Please check this blog article for more information. https://www.myonlinetraininghub.com/excel-group-and-outline-data
Br,
Anders
Thank you very much, Anders (my friend);
Have a great day.
