Display Column Header Name of latest Status

[surkdidat]

Hopefully the solution is easier than the explanation!

I have a tab called “DataSheet”

In Column A is a Work Item Name.

In Columns B:E are the various stages of how the work itemis progressing

These columns will either contain a value, or be blank (thevalue can vary)

In Row 1 is the various stages the work item can be.

I have a second tab called “Stats”

In this tab, I have the name of any work item that is inprogress.

What I need is for the sheet to display the status its in.

DATASHEET

--COL_A--|--COL_B--|--COL_C--|--COL_D--|--COL_E--|
ItemName|Stage_1|-Stage_2-|-Stage_3-|-Stage_4-|
-----------------------------------------------------------------------
AAAAAAAA|--Comp--|--Comp--|
BBBBBBBBB|--Comp--|
CCCCCCCCCC|--Comp--|--Comp--|--Comp--|



STATS SHEET

AAAAAAAAA|--- (Should display “STAGE_2” )
BBBBBBBBB| ----(Should Display “Stage_1”)
CCCCCCCCCC| ---- (Should Display “Stage 3” )


Hope that makes sense!

 

[steve the fish]

Try:

=INDEX(Datasheet!$A$1:$Z$1,MATCH("zzz",INDEX(Datasheet!$A$2:$Z$1000,MATCH(A2,Datasheet!$A$2:$A$1000,0),0)))

 

[AlanY]

try this

Book1
	A	B	C	D	E
1	DATASHEET
2
3	ItemName	Stage_1	Stage_2	Stage_3	Stage_4
4	AAAAAAAA	Comp	Comp
5	BBBBBBBBB	Comp
6	CCCCCCCCCC	Comp	Comp	Comp
7
8
9
10	STATS SHEET
11
12	AAAAAAAA	Stage_2
13	BBBBBBBBB	Stage_1
14	CCCCCCCCCC	Stage_3
Sheet3

Cell Formulas
Range	Formula
B12	=LOOKUP(2,1/(INDEX($B$4:$E$6,MATCH(A12,$A$4:$A$6,0),)>0),B$3:E$3)

 

[surkdidat]

Thanks as always - superb help and very quick in the forum!

 

[surkdidat]

Apologies, a quick followup question, if the cell contains the word "Returned" can the formaula be adjusted to either include "Returned" on the end of the result (ideally) or go back one cell to the left and display that heading?

try this

	A	B	C	D	E
1	DATASHEET
2
3	ItemName	Stage_1	Stage_2	Stage_3	Stage_4
4	AAAAAAAA	Comp	Comp
5	BBBBBBBBB	Comp
6	CCCCCCCCCC	Comp	Comp	Comp
7
8
9
10	STATS SHEET
11
12	AAAAAAAA	Stage_2
13	BBBBBBBBB	Stage_1
14	CCCCCCCCCC	Stage_3

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B12 =LOOKUP(2,1/(INDEX($B$4:$E$6,MATCH(A12,$A$4:$A$6,0),)>0),B$3:E$3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

like this?

Book1
	A	B	C	D	E
1	DATASHEET
2
3	ItemName	Stage_1	Stage_2	Stage_3	Stage_4
4	AAAAAAAA	Comp	Returned
5	BBBBBBBBB	Comp
6	CCCCCCCCCC	Comp	Comp	Comp
7
8
9
10	STATS SHEET
11
12	AAAAAAAA	Returned
13	BBBBBBBBB	Stage_1
14	CCCCCCCCCC	Stage_3
Sheet3

Cell Formulas
Range	Formula
B12	=IF(ISNUMBER(MATCH("Returned",INDEX($B$4:$E$6,MATCH(A12,$A$4:$A$6,0),),0)),"Returned", LOOKUP(2,1/(INDEX($B$4:$E$6,MATCH(A12,$A$4:$A$6,0),)>0),B$3:E$3))

 

[surkdidat]

SUPERB!! I really appreciate this!!

like this?

	A	B	C	D	E
1	DATASHEET
2
3	ItemName	Stage_1	Stage_2	Stage_3	Stage_4
4	AAAAAAAA	Comp	Returned
5	BBBBBBBBB	Comp
6	CCCCCCCCCC	Comp	Comp	Comp
7
8
9
10	STATS SHEET
11
12	AAAAAAAA	Returned
13	BBBBBBBBB	Stage_1
14	CCCCCCCCCC	Stage_3

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B12 =IF(ISNUMBER(MATCH("Returned",INDEX($B$4:$E$6,MATCH(A12,$A$4:$A$6,0),),0)),"Returned", LOOKUP(2,1/(INDEX($B$4:$E$6,MATCH(A12,$A$4:$A$6,0),)>0),B$3:E$3)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

you're welcome