Need to consolidate the data with an effective formula

[aparnawangu]

Hi Friends,

I have various sheets with the info of employees and this is for various clients.I need to consolidate data employeewise for various clients.The sheet contains three sections Overall forecast as per capacity,H&M phase forecast and Fixed phase forecast.I need formula to calculate H&M phase forecast employeewise for all clients.Fixed phase forecast is already formulized and overall forecast capacity will be sum of all months.

Pls its urgent help me with an appropriate formula.


attaching sample sheet.

Regards,
Aps

[Richard Buttrey]

Hello and welcome to the forum.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains the following columns

Date
Client *
Resource Name *
Roles - Could be a VLOOKUP if each resource name only has one role, otherwise a drop down.
Forecast Type - * (a choice of "Overall" or "H&M")
Value

The * values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

[aparnawangu]

Hi
Yhis format was already created and even clients tabs are predesigned.I only need to populate the consolidation sheet for now.Later I will work to update the sheet.Can anyone pls help me with the formula for now.Will vlookup+Vlookup formula will work for thsis if yes how to write that formula.I will be highly obliged.Pls help some one with any formula which will capture the desired result.


Regards,
aps

[sandy666]

As Richard said use PowerPivot/PowerQuery

You can read about it here: Power Pivot: Powerful data analysis and data modeling in Excel and here: Microsoft Power Query for Excel

[aparnawangu]

Hi Sandy,

Thanka Richard and sandy for the valuable suggestions but try to understand my situation.Neither I am a masters in macro nor power quey and poer pivot.It will take hell lot of time for me to understand the concepta which you are talking about.I though that I will get some formula help for now.Is that possible? pls help me take the first step for now.I am new to all these advance concepts and will need the experts guidance for now.


Regards,
Aps

[sandy666]

first update your profile about excel version. word "professional" is not a version

[aparnawangu]

Hi
Sorry for the inconvenience caused.I have update the Ms-off Ver.

Thanks
Aps

[Richard Buttrey]

Given that the same employee can exist on different rows on different sheets I can't think of any simple formula that will produce the result.

You could use 10 SUMIFS(() formulae. e.g. in E5 copied across and down

Please Login or Register  to view this content.

But it really would pay you, particularly if you want to use this obtain any serious analysis or other stats, to get all your data into one sheet as previously suggested.

[aparnawangu]

Hi Richards,
Appreciate your quick response.I implemented this formula and hopefully its not showing any error but I am trying to understand here that How the overall capacity planning table should work and fetch the records from H&M table.
As we are fetching the result from E:E column of the overall capacity planning table and it is dependent on the H&M capacity plan table results.Please help that it should be merely a link to the values of the H&M tab or soe other formula needs to be implemented.Pls suggest

Regards,
aps

[JohnTopley]

.I need formula to calculate H&M phase forecast

.... which is what: what is the logic ???

The "Consolidation" appears to be simply a summation of the corresponding tables in each sheet: an assumption that data is entered (not derived).

Alternative formula

in E5 on "Consolidation"

=SUMPRODUCT(SUMIF(INDIRECT("'" & Sheets &"'!B:B"),$B5,INDIRECT("'" & Sheets &"'!E:E")))

Copy down

Change E:E to F:F, G:G as you copy across

"Sheets" is named range of all your sheet names

[aparnawangu]

Hi John,
Yes you are correct Overall will be the summation only of H&M column and it will be manually entered for all the sheets and same is the case with H&M capacity it will be entered manually for all the sheets.Actually Overall Capacity is divided into two categories H&M and fixed.

consolidation formula will be required for H&M table only .So Please help me with the formula for H&M table.


Regards
Aps

[Richard Buttrey]

Hi,

Re #9. Not sure what you are getting at.

In E5 of the Consolidation you need to add 10 of the SUMIF formulae together changing the 2 character sheet name in each of the sumifs. Then copy down and across.

Obviously when you copy it to N5 and across you need to adjust the column ref from E:E to L:L because you have an additional two columns L&M on the Consolidation sheet which means a straight copy from E5 to N5 will be two colunmns adrift. Which is a good reason for keeping these sorts of things entirely consistent. i.e. your individual as, an.. sheets should have had exactly the same colunns as the Consolidation. You could have hidden them if necessary.

[aparnawangu]

Hi Richard,

I require employeewise data for H&M phase only.I dont want for Overall capacityThe formula which u suggested was calculating employeewise data for overall capacity phase.Overall capacity will not contain any value and first H&M capacity should be populated and the overall capacity will be only summation of T&M & Fixed phase.I require formula to calculate H&M phase only.

For eg Prasad employees Nov-17 till May-18 H&M data's consolidation and that should come as 1472 total for nov month for all the projects.

Attached the sheet again,

Regards
Aps

[JohnTopley]

This ??
Formula as previously supplied referencing column L to P

[aparnawangu]

Hi,
I think I need to change the reference and the formula will work.Thanks for your help.

Regards,
Aps

[sandy666]

is that what you want (for H&M only) ?
(with PowerQuery)

[aparnawangu]

Hi Friends,
Need one more help.In the excel sheet which I will attach already I got sumif formula for consolidating.Its working fine thanks a lot.Now One more sheet is included in the current sheet named client services and summary of this data also I want to include in the consolidation sheet.Pls guide me with the formula.Sould I need to include in the same sumif formula?Pls suggest


Attaching the sheet for reference.


Regards,
aps

[JohnTopley]

Basically the same formula which would easier if you had a column labelled "Client Services" with values of "AC","AR" etc in ONE table.

And how will the table look in "Consolidation" ?