data table with 3 criterias

[JPRICH16]

Hi

I am creating a forecast based on ppl and their team by quarter but people switch teams or leave the company.

Basically if someone is in team A in Q1 and B in Q2 the pivot or table should show them in A or in B in their repsective quarters.

So if i it can be search by teams and have the soltuons or formula show the teams by ppl who are in their team in all or any quarter!

Basically an index 3 match function for team name and name, quarter and $ in quarter or something to get the solution results in the attachment!

See example attached!

the teams needs to be extracted but only show the names that are with the teams in each quarter. so i could work for team A in Q1 and Team B in Q2 so the forumula should show me in Team A in Q1 and in Team B in Q2.

Basically i need the full year spend for each team but the names are within multiple teams throughout the year!

SO the point of this is to find the FULL COST SPENT for each team and to achieve this I need a formula which finds the team name, persons name, and IF they worked there for a certain time in the year and extract the cost but of the specifici time they worked for a team!

Jeffrey

[BMV]

JPRICH16 may be somebody understand your explanation but not me. Do you need extract data per quarters only or create list of full names per team also?

[JPRICH16]

the teams needs to be extracted but only show the names that are with the teams in each quarter. so i could work for team A in Q1 and Team B in Q2 so the forumula should show me in Team A in Q1 and in Team B in Q2.

Basically i need the full year spend for each team but the names are within multiple teams throughout the year!

SO the point of this is to find the FULL COST SPENT for each team and to achieve this I need a formula which finds the team name, persons name, and IF they worked there for a certain time in the year and extract the cost but of the specifici time they worked for a team!

[BMV]

I'mnot sure I understood right
for full name list

Please Login or Register  to view this content.

and for q1-q3

Please Login or Register  to view this content.

[JPRICH16]

thats insane!!! Can you do it though as if First and Last name A1 & B2 were not present.


So the B16 in your attachment is based off of A16=C2?

if that makes sense!

[JPRICH16]

the full name will be a copy and paste! sorry if my attachment was confusing!

[JPRICH16]

kinda like this one attached!!

[BMV]

JPRICH16. Now I completely confused by last attachment and comments. And you can edit your messages.

[JPRICH16]

Basically the same thing you did EXCEPT the full name with be stagnant.

The full name will not be a forumla.

In my new attachment the cells from I3:K15 in "soltions tab" are pulling information from "Forecast" matching the columns E,F,G "Forecast" with A1, A7 & A12 "solutions". Based off that then columns I,J,K "foreacast" are pulled matching the headers of I,J,K "solutions" on the same line of the full name.

Makes sense?

Essentially if the FULL NAME is found in forecast tab then match A1, A7 & A12 with the forecast tab in the specific "Q1-Q3 Divison" and if that matches too then pull information "Q1-Q3" and the Q's should always match i.e 1-1, 2-2.

I used vlookups and IF to do this becasue Im not sure of the formula. Essentially its a index lookup with 2 headers matching before information is pulled

does that make sense?

[JeteMc]

If I understand BMV's formula for Q1:Q3, this is how it may be applied to the version of the file attached to post #7:
1. The 'Solution' sheet will need to be modified, as modeled in the attached file, so that there is a blank cell in column A before each title which are then immediately followed by full names.
2. The formula is modified to read:

Please Login or Register  to view this content.

Let us know if you have any questions.