Value of Cell to equal Cell coloured Green when the same row

PeterWays

New Member
Joined
Jul 21, 2015
Messages
5
Hi Guys,

Is there a way of creating a formula to show the value of which ever cell is coloured Green within a the same row

I have a Spreadsheet has a column to the right that shows the selected value for each row. This value currently is just a simple =A2 value. A2 is then coloured Green to show which value was used.

B2 would have an another value but if its not chosen it would just be left blank for future reference.

Survey 1Survey 2SELECTED RESULTS
20040 (GREEN)50020040 {Formula required}
30005100 (green)5100 {Formula required}

<tbody>
</tbody>





Thanks for all your help
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Go to Formulas --> Name Manager --> New
--> In the name box type, CellColorL
--> In the Refers to: box, type, =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1)) and select Ok.

Then:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Survey 1</td><td style=";">Survey 2</td><td style=";">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #00B050;;">20040</td><td style="text-align: right;;">500</td><td style="text-align: right;;">20040</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3000</td><td style="text-align: right;background-color: #00B050;;">5100</td><td style="text-align: right;;">5100</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=CHOOSE(<font color="Blue">VLOOKUP(<font color="Red">CellColorL,E2:F3,2,0</font>),A2,B2</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">CellColorL</th><td style="text-align:left">=GET.CELL(<font color="Blue">63,OFFSET(<font color="Red">INDIRECT(<font color="Green">"RC",FALSE</font>),0,-1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

PeterWays

New Member
Joined
Jul 21, 2015
Messages
5
Thanks Heaps ben. worked great. What if i wanted it to do it the other way. Say.......

I have a Spreadsheet that a column to the right (THAT IS BLANK) needs to equal the value within the cell along the row that the user selects as green. The cell thats green changes depending on which survey is used for pricing for example.

Its shows the selected green cell value for each row. This formula currently is just a simple '=A2' value. Because A2 is coloured Green to show which value was used.


B2 has an another value but if its not chosen because it would just be left blank for future price comparison reference.

Is there a way of doing this?


Once again thanks in advance.
 

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Can you show an example of what you're looking for in this second example? I don't quite follow. Which columns are we looking through to find the green cell?
 

PeterWays

New Member
Joined
Jul 21, 2015
Messages
5

ADVERTISEMENT

Hope this helps explain it

I just need to see how the formula is written and i hope i will able to apply it to a whole template with multiple rows.

I need cells in Column D to have a condition (logical test) to see what is shown. i have given all the different out comes I am after.

Thanks

Excel 2010
ABCD
1Survey 1Survey 2Survey 3SELECTED COST
2$20040$500$4113=A2 [because it is green]
3$3000$51005100=B3 [because it is green]
4=BLANK because there isnt any cells coloured green and no values
5512151134551=TEXT "select one"

<tbody>
</tbody>
 

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Try this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Survey 1</td><td style=";">Survey 2</td><td style=";">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #00B050;;">20040</td><td style="text-align: right;;">500</td><td style="text-align: right;;">20040</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3000</td><td style="text-align: right;background-color: #00B050;;">5100</td><td style="text-align: right;;">5100</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #00B050;;">5121</td><td style="text-align: right;background-color: #00B050;;">5113</td><td style=";">Select One</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">CellColorL=10,CellColor2L=10</font>),"Select One",IF(<font color="Red">CellColorL=10,B2,IF(<font color="Green">CellColor2L=10,A2,""</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">CellColor2L</th><td style="text-align:left">=GET.CELL(<font color="Blue">63,OFFSET(<font color="Red">INDIRECT(<font color="Green">"RC",FALSE</font>),0,-2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">CellColorL</th><td style="text-align:left">=GET.CELL(<font color="Blue">63,OFFSET(<font color="Red">INDIRECT(<font color="Green">"RC",FALSE</font>),0,-1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

PeterWays

New Member
Joined
Jul 21, 2015
Messages
5

ADVERTISEMENT

Hi Ben, Thanks That works if I have only a few cells of values and a few Rows. Say if I have 7 survey values and Multiple Rows??

Is there a way of make it more easier to use and apply to multiple Rows and even drag the formula down and for it still to work?
 

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Honestly PeterWays,

This isn't a very robust formula. It's a workaround using an obsolete formula GET.CELL, because there is no formula that returns fill color. You're not going to get the same kind of functionality that you would with normal functions. Can I ask why you would want to calculate based on the color green? What determines which cells get shaded green, and maybe we can use that same logic in the formula instead of chasing after the fill color?
 

PeterWays

New Member
Joined
Jul 21, 2015
Messages
5
unfortunately it is a user selected. the logic behind which cell is green (selected) is based on a number of things. EG value, inclusions & exclusions in the quote. (things that aren't easily put into a formula) what if we use another way of showing the selected cell (instead of the green)

Thanks
 

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Something like this would certainly be a lot easier to work with:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Survey 1</td><td style=";">Survey 2</td><td style=";">Selected Survey</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">20040</td><td style="text-align: right;;">500</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">5100</td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Forum statistics

Threads
1,136,637
Messages
5,676,924
Members
419,659
Latest member
ShalDRH

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