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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
712
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

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

Well-known Member
Joined
Mar 22, 2020
Messages
712
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
=LOOKUP(YourFormula;{"G","R","Y"},{1,-1,0})
 

Sly1980

New Member
Joined
Nov 3, 2017
Messages
14

ADVERTISEMENT

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

Well-known Member
Joined
Mar 22, 2020
Messages
712
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEF
1RYG
2
3-1
40
51
6
Sheet1
Cell Formulas
RangeFormula
E3:E5E3=LOOKUP(INDEX(Sheet1!B$1:E$1,ROWS(F$3:F3)),{"G";"R";"Y"},{1;-1;0})
 

Sly1980

New Member
Joined
Nov 3, 2017
Messages
14

ADVERTISEMENT

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}).
1587470325912.png
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
712
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

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

1587479381452.png


1587479560235.png
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,173
Office Version
  1. 2013
Platform
  1. Windows
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.

Screenshot 2020-04-21 at 15.52.03.png


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

Watch MrExcel Video

Forum statistics

Threads
1,130,191
Messages
5,640,762
Members
417,165
Latest member
Hilders1

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