Excel data validation only for some rows

0
votes

I have, what seems to me, an easy question to answer, but I cannot find the answer online yet without using VBA.

I have two columns, A and B. Column A contains a fruit, column B a number. I want to apply data validation (i.e. a drop down list) for column B only in case the value of A is apple. So for other fruits, the user should just be able to enter any value.

Important: When I apply sorting to the complete table, validation should still work.

Hope the question is clear.

1
excelvalidationconditional-statements
What sort of output do you want? A change in fill to red if invalid, conditional formatting, is easy. - David G. Pickett
Are you talking about a list or conditional formatting - urdearboy
A drop - down list, so not conditional formatting. So if Apple in A, then I should only be able to enter a certain list. If banana in A, then any value. - Hans
Refer almost same question. For exception, refer same cell .. For example B2 list validation formula =IF(A2="Apple",List,IF(ISNUMBER(B2),B2)) - Naresh
Thank you all. I went for Naresh' solution. Urdearboy also thanks for your help. - Hans

1 Answers

0
votes

If you have access to UNIQUE and FILTER you can build your validation list by making use of a helper column. The set up will be something like:

  • Columns A:B contains your data
  • Cell D2 is the input (fruit: where A is Apple)
  • Cell E2 is the data validation list
  • Column F:F is your help column 'Values'
  • In F2 = IF($D$2="A",UNIQUE(FILTER(B2:B9,A2:A9="A")),$B$2:$B$9)

  • Data Validation Formula in E2 = INDIRECT("F2:F"&COUNTA(F:F),TRUE)

    enter image description here

You can hide your helper column if needed (or place it in the last column out of sight)