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

[PeterWays]

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.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Survey 1[/TD]
[TD]Survey 2[/TD]
[TD]SELECTED RESULTS[/TD]
[/TR]
[TR]
[TD]20040 (GREEN)[/TD]
[TD]500[/TD]
[TD]20040 {Formula required}[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]5100 (green)[/TD]
[TD]5100 {Formula required}[/TD]
[/TR]
</tbody>[/TABLE]





Thanks for all your help

 

[bschwartz]

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:

Excel 2010
	A	B	C
1	Survey 1	Survey 2	Result
2	20040	500	20040
3	3000	5100	5100
Sheet1

Cell Formulas
Range	Formula
C2	=CHOOSE(VLOOKUP(CellColorL,E2:F3,2,0),A2,B2)

Named Ranges
Name	Refers To	Cells
CellColorL	=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

 

[PeterWays]

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]

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]

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

	A	B	C	D
Survey 1	Survey 2	Survey 3	SELECTED COST

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #00B050, align: right"]$20040[/TD]
[TD="align: right"]$500[/TD]
[TD="align: right"]$4113[/TD]
[TD="align: right"]=A2 [because it is green][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]$3000[/TD]
[TD="bgcolor: #00B050, align: right"]$5100[/TD]
[TD="align: right"]5100[/TD]
[TD="align: right"]=B3 [because it is green][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]=BLANK because there isnt any cells coloured green and no values[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #00B050, align: right"]5121[/TD]
[TD="bgcolor: #00B050, align: right"]5113[/TD]
[TD="align: right"]4551[/TD]
[TD="align: right"]=TEXT "select one"[/TD]

</tbody>

 

[bschwartz]

Try this:

Excel 2010
	A	B	C
1	Survey 1	Survey 2	Result
2	20040	500	20040
3	3000	5100	5100
4
5	5121	5113	Select One
Sheet1

Cell Formulas
Range	Formula
C2	=IF(AND(CellColorL=10,CellColor2L=10),"Select One",IF(CellColorL=10,B2,IF(CellColor2L=10,A2,"")))

Named Ranges
Name	Refers To	Cells
CellColor2L	=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-2))
CellColorL	=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

 

[PeterWays]

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]

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]

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]

Something like this would certainly be a lot easier to work with:

Excel 2010
	A	B	C
1	Survey 1	Survey 2	Selected Survey
2	20040	500	1
3	3000	5100	2
Sheet1