creating a master worksheet of data

[chillz]

HI,

I have a 5 worksheets that would need to be consolidated into a master sheet.
The sheets contain accounts and monthly costs for each month. Not all sheets have all the accounts.
I am hoping to create a roll up of all the accounts in one sheet and add up each month's figures if the account is present. I do not want to have to add the same account structure for each worksheet and add them up individually.... this creates too many necessary items to review.

Thanks
Hope there is a workaround.
Any suggestion would be greatly apeaciated

[jeffreybrown]

To receive the most appropriate answer, please attach a sample workbook with the structure you have and what you expect.

It's unlikely somebody is going to put one together to test out a solution.

[Shareez Saleem]

Use Pivot Chart Wizard (Alt+D+P)

Alt+D+P > Select "Multiple Consolidation Ranges" > Select "I will create the page fields" > Select Range "All 5 sheets one by one > Add > Next > Finish

Better convert all the sheet ranges to "Table" (Ctrl+T) before doing the consolidation.

[chillz]

here is a sample version.

I have tried the Pivot table method however it it not giving me the data that i need.
The description fields unmerge and do not provide a full view of all the expenditures
under that merged section also i will not let me pivot to the expenditure account column instead of the description...

I would also like to figure out a way to be able to update actuals on a monthly basis from a similar downloaded version i was thinking some sort of edit links...
But i would have to find away to define the actuals only section that needs to be updated......

[chillz]

anyone care to comment?

[jeffreybrown]

I think a pivot table is the way to go, but I see a few things I would to with your data.

1) Combine all sheets into one. I understand they are different in the sense of Admin, Misc, and Maint, but create a column with those designations which allows you to separate the data.

2) Remove the total rows. This can be done in a pivot table. Example, on the Admin tab row 3, remove it.

3) Fill in empty cells. Example, on the Admin tab, why leave A4 and A5 blank when they can both say Salaries