Fred set up a workbook in Excel 2010 that uses off-sheet Data Validation. Everything works fine in Windows, but the dropdown arrow is not appearing in Excel:mac and presumably not on Excel 97 either. While Excel now allows for Validation to point to another sheet, it never used to do that. Here is the workaround to let your validation dropdowns work on a mac.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode: 1807, Off Sheet Data Validation Backwards compatibility.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's questions sent in by Fred.
Fred has a really cool spreadsheet.
And when I looked at that spreadsheet, Fred it has one date about a validation cell right here somewhere.
And he allowed a list and I was surprised to see that he just clicked here in the source and selected Sheet 2 and this range.
All right! Never...It used to be that you could not point to another sheet, using Data Validation but hey, it's working right. It's really cool.
And all right! Good, great!
So, they change that somewhere along the line.
I don't know where.
Here we are in Excel 2010.
Clearly it's working here.
Ah, but then Fred then shared that with his friends and co-workers.
And he reported back the people on a Mac are not seeing the drop-down.
I'm also betting the people in Excel 2003, Excel 2002 and Excel 97 aren't seeing the drop-down either.
Because back then you weren't allowed to have Validation pointing to another sheet.
Ah! But there was a workaround.
Here's what you have to do.
Now, the first thing I did is selected this validation, I did [ ALT+E A A ] to completely clear that cell.
Then we're gonna come back here to where the list lives.
We're gonna select the list and name this list.
One word here, I don't care product list, broad list but no space.
All right! I'm just gonna call it Plist, like that P list, so now that is a named range.
Now that that named range exists and it's a global name range.
In other words, it's work a...It has a workbook scope.
Every single sheet thinks that P list lives on that sheet.
So, we're allowed to come back here now and do [ ALT+D L ] for data validation.
We're going to allow a list.
And the source is going to be equal Plist.
Click OK.
Now, here we are in Excel 2010.
That's gonna work just like it worked in when we pointed to the sheet without using a name range The difference is gonna be when Fred shares this with his friends who have Macs or Excel 97.
Then the data validation will continue to work because we're using a named range instead of pointing to a different sheet.
Now, hey, it's really cool if the folks out at Microsoft found that this was a problem and fixed it somewhere along the way and provided you and all your friends who are on Excel 2010 or newer on a Windows PC.
Hey, it's great!
But knowing this this cool little backwards compatibility trick, in case you're sharing your workbooks with someone who is using Excel on a Mac or Excel 97, there is a good thing to know.
Well, I think Fred for sending that question in I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode: 1807, Off Sheet Data Validation Backwards compatibility.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's questions sent in by Fred.
Fred has a really cool spreadsheet.
And when I looked at that spreadsheet, Fred it has one date about a validation cell right here somewhere.
And he allowed a list and I was surprised to see that he just clicked here in the source and selected Sheet 2 and this range.
All right! Never...It used to be that you could not point to another sheet, using Data Validation but hey, it's working right. It's really cool.
And all right! Good, great!
So, they change that somewhere along the line.
I don't know where.
Here we are in Excel 2010.
Clearly it's working here.
Ah, but then Fred then shared that with his friends and co-workers.
And he reported back the people on a Mac are not seeing the drop-down.
I'm also betting the people in Excel 2003, Excel 2002 and Excel 97 aren't seeing the drop-down either.
Because back then you weren't allowed to have Validation pointing to another sheet.
Ah! But there was a workaround.
Here's what you have to do.
Now, the first thing I did is selected this validation, I did [ ALT+E A A ] to completely clear that cell.
Then we're gonna come back here to where the list lives.
We're gonna select the list and name this list.
One word here, I don't care product list, broad list but no space.
All right! I'm just gonna call it Plist, like that P list, so now that is a named range.
Now that that named range exists and it's a global name range.
In other words, it's work a...It has a workbook scope.
Every single sheet thinks that P list lives on that sheet.
So, we're allowed to come back here now and do [ ALT+D L ] for data validation.
We're going to allow a list.
And the source is going to be equal Plist.
Click OK.
Now, here we are in Excel 2010.
That's gonna work just like it worked in when we pointed to the sheet without using a name range The difference is gonna be when Fred shares this with his friends who have Macs or Excel 97.
Then the data validation will continue to work because we're using a named range instead of pointing to a different sheet.
Now, hey, it's really cool if the folks out at Microsoft found that this was a problem and fixed it somewhere along the way and provided you and all your friends who are on Excel 2010 or newer on a Windows PC.
Hey, it's great!
But knowing this this cool little backwards compatibility trick, in case you're sharing your workbooks with someone who is using Excel on a Mac or Excel 97, there is a good thing to know.
Well, I think Fred for sending that question in I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
