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.

 

[GraH]

I know this is easily done with numbers but I don't know whether this can be done with letters.

Then why not use "-1,0,1" for "R", "Y", "G"? And format cell as "G","R","Y" (positive, negative, zero)?

 

[Sly1980]

Hi there
Perhaps I should mentioned - the "R", "Y", "G" values are pulled through from another sheet using the index formula - this input sheet is updated by multiple team members. How would I reference the indexed result in a cell (which would be "R", "Y", "G") to a number?

 

[GraH]

=LOOKUP(YourFormula;{"G","R","Y"},{1,-1,0})

 

[Sly1980]

Hi there
I've tried pasting my formula in there but it gives me an error. My formula for one cell is:
=INDEX(Sheet1!B$141:BL$141,ROWS(F$3:F3))
I'm using the index formula as there is quite a bit of data on the original input sheet and the summary of the data which includes "R", "Y", "G" is transposed. I'm not sure if the formulas can be used together?

 

[GraH]

Learned something new as I never saw a construct like =INDEX(Sheet1!B$141:BL$141,ROWS(F$3:F3)).
I tried on a range, at it seems to work like I said.

Book1
	A	B	C	D	E	F
1		R	Y	G
2
3					-1
4					0
5					1
6
Sheet1

Cell Formulas
Range	Formula
E3:E5	E3	=LOOKUP(INDEX(Sheet1!B$1:E$1,ROWS(F$3:F3)),{"G";"R";"Y"},{1;-1;0})

 

[Sly1980]

Hi Thanks!
I tried the formula and it works but it seems to break after the 4th line for some reason - cannot understand why but it give me a #ref error. I've tried it on another column as well and it does the same after the fourth value. Puzzled? The formula just above the highlighted one is =LOOKUP(INDEX(Sheet1!B$8:E$8,ROWS(K$3:K6)),{"G";"R";"Y"},{1;-1;0}).

 

[GraH]

It's turning out to be a wild-goose chase for me. That index construct puzzles me a bit.
=LOOKUP(INDEX(Sheet1!B$8:E$8,ROWS(K$3:K7)),{"G";"R";"Y"},{1;-1;0})

This contains 4 columns: B, C ,D , E
The red part returns "5" (3, 4 , 5, 6 ,7)

How can that work? -> #REF! seems logic in this case.

Can you show a sample range of the data on which that index is applied?

 

[Sly1980]

It's turning out to be a wild-goose chase for me. That index construct puzzles me a bit.
=LOOKUP(INDEX(Sheet1!B$8:E$8,ROWS(K$3:K7)),{"G";"R";"Y"},{1;-1;0})

This contains 4 columns: B, C ,D , E
The red part returns "5" (3, 4 , 5, 6 ,7)

How can that work? -> #REF! seems logic in this case.

Can you show a sample range of the data on which that index is applied?

Hi GraH
I've created a sample data sheet which I've pasted below. The input cells have no formulas, just a drop down arrow with the "R", "Y", "G" selections or the data can be typed in. The result is shown in the second screengrab. The formula for the highlighted cell is shown in the formula bar. I've highlighted a portion of the formula, the range runs until S as the team members can add additional columns as time goes by but if it was strictly this data set, the formula would show G instead of S. The traffic lights, just for explanation, are done by means of conditional formatting using Webdings (letter n) and allocate the colour based on the corresponding column, i.e., column F shows the result of column H and column G shows the result of column I. The columns with the "R", "Y", and "G" are then hidden in the final viewable sheet. Additionally, if the input cell is blank, the traffic light will format to a dark grey colour to indicate incomplete.

Perhaps there is a better way of pulling the data through and getting it to work, unsig Index was just the first solution I found. I can then just add the formula for the first cell in a column and then drag it down. Having the fourth robot of incomplete also complicates things a bit and I would need a workaround for this as well if possible. If not, we can try for the three colours now. I hope that this is clear enough. Thanks for your help thus far ?

 

[Snakehips]

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.

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