Data Validation Drop Downs

[Rob0424]

I am working in Excel 365 and have created some List Drop Downs using Data Validation. This spreadsheet is for someone who has an older version of Excel and when they open the file it removes all the Drop Downs. Is there a way to fix this?

 

[6StringJazzer]

older version of Excel

How old? Data validation with dropdown list was introduced in Excel 97.

 

[Rob0424]

They are using Excel 2003. When they open the file, it converts it to something. I also get an error in 365 when trying to save it down from an .xlsx to an .xls

 

[Fluff]

Are you using Formulae to create the lists?

 

[Rob0424]

I have the list data on a separate Worksheet and have this formula:

=Sheet2!$A$4:$A$7

 

[Fluff]

Is A4:A7 hard values or a formula?

 

[Rob0424]

A4:A7 is Text

 

[Rob0424]

I fixed the problem. For some reason it does not like the Data being in a separate worksheet. I moved it into the same worksheet and it is now working fine for the person

 

[Fluff]

Glad you sorted it & thanks for letting us know.

 

[6StringJazzer]

Yes, earlier versions of Excel do not allow references to other sheets in a data validation list expression. The workaround is to create a named range on the other sheet then use that in the data validation expression.