Data Validation Drop Downs

Rob0424

New Member
Joined
Jan 9, 2007
Messages
17
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Rob0424

New Member
Joined
Jan 9, 2007
Messages
17
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Are you using Formulae to create the lists?
 

Rob0424

New Member
Joined
Jan 9, 2007
Messages
17

ADVERTISEMENT

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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Is A4:A7 hard values or a formula?
 

Rob0424

New Member
Joined
Jan 9, 2007
Messages
17

ADVERTISEMENT

A4:A7 is Text
 

Rob0424

New Member
Joined
Jan 9, 2007
Messages
17
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for letting us know.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
846
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,572
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top