Formula needed to look up specific name and enter data from specific column within same row

[KimboGreig]

Hello,

I have a list of consultants for different teams and their targets / points. Each tab on the spreadsheet is "per team" and within the tabs the targets are listed per month.
Sometimes there are consultant moves, which means the name no longer appears say in "Section B tab" but will now appear under "Section D tab" and under that month.

The last tab "All Teams - Summary" is where i require the formula for each consultant and to show their stats and match the cell colour if red or green.
This is so at a glance we can see all consultants and who has hit or missed target.

Below is the first team and consultants for Jan and Feb (Jan first table half cut off - Feb 2nd table, exactly same as Jan but with 2 removed consultants)

Below is a second team and consultants for Jan and Feb - with moves in Feb (These are just examples, there are 3 other tabs with teams)

All Teams tab is as below where i want the data to auto complete.
It will need to read the consultants name for said particular month (finding the name potentially across all sheets if they have moved - name 7 & 4 in example) and read and apply the data and cell colour in the ones which are green and red (column O on the sheet)

Any help with this appreciated! I feel maybe impossible or having to change few things which willing to do.

Thanks!

 

[awoohaw]

Is this a management tool you are creating for yourself? Or is this in a format you're required to follow?
If you can change your data recording you can create a dashboard of pivot tables that may make your quick viewing easier.
If you can do that, start by creating a data entry page. And build your pivot reports/charts based on that.
I am assuming ROLE and TEAM are synonyms, if not just add a new column for TEAM.
And it also seems like you can have a control table to lookup items that are formula driven like standard hours/days and Expected values.
And you can use formulas to calculate personal best as well.

So, have a data entry table like this, and build your reporting off of it:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
21	Consultant	Month	Role/TEAM	Std Week Hr	Std Day Hrs	Mnth Wrk Days	Days off	Add Wrk Day	Add Wrk Hr	Act Day Wrk	Acct Hrs Wrk	Exp /Hr Team	Exp Pts Team	Act Pt Ach	Act Pt/Hr	Act Pts/Day	Pers Best	Nbr Errs	Nbr Q Sav	Nbr Pos FB
22	Data Entry	Data Entry	Formula	Formula	Formula	Formula	Data Entry	Data Entry	Data Entry	Data Entry	Data Entry	Formula	Formula	Data Entry	Data Entry	Data Entry	Formula	Data Entry	Data Entry	Data Entry
SectA

If this doesn't work for you, then please post your data uisng the xl2bb add in (Link Below). It makes it easier for the forum to recreate your scenario.

Thank in advance, and welcome to the forum.