Formulas and Data Validation

[DAD]

Hi All,

I am trying to implement a change to a spreadsheet, but I am not even sure if the change I hope for is possible. I am hoping someone here may have a solution. I will preface this by saying unfortunately I cannot use VBA to solve the problem as the spreadsheet will be deployed on an iPad.

If I have a value in one cell, I would like the value in a second cell to be either a fixed value, or a drop down list. For example, If the value in cell A1 is "YES", then the value in A2 will be "Summer" (this is the inserted value, not part of a drop down list). If however the Value in A1 is "NO", the cell A2 is a drop down list of "Winter", "Autumn", "Spring", and the user can select either of the three.

As I said, I don't think this is possible without VBA, but I would be most grateful if anyone could correct me on that.

 

[Kamolga]

Indeed my solution would be VBA and I never been across a formula to change data validation. Because of the Ipad, I would go for a compromise: a drop down showing summer when yes and

"Winter", "Autumn", "Spring" when no.

To do so is pretty easy. Write down 4 seasons for your list, click on summer, formula tab, define name and call it "yes". Select the 3 other seasons and call it "no".

Now, in the cell where you want your dropdown, put the list in datavalidation and put

=indirect($A$2)

in source. Again, not as good as VBA.
​

 

[DAD]

Hi Kamolga,

Thanks for taking the time to reply. I had also come up with your suggested solution, but unfortunately it does not add any efficiency to the spreadsheet in its current form.

Thanks anyway.