[SOLVED] Lookup Value against Column Heading and value in intesecting row

[filmnoir]

Wonder if anyone can help?

Q1 Q2 Q3 Q4 FTE Manually Entered End Date Check Date
1 3 5 1 10 Q4 Q4
0 1 1 0 2 Q2 Q3


What I would like to do is use the row one value in column Manually Entered End Date Q4 and look against the column heading Q1 to Q4 and check that the corresponding Q4 Row Value is more than zero. This will then conditional format the check date row vale Q4 green.

In the second row, it would take Q2, look and see that Q3 is the last date used for FTE and populate it in check date and highlight the Q3 in Check Date Red.

Any suggestions appreciated?

[filmnoir]

Attatched is an example.

[DGagnon]

im not sure i understand, what value is being checked? in one example you are looking at the same Q and in the other example you are looking at the next Q.

[filmnoir]

I want to check that the value in column I corresponds to the correct colum heading in D to G, where they have populated FTE resource. Where there is a discrepency I need to highlight it.

So in the row 4, they have entered in I4 Q2. Checking the last full value of FTE resource used is in F4 value Q3. The check highlights Q3 and highlights it red.

[DGagnon]

ok, you can use this formula in Col J
=IF(G3>0,G$2,IF(F3>0,F$2,IF(E3>0,E$2,IF(D3>0,D$2))))

then conditional format using these:
=$J3=$I3 - For Green
=$J3<>$I3 - For Red

[filmnoir]

Ah ok I see where it's going. There is a slight snag on I6 as I've replicated some more data to test.

[filmnoir]

My apolgies worked it out! Conditional formatting doesnt replicate!

Many Thanks

[DGagnon]

glad to assist, good luck with the project.