Formula to get the result depending on the color of cell

[RAJESH1960]

Hello, I have this data in excel sheet where column E has some values. Some of the cells are of 2 different colors. I am trying to get a formula in column D where if the color of the cell is Pink I want it to display B, If it is yellow it should display C, and all other cells it should display A as in the image.

Book1
	A	B	C	D	E
1	1	2	3	4	5
2				A	120.00
3				A	220.00
4				B	250.00
5				B	450.00
6				A	600.00
7				A	1080.00
8				A	1080.00
9				A	1350.00
10				B	1350.00
11				B	1440.00
12				A	1460.00
13				A	3560.00
14				C	3560.00
15				C	3560.00
16				A	3750.00
17				A	4640.00
18				A	4850.00
19				C	12558.00
20				C	29400.00
Query

 

[etaf]

how are the colours set, is this set using conditional formatting , if so then can use the same , otherwise VBA would be needed, not my expertise

 

[RAJESH1960]

how are the colours set, is this set using conditional formatting , if so then can use the same , otherwise VBA would be needed, not my expertise

When the data is entered, the cells are colored manually as per requirement. There are more than 10,000 rows. Please I would prefer a formula rather than a VBA.

 

[Joe4]

When the data is entered, the cells are colored manually as per requirement. There are more than 10,000 rows. Please I would prefer a formula rather than a VBA.

It is not possible. Formulas can only run off of values in cells, not the formatting applied to the cells.
If the color is being set manually, a VBA solution is your only option.

 

[RAJESH1960]

It is not possible. Formulas can only run off of values in cells, not the formatting applied to the cells.
If the color is being set manually, a VBA solution is your only option.

I found the solution. Thanks. I filtered the data, each time by one color and entered the required data. Thanks for your response etaf and Joe4

 

[Fluff]

You could use the old Excel4macro functions

+Fluff 1.xlsm
	A	B	C	D	E
1	1	2	3	4	5
2				A	120
3				A	220
4				B	250
5				B	450
6				A	600
7				C	1080
8				A	1080
9				A	1350
10				B	1350
11				B	1440
12				A	1460
13				A	3560
14				C	3560
15				C	3560
16				A	3750
17				A	4640
18				A	4850
19				C	12558
20				B	29400
Master

Cell Formulas
Range	Formula
D2:D20	D2	=IF(GetColour=40,"B",IF(GetColour=6,"C","A"))

Where GetColour is a defined name using

=GET.CELL(63,INDIRECT("rc[1]",0))

 

[Joe4]

Glad you figured out a manageable workaround.

 

[RAJESH1960]

You could use the old Excel4macro functions

+Fluff 1.xlsm
	A	B	C	D	E
1	1	2	3	4	5
2				A	120
3				A	220
4				B	250
5				B	450
6				A	600
7				C	1080
8				A	1080
9				A	1350
10				B	1350
11				B	1440
12				A	1460
13				A	3560
14				C	3560
15				C	3560
16				A	3750
17				A	4640
18				A	4850
19				C	12558
20				B	29400
Master

Cell Formulas
Range	Formula
D2:D20	D2	=IF(GetColour=40,"B",IF(GetColour=6,"C","A"))

Where GetColour is a defined name using

=GET.CELL(63,INDIRECT("rc[1]",0))

Thanks Fluff. But it is too complicated for me as I am not good at colors and color codes. Maybe it will help when & if we don't have a filter option for a data. I would like to go with the filter option only.

 

[RAJESH1960]

Glad you figured out a manageable workaround.

Learnt everything from you experts and still learning.?

 

[Fluff]

I would like to go with the filter option only.

That's fine & thanks for the feedback.