Data validation with IF statement, no list

[mariposa]

Hello,
I need a way, either via VBA or Data Validation or combination of both to determine the cell value based on another cell's value.

So in column "F" I have a data validation list of just 2 options "Salary" or "Hourly", easy.
Based on what is selected by the user, column "H" need to be either an hourly rate or annual rate.
Meaning if "F" equals "Salary" the value in "H" should be greater than $xxx.xx or $x,xxx.xx, or greater than an obvious hourly figure, like $99 (I don't care what the value is as long as user gets something to say "must enter annual salary" if they enter $20-something aka the hourly equivalent).
And same applies if "F" equals "Hourly", then the value in "H" should be $xx.xx or less, so if they enter the annual equivalent, something tells them to enter the hourly rate.


Thank you in advance!

 

[Eric W]

Try this:

Select column H (the whole column).
Click Data Validation.
Choose Custom and enter this formula:
=(F1="Hourly")*(H1<99)+(F1="Salary")*(H1>99)

Click on the Error Alert tab and enter something like:
Please enter an amount appropriate to the type of pay in F.

Hope this helps.

 

[mariposa]

Thank you Eric!

Works great, but now have another dilemma...


This is in a table, with a macro that allows the user to click a button to insert a new person (the sheet is protected aside from certain columns they fill in).
All the other data validations copy down when new line is inserted expect for this one.
I currently have this entered: =(F9:F108="Hourly")*(H9:H108<99)+(F9:F108="Salary")*(H9:H108>10000)
Even though this says to row 108 currently the row is only up to 35.
I tried the following but it didn't work: =(F:F="Hourly")*(H:H<99)+(F:F="Salary")*(H:H>10000)

 

[Eric W]

Don't use ranges in the formula. No F9:F108 or H9:H108. Just select the entire column and use the formula as I originally posted it. When you enter the Data Validation that way, the formula as entered applies to the top cell in the range. But internally, Excel modifies the formula to each cell in the range as if you dragged it down the range.

So
=(F1="Hourly")*(H1<99)+(F1="Salary")*(H1>99) applies to row 1.
=(F2="Hourly")*(H2<99)+(F2="Salary")*(H2>99) will apply to row 2, and so on.

And if you do it that way, the Data Validation will apply to any new rows you add as well.

 

[mariposa]

Hello Eric,
Unfortunately it will not allow me to select entire column since there are merged cells within the column above where the table starts.
Any work around? Table headers are in row 8 and table content starts row 9.

Thanks

 

[mariposa]

Never mind, got it to work by entering the validation as: =(F9="Hourly")*(H9<99)+(F9="Salary")*(H9>10000)
in the first cell of table, dragging down table so that as you mention, 9 becomes 10 and etc.
This allowed new row to continue counting within table when the row is entered.

Thank you again!

 

[Eric W]

You're welcome!