Using arrows to show data change between two columns

Sly1980

New Member
Joined
Nov 3, 2017
Messages
14
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

New Member
Joined
Nov 3, 2017
Messages
14
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
HIJKL
2No ActionAction
3GG10
4GY1-1
5GR1-2
6YY20
7YG21
8YR2-1
9RR30
10RG32
11RY31
12GR1-2
13YY20
14RG32
15RG32
16YY20
17GR1-2
18YY20
19RR30
20YG21
21YR2-1
22  
Sheet5
Cell Formulas
RangeFormula
K3:K21K3=IFERROR(LOOKUP(INDEX(Sheet1!B$141:BL$141,ROWS(F$3:F3)),{"G","R","Y"},{1,3,2}),"")
L3:L21L3=IFERROR(K3-(LOOKUP(INDEX(Sheet1!B$142:BL$142,ROWS(F$3:F3)),{"G","R","Y"},{1,3,2})),"")
K22K22=IFERROR(LOOKUP(INDEX(Sheet1!B$1:BK$1,ROWS(F$3:F22)),{"G","R","Y"},{1,3,2}),"")
L22L22=IFERROR(K22-(LOOKUP(INDEX(Sheet1!B$142:BK$142,ROWS(F$3:F22)),{"G","R","Y"},{1,3,2})),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K3:K25Other TypeIcon setNO
L3:L25Other TypeIcon setNO
K3:K25Other TypeIcon setNO


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.


1587482624202.png
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,159
Office Version
  1. 2013
Platform
  1. Windows
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
FGHIJKLMNO
2No ActionActionNo ActionActionEffectNo ActionActionNo ActionActionEffect
3-10GY10-1YG-1
401YR1-11GR2
50-1YG-1-10GY1
61-1RG-2-11GR2
7-10GY1-1-1GG0
801YR101YR1
Sheet23
Cell Formulas
RangeFormula
F3:G8, K3:L8F3=LOOKUP(H3,{"G","R","Y"},{-1,1,0})
J3:J8, O3:O8J3=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.

Screenshot 2020-04-21 at 19.14.11.png

Screenshot 2020-04-21 at 19.15.30.png

Screenshot 2020-04-21 at 19.16.24.png

Hope that helps.
 

Sly1980

New Member
Joined
Nov 3, 2017
Messages
14
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
FGHIJKLMNO
2No ActionActionNo ActionActionEffectNo ActionActionNo ActionActionEffect
3-10GY10-1YG-1
401YR1-11GR2
50-1YG-1-10GY1
61-1RG-2-11GR2
7-10GY1-1-1GG0
801YR101YR1
Sheet23
Cell Formulas
RangeFormula
F3:G8, K3:L8F3=LOOKUP(H3,{"G","R","Y"},{-1,1,0})
J3:J8, O3:O8J3=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,543
Members
416,923
Latest member
jarri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top