asked on

Data Validation based on formula

I have a variable saved as a value, it is an average. lets call it MyAvg.

I would like to apply a data validation on a column (D) that uses MyAvg that:

If value is >15% of MyAvg = Red
If value is between >10 of MyAvg but <15% of MyAvg = Yellow
If value is between <10 of MyAvg and -10% of MyAvg = Green

How would I write a formula in the Data Validation, to use MyAvg?
Ideally, or another option I would like to explore this using some VBA code against a cell... but was going to try both ways.

Please advise and thanks.

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

Does it matter if MyAvg is a single value? I do have another name that is a range that I used to determine the MyAvg, that I can use?

RWayneH

ASKER

I actually prefer the vba to apply to a cell, because as data is appended to the sheet the averages will chg... I only add 4 lines to the table at a time, and would like to evaluate a cell to a single MyAvg value, changing the color based on the three rules. I have 6 different MyAvg values for six different columns, but I can apply the one example to each column. Then I will apply a differnt filter and do it again.

Is it possible to try the vba cell to color option? I already have all the averages defined to elvaluate a cell based on a cell. Also with this option I do not have to refilter the data.

I would like to evaluate ActiveCell value to the MyAvg value, to get its color.... I can move the ActiveCell with offset commands.

RWayneH

ASKER

I think that the two value comparison can be done in vba, to give the cell a color. One being the ActiveCell and the other being a collected average, saved as a variable. The more I think through how this is going to be used the more I am not going to be able to do this by the whole column at a time.

Subodh Tiwari (Neeraj)

Does it matter if MyAvg is a single value? I do have another name that is a range that I used to determine the MyAvg, that I can use?

MyAvg is supposed to be a named range based on a single cell that means a single value. It can be replaced by any single cell named range.

RWayneH

ASKER

Ok, so what happens when I chg the filter and apply the condition again? As was evaluating and as I review the process that is going to be used, doing this to the whole column is not going to work.... we do not want to chg any existing colors that have been applied previously. I really would like to use the vba scenario to compare ActiveCell to MyAvg so it leaves those cells alone that were already colored from a previous running of the file. Can we do that? I need some help writing that vba to do this.

RWayneH

ASKER

So are you bailing on this question? My question states that I would like to look at both options... Conditional Formating (which is now not going to work) and the vba option to compare two values (MyAvg and the ActiveCell) and color the cell based on the result. I need a hand with the vba that would compare the two cells, but I have not heard anything for a couple days? Please advise and thanks.

Subodh Tiwari (Neeraj)

Sorry! Normally I am busy in my projects so don't get much time to come here and help for free.
I answered your original question considering your original description but don't have time to sort out what if kind of things.
If you need a devoted solver, I would suggest either you should open a Gig project or hire someone on Live.

RWayneH

ASKER

Thanks, appreciate the help.

Subodh Tiwari (Neeraj)

You're welcome!