consolidate data from multiple tabs in the same excel file

[alex.heng]

Hi All,

Apologies beforehand if this question sounds stupid.

I am trying to do a consolidation of all the data from a few tabs. I also need to be able to update anything in any of the tabs and the results should be shown in the consolidated tab.

I am only able to do the consolidation whereby i can update the numbers of hours and it will be dynamically reflected in the consolidated page. But this is not what i want. I want to be able to add a new line e,g 'project abc - 100 hours' and this line would be reflected in the consolidated page dynamically.

What i am trying to update is this,
project name - 10 hours
project xxx - 10 hours.

Cheers,
Alex

[MarvinP]

Hey alex,

I'd download your file and look at the question but the website wants me to install some software on my machine. I don't want to install it. You can attach an excel file to this site by clicking on "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area. Then we could look at the problem and you might get more help.

I'm sorry for the non-standard (to you) method of attaching files. I don't tweet or text much either. I guess if you want to submit a question you need to follow this forum's rules.

[alex.heng]

Hey alex,

I'd download your file and look at the question but the website wants me to install some software on my machine. I don't want to install it. You can attach an excel file to this site by clicking on "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area. Then we could look at the problem and you might get more help.

I'm sorry for the non-standard (to you) method of attaching files. I don't tweet or text much either. I guess if you want to submit a question you need to follow this forum's rules.

Hi Marvin,

My apologies for this. I have attached the file as per your suggestion. If you require more info, do let me know and i will try to furnish you asap.

cheers,
Alex

[inayat]

Hi Alex,

Please check the Consolidated tab of the attached sheet. Hope it is what you wanted.

Cheers-
Inayat

[alex.heng]

Hi Alex,

Please check the Consolidated tab of the attached sheet. Hope it is what you wanted.

Cheers-
Inayat

Hi Inayat,

Its indeed what i want. Could you please explain to me how it is done?

[inayat]

Sure Alex.

I used the Sumif formula, which gives out the sum if a particular condition is met. I used this formula to check all the sheets and then just did a sum of it. If you look at the formula the formula is

Please Login or Register  to view this content.

Please let me know if you need any further clarifications. also you can use Evaluate Formula to check the formula.

Cheers-
Inayat

[MarvinP]

Hi Alex,

I'd do this problem a little differently. This method grabs all the data from sheet 2 to the last one and appends it on the first (Consolidated) sheet. Then I do a pivot table of hours by class name.

This will allow you to add more tabs for as many students as you need and still have it work.

I have a Dynamic Named Range on tab 1 to grow for all classes and a pivot table and a button to run the macro.

See if this is better than the formula where you will need to change it for each new student.

[inayat]

Hey Marvin,

As from what you say I definitely think that it is the better way to do it. Can you please attach the file again as I can not download the file. it is giving some error.

Thanks.

[MarvinP]

Hi Inayat,

See if you can open this one.

[alex.heng]

can i ask why cant i use consolidate?

[MarvinP]

Hi alex,

You can't use consolidate? Have you enabled macros to be used when you open the file? See
http://office.microsoft.com/en-us/ex...001119579.aspx

I'm assuming that is the question and the answer but not sure.
Hope this helps.

[alex.heng]

Hey Guys, apologies for the late reply. Was not feeling too well.
Thanks for all the guidance.
But i have another question. I can understand how this worksheet works. But if i need to create another spreadsheet for another month, How do i esnure that under the consolidation time for each and every project will stay while the engineer timesheet will be blank? Main thing is i want the records from the previous month to stay and at the same time the time that the engineers key in will be added to the consolidated amount.

I have tried everything within my excel knowledge but it didnt do much help.

Attached is the spreadsheet.

[alex.heng]

Thanks guy. I have got it resolved.
Many thanks appreciated.