Conditional data types

[excelhumblelearner]

Hi,

I'm currently stuck on a particular Excel problem, which essentially boils down to the following case: creating an Excel worksheet with the following features:
- In cell A1, a drop-down list comprising of say "UK" and "USA"
- In cell A2, a drop-down list "YES" and "NO" if A1 = UK and a blank cell (where a user could input a number) if A1 = USA

I've looked into some Excel functions to address/circumvent the problem, such as IF, INDEX, MATCH but all of them work with the value datatype.
I've also learnt how to create dependent drop-down lists based on other drop-down lists.

However, from what I've tried/understood both these approaches fail to create cells that support conditional data types i.e where the data type of A2 varies according to the selection made in A1.

I'd be glad to learn if there's a way to achieve that in Excel, with a preference for "basic" tools over more complex ones (i.e use of Excel functions vs VBA).

Thank you to everyone taking the time to read/answer my post!

[¯\_(ツ)_/¯]

Without using VBA, I don't think there's a way to do what you're asking.

An option would be to use Data Validation like so:

Select A1
On Data tab, click Data Validation > Data Validation...
Change 'Validation criteria' to List
Beneath 'Source', type: UK,USA
Leave 'Ignore blank' and 'In-cell dropdown' checked
Click OK

Select A2
On Data tab, click Data Validation > Data Validation...
Change 'Validation criteria' to List
Beneath 'Source', type: Yes,No
Leave 'Ignore blank' and 'In-cell dropdown' checked
On 'Error Alert' tab, un-check box next to 'Show error alert after invalid data is entered'
Click OK

You can then select UK or USA in cell A1, then Yes or No in A2 - or enter any value you want into A2 since you turned off the invalid data warning.

The drawback is that A2 will always have a drop-down showing Yes or No, even if you pick USA. It can be left blank, of course.

[excelhumblelearner]

Thank you!

I didn't know about the Error Alert tab.
Guess I'll have to learn VBA then.
I'll leave the thread open a little longer see if anyone else has something just in case.

[¯\_(ツ)_/¯]

The following code, added to the worksheet module that has the data validation list in cell A1 (containing UK and USA), should allow you to control what appears in A2. In A1, if you select USA or leave it blank (or delete the current value) it clears cell A2 and would allow you to type anything. Once you select UK from the drop-down in A1, data validation is then added to A2 limited to Yes or No.

Please Login or Register  to view this content.

[dflak]

Here is a little-two step using a named dynamic range.

I happened to set up my range as D1:D2.

In Formulas -> Name Manager I set up a name called "UK_List" with the Refers to: =IF(Sheet1!$A$1="UK",Sheet1!$D$1:$D$2,"")

and I use a list validation =UK_List in Cell A2.

Oddly enough it doesn't balk at all when you free form entry something in Cell A2.

[excelhumblelearner]

This is good but for A1= USA we still get a list, albeit an empty one.

Thanks for telling me about dynamic ranges though.

[excelhumblelearner]

This works just fine. Cheers!