Data validation list disappears after save if referencing another tab

**vikefan82** · 10-26-2012, 06:03 PM

Now I've run into two separate and strange bugs in the same day regarding data validation. While working in Excel 2010, the problem I'm currently having is that my data validation list is erased after saving the file, closing, and reopening it IF the data validation list is referencing cells in another worksheet.

Here's my setup:

Cell B3 on Worksheet "A" is the cell with the list. The worksheet I'm trying to reference is Worksheet B. While highlighting B3 on "A," I click on the Data ribbon, then select Data Validation. I select "List" for the Allow category, and the source data is selected as =B!$R$1:$R$2 . I also have Ignore Blank and In-cell dropdown checked. The worksheet works well and good up to this point. However, now once I save the file as an .xls and reopen the file, my validation list for B3 is completely gone. If I select the cell and hit "Data Validation" again, everything in the popup is back to default (i.e. "Allow" is set to the default "Any value").

I've tried redoing my steps several times to ensure I was selecting everything properly, and I've also verified that this whole process works just fine as long as my data validation is referencing cells within the same "A" worksheet. Any ideas on what this might be?

**djapigo** · 10-26-2012, 06:23 PM

Hi vikefan82,

Try saving it as *.xlsx

I wonder if has something to do with compatibility issues with older versions.

**vikefan82** · 10-26-2012, 06:33 PM

Ah yep that's what it was. Guess the earlier versions of excel don't support that particular behavior...I think I'll just enter the values I want on the main sheet and then hide the rows I suppose.

Thanks again Dj.

**TMS** · 10-26-2012, 06:38 PM

I suspect the problem with the Data Validation is the saving to a .xls file.

Historically, you could not refer to a range on a sheet other than where the DV box was located. This was introduced in Excel 2010. The workaround was to create a named range and then you could happily refer to that in your DV ... as "=DVnamedRange", for example (no quotes).

I guess that what is happening is that you set up the DV in the Excel 2010 framework and then save the file as an Excel 2003 .xls compatible file ... and that's when the compatibility check says, "no way". When you subsequently open the file, it's gone.

So, set up the list as a named range and use that; that will make it backwards compatible. Personally, I'd do that anyway because it's easier to set up a dynamic named range and refer to that.

Anyway, that only sounds like one problem.

That help?

Regards, TMS