Data Validation List with IF Function

Question

Hello,

I have a set of data that requires people to add comments but only if it is greater than 100K or less than 100K and respond yes when they have completed the comment. I want a dropdown with: 1) the cell to say "Not Needed" if it doesn't meet that threshold or No if it does 2) the person to changes it to Yes when the comment is done. I want the 1) to be completely automated. I need 2) because there will always be a comment there but it may be from the last update and I need to know if they adjusted the comment based on the current update.

I've tried looking up various solutions but none seemed to be what I need (mostly nested dropdowns). PLEASE NOTE: I am unfortunately using Excel 2013. I have tried putting the IF Function directly in the data validation source data but it just gives me the formula as one of the drop down options and doesn't actually change based on the IF formula. e.g.:

Can you think of anything other than having a helper col (including basic macros)? I'd like to not have to update the data validation each time with the updated helper col info unless its a simple button click. Also, I've created a macro to reset the list to No when I click a button so I don't know how that will impact this, but I can figure that out later.

Here is a sample of the data:

Program	Amount	Comment	Analyst Confirmation
Oranges	50,000		This is where I want the dropdown; this should say "Not Needed" based on col B
Apples	125,000	20 boxes delayed 1 day	Should automatically change to No since its over 100K and the analyst hasnt added their comment and changed it to Yes
---	---	---	---
Bananas	150,000	10 boxes on-time	Yes (changed by analyst)

Thanks!

Answer 1

Hi Lauren. I am an Excel user like you.

There are two things that you are trying to do. The first is to have No or Not Needed appear in the cell and the second is to have an available dropdown for the analyst to change it. These two will have to be done in separate operations.

It may be possible to set this up with sheet code, but I will address it on a formula level. I used Row 2 since that is the first row of you sample data. In Row 2 of your column Analyst Confirmation, enter this formula and drag down:

=IF(A2>100000,"No","Not Needed")

Modify this according to your actual data. In your sample the Amount is actually in Column B it appears. This will fill in the initial information based on the Amount in the chart.

For the Data Validation, enter the three options in the Data Validation window as below.

The Analyst will be able to override the formula and enter Yes when they have entered their comment. The drawback, that sheet code might be able to correct, is that when the Analyst enters Yes in the cell the formula will be overwritten and deleted. You currently have the macro to reset the sheet to No, so you should be able to revise that macro to put the formulas back in place to reset the form.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

You might not even need the macro to reset the sheet--just drag the formula into the cells that have been edited.

Rich~M

Answer 3

Thanks for your response. I was really hoping for a one click solution with no possibility for error (I dont trust the analysts to pay attention to what they are doing) but your answer helped me get inspired to try different options. I thought maybe I could have the formula in the cell then put the drop down list but when I picked yes from the drop down, I couldn't recover my formula.

I ended up resolving this question myself using macros. I found that when you use the macro in a cell with a drop down, you dont get an error that the cell cant be changed so it allowed me to do what I needed. In case anyone else needs it, here is the macro; you can copy it right into your Module (Alt + F11 then go to Insert > Module). For brand new users to coding, anything with a ' at the beginning below is an explanation, not coding BUT you can put that in the Module also since it wont be read when you run it (just like if you put a ' in front in a cell). Also note that this has a loop, which means it changes for every cell in the col.

--

Sub CatVartab()

'Sub starts the macro and "CatVartab()" is the name I gave it; you must always end the name with ()

Dim ws As Worksheet

'Designates "ws" as a short form to the word "worksheet" so you don't have to type out the entire thing each time

Dim lastRow As Long

Dim currentRow As Long

Set ws = ThisWorkbook.Worksheets("Sheet 1") 'Change "Sheet1" to the name of your worksheet

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Finds the last row with data in column A so it can be applied to all rows

For currentRow = 2 To lastRow 'This assumes you have a header and want to start in row 2; you can change that as needed

If ws.Cells(currentRow, "E").Value > 100000 Then 'This starts the if statement: If the amount in Sheet 1, starting in the current row (which you assigned above as row 2) and in column E have a value greater than 100,000 then (the then formula MUST go on the next row to work). Because of the loop, this coding will then be applied to each row (but in the same Col assigned) so no need to repeat anything.

    ws.Cells(currentRow, "Q").Value = "No" 'in Sheet 1, starting in the current row (again, assigned as 2 per above) put "No" in col Q

Else 

    ws.Cells(currentRow, "Q").Value = "Not Needed" 'if the value is < than 100,000, it will put "Not Needed" instead of No

End If 

Next currentRow

End Sub

--

Hope this helps!