Using arrows to show data change between two columns

[Sly1980]

Hi there
I would like to use arrows to show the difference between two columns - the columns have "R", "Y", and "G" to indicate red, yellow, and green in terms of status. The two columns would have status without actions and status with actions - I would like to visually show whether there has been a change as a result of the actions. For example, if the status without actions is "R" and the status with actions become "G", then the arrow should show an upwards pointing green arrow. I know this is easily done with numbers but I don't know whether this can be done with letters.

 

[Sly1980]

Yes, the ROWS function is generating a number greater than the columns in the INDEX range.
In Sly1980 's example Index formula there were 63 columns B:BL
@Sly1980 you do not need to change the G,Y,R generating index part of the formula when you include in the new Lookup.

I am still not sure what you want. Do you want the Arrow in the Action column to indicate that progress compared to the No Action status, is positive. negative or same?

Does this approach help......
Columns H & I are just for illustration of what the G,Y,R values are.
Column K is colour coded Action Status
Column L is Coloured arrows indicating progress.

Book1
	H	I	J	K	L
2				No Action	Action
3	G	G		1	0
4	G	Y		1	-1
5	G	R		1	-2
6	Y	Y		2	0
7	Y	G		2	1
8	Y	R		2	-1
9	R	R		3	0
10	R	G		3	2
11	R	Y		3	1
12	G	R		1	-2
13	Y	Y		2	0
14	R	G		3	2
15	R	G		3	2
16	Y	Y		2	0
17	G	R		1	-2
18	Y	Y		2	0
19	R	R		3	0
20	Y	G		2	1
21	Y	R		2	-1
22
Sheet5

Cell Formulas
Range	Formula
K3:K21	K3	=IFERROR(LOOKUP(INDEX(Sheet1!B$141:BL$141,ROWS(F$3:F3)),{"G","R","Y"},{1,3,2}),"")
L3:L21	L3	=IFERROR(K3-(LOOKUP(INDEX(Sheet1!B$142:BL$142,ROWS(F$3:F3)),{"G","R","Y"},{1,3,2})),"")
K22	K22	=IFERROR(LOOKUP(INDEX(Sheet1!B$1:BK$1,ROWS(F$3:F22)),{"G","R","Y"},{1,3,2}),"")
L22	L22	=IFERROR(K22-(LOOKUP(INDEX(Sheet1!B$142:BK$142,ROWS(F$3:F22)),{"G","R","Y"},{1,3,2})),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K3:K25	Other Type	Icon set		NO
L3:L25	Other Type	Icon set		NO
K3:K25	Other Type	Icon set		NO

Edit: Ignore the formulas shown for K22 L22 , they are irrelevant.

View attachment 11972

The values can be hidden by narrowing the columns or setting the cell text to white.

Hi Snakehips
Thank you for the response. To avoid confusion, I will outline what I need (we can forget the index formula if it would be easier). I need the data that is entered in the input sheet to show in a summary sheet as traffic lights. Example of the input sheet below. So, only data from the rows with "R", "Y", "G" are pulled through for traffic light conversion. For the names, company, etc, I can still use index as this data is represented as is. The original data needs to additionally, be transposed on the summary sheet. Another complication is that in cases where no "R", "Y", or "G" is entered, I want a traffic light to show this - I have been using a dark grey to denote this.

Adding onto this, my original question in this thread - would it be possible to add a column next to the two columns (status without actions & status with actions) that shows whether there is a difference between the two "action" columns, i.e., if it is two yellows, or two reds, or two greens, it would show a line or dash; if red to yellow, red to green, or yellow to green, it would show a green up arrow; if yellow to red, or green to red, it would show a down arrow. If blank, it would show nothing. I hope that this is possible, if not, hopefully the traffic lights can at least work.

 

[Snakehips]

Hows about this...
G,Y,R values in H,I M,N etc as you are doing now.
Formulas as below in F, G, J, K, L, O

Apply Icon Set Conditional Formatting to those columns typically as below.

Book1
	F	G	H	I	J	K	L	M	N	O
2	No Action	Action	No Action	Action	Effect	No Action	Action	No Action	Action	Effect
3	-1	0	G	Y	1	0	-1	Y	G	-1
4	0	1	Y	R	1	-1	1	G	R	2
5	0	-1	Y	G	-1	-1	0	G	Y	1
6	1	-1	R	G	-2	-1	1	G	R	2
7	-1	0	G	Y	1	-1	-1	G	G	0
8	0	1	Y	R	1	0	1	Y	R	1
Sheet23

Cell Formulas
Range	Formula
F3:G8, K3:L8	F3	=LOOKUP(H3,{"G","R","Y"},{-1,1,0})
J3:J8, O3:O8	J3	=IFERROR((LOOKUP(I3,{"G","R","Y"},{1,3,2}))-(LOOKUP(H3,{"G","R","Y"},{1,3,2})),"")

NOTE: The XL2BB above cannot show the conditional formatting.
The actual sheet is as below.

Hope that helps.

 

[Sly1980]

Hows about this...
G,Y,R values in H,I M,N etc as you are doing now.
Formulas as below in F, G, J, K, L, O

Apply Icon Set Conditional Formatting to those columns typically as below.

Book1
	F	G	H	I	J	K	L	M	N	O
2	No Action	Action	No Action	Action	Effect	No Action	Action	No Action	Action	Effect
3	-1	0	G	Y	1	0	-1	Y	G	-1
4	0	1	Y	R	1	-1	1	G	R	2
5	0	-1	Y	G	-1	-1	0	G	Y	1
6	1	-1	R	G	-2	-1	1	G	R	2
7	-1	0	G	Y	1	-1	-1	G	G	0
8	0	1	Y	R	1	0	1	Y	R	1
Sheet23

Cell Formulas
Range	Formula
F3:G8, K3:L8	F3	=LOOKUP(H3,{"G","R","Y"},{-1,1,0})
J3:J8, O3:O8	J3	=IFERROR((LOOKUP(I3,{"G","R","Y"},{1,3,2}))-(LOOKUP(H3,{"G","R","Y"},{1,3,2})),"")

NOTE: The XL2BB above cannot show the conditional formatting.
The actual sheet is as below.

View attachment 11993
View attachment 11995
View attachment 11996
Hope that helps.

Hi Snakehips
This solution works perfectly on the cells I've tried. Thank you very much! I will now convert the worksheet, if I have any issues, I will add a reply on this. Thank you once again!

 

[Snakehips]

Great! Glad @GraH and I were able to help.

 

[GraH]

You did the hard part Snakehips?