Formula to add a value to a cell, based on cell to the left's color

**hellinabucket** · 12-10-2013, 09:27 PM

I would like to add a value to a cell. The value in this cell will either be 1, 2, 3 or 4.
The value of this cell needs to be "1", if the cell to its left is "Red".
The value of this cell needs to be "2", if the cell to its left is "Purple".
The value of this cell needs to be "3", if the cell to its left is "Green".
The value of this cell needs to be "4", if the cell to its left is "Blue".

Thank you so much for your help with this, I really appreciate whatever you can share. I believe I need to find the HEX values for the colors, but that is no problem, I just need the formula of the function if you know it. Thanks again.

**vlady** · 12-10-2013, 09:54 PM

How did you arrive in those colors any explanations? did you used conditional formatting?

if you just colored them, you cannot use formula as you are requesting. Though vba could do it. PM me and i will move your thread to vba/programming subforum.

Regards,
Vladimir

**hellinabucket** · 12-10-2013, 10:01 PM

Yes, I used conditional formatting. In column R, there is a pull-down list menu with 4 choices. If choice one is selected, then the cell turns red.
I would like the cell next to it to have a number "1" in it, if the cell to its left is red.

**vlady** · 12-10-2013, 10:08 PM

You can then base the formula in that conditional formatting rule.

something like this maybe

=if(R1=choice1,1,if(R1=choice2,2,if(r1=choice3,3,4)))

so you will not be "referring" to the cell with the colors instead in column R choices.

**hellinabucket** · 12-10-2013, 10:54 PM

thx, I am almost there but I keep getting #NAME error.
ug I thank you for your patience, If you have a suggestion, I will try

**hellinabucket** · 12-10-2013, 11:04 PM

I then tried to add that formula to a new conditional format, but its not working yet

**vlady** · 12-10-2013, 11:06 PM

my mistake

=if(R1=choice1,1,if(R1=choice2,2,if(r1=choice3,3,4)))

you have to put the real choices on those pull down list and if they are text be sure to enclose then to quotation marks
say your drop down list is A,B,C,D located in R1

=if(R1="A",1,if(R1="B",2,if(R1="C",3,4)))

**hellinabucket** · 12-10-2013, 11:15 PM

My actual cell is F3 so this is what I wrote based on your suggestion above

=if(F3=H23,1,if(F3=H24,2,if(F3=H25,3,if(F3=H26,4)))

oh my goodness, it worked. If it you have time, can we add color to this new cell also? such as

=if(F3=H23,1 "color =red", etc..?

**vlady** · 12-10-2013, 11:23 PM

thats conditional formatting
in the above post

can we add color to this new cell also

in CF use formula.
=new cell=1 -->select color
then create another cf for
=new cell=2
...
say the new cell is D1
so in cf
=d1=1 -> select color
=d1=2 -> select color
=d1=3 -> select color
=d1=4 -> select color

**Tony Valko** · 12-10-2013, 11:26 PM

Originally Posted by hellinabucket

=if(F3=H23,1,if(F3=H24,2,if(F3=H25,3,if(F3=H26,4)))

This may work and is a few keystrokes shorter.

=MATCH(F3,H23:H26,0)

**hellinabucket** · 12-10-2013, 11:33 PM

wow
this was my first time on this forum
I really appreciate your help. cheers to you

**arlu1201** · 12-11-2013, 01:55 AM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

Formula to add a value to a cell, based on cell to the left's color

LinkBack

Thread Tools

Rate This Thread

Display

Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Re: Formula to add a value to a cell, based on cell to the left's color

Thread Information

Users Browsing this Thread

Similar Threads

Color tab based on cell value but ignore formula

[SOLVED] Color-format a cell which contains data in a column based on the color of another cell

Conditional Formatting: Change color of a cell based on the color of another cell

Color code cell with case statement and datestamp cell(s) to the left

formula based on cell background color

Bookmarks

Bookmarks

Posting Permissions