Bring back last grey colour column

[Sat2021]

Hi

I have a large spreadsheet with dates going across the top and plot numbers on the side.

Then across the top i have dates and the cells have various number 1-12 (does include 'BC & 'LC' & 'CP') as well.

Various cells are colored in different colors and for example one row might have 10 cells with the color grey what i want to do is bring back the value from the last grey colored cell for each row?

Please see attached example. So for the first line i would like it bring back 'C&P' and for the fourth line down id like it bring back 12.

Thanks,

Sat

 

[mart37]

On sheet CELLCOLOR i have made a blueprint with the cellcollors of sheet DATA.
I have used a old xlm-macro GET.CELL. This macro is only working in a name. So you need the namemanager to create a name with a formula.
The name: CellColor
The formula: =GET.CELL(63,INDIRECT(ADDRESS(ROW(),COLOM(),,,"data")))

In the sheet DATA i use the function SUMPRODUCT for the last value of the colornumber 15. Maybe you have an other number.
=INDEX(C2:M2,1,SUMPRODUCT(LARGE((data!C13:M13=15)*(COLUMN(C13:M13)-COLUMN(C13)+1),1)))

Map3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Plot No.	Last value	18-jan	25-jan	1-feb	8-feb	15-feb	22-feb	1-mrt	8-mrt	15-mrt	22-mrt	29-mrt
2	1	2	8	7	2	5	10	2	1	1	8	10	4
3	2	8	9	3	6	8	6	6	7	10	1	5	10
4	3	C&P	5	9	5	7	7	4	9	C&P	8	7	7
5	4	10	9	7	3	10	2	1	4	3	2	4	1
6	5	3	5	4	5	10	7	3	8	5	7	3	7
7	6	2	6	2	3	6	5	10	2	1	1	7	5
8	7	BC	8	4	5	BC	2	4	9	7	4	8	10
9	8	9	1	5	2	9	2	6	10	3	3	9	10
10	9	1	5	1	6	5	8	6	2	2	4	9	7
11	10	9	5	9	8	2	6	10	6	5	5	7	1
data

Cell Formulas
Range	Formula
B2:B11	B2	=INDEX(C2:M2,1,SUMPRODUCT(LARGE((data!C13:M13=15)*(COLUMN(C13:M13)-COLUMN(C13)+1),1)))

Map3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	0	0	0	0	0	0	0	0	0	0	0	0	0
2	0	0	15	15	15	15	15	15	36	36	36	36	36
3	0	0	15	15	15	15	36	36	36	36	36	36	36
4	0	0	15	15	15	15	15	15	15	15	36	36	36
5	0	0	15	15	15	15	36	36	36	36	36	36	36
6	0	0	15	15	15	15	15	15	36	36	36	36	36
7	0	0	15	15	36	36	36	36	36	36	36	36	36
8	0	0	15	15	15	15	36	36	36	36	36	36	36
9	0	0	15	15	15	15	36	36	36	36	36	36	36
10	0	0	15	15	36	36	36	36	36	36	36	36	36
11	0	0	15	15	36	36	36	36	36	36	36	36	36
12
13	Namemanager
14	CellColor:	=GET.CELL(63,INDIRECT(ADDRESS(ROW(),COLOM(),,,"data")))
cellcolor

Cell Formulas
Range	Formula
A1:M11	A1	=CellColor

 

[Sat2021]

could you send me your excel spreadsheet with working please?

 

[mart37]

CellColor