I have found that when doing DV on one sheet and the range of data is on another sheet, it always works better to assign a range name to the data and then in the DV set up, you can then choose the range, ie. =MyRange.
I have found that when doing DV on one sheet and the range of data is on another sheet, it always works better to assign a range name to the data and then in the DV set up, you can then choose the range, ie. =MyRange.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
In the past, you had no choice but I agree that it is sensible and practical approach ... particularly if you define and refer to tables.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Starting in Excel 2010, you can directly reference other sheets as the source for the drop down list.
In addition to using a named range, you can also use the INDIRECT function:
=INDIRECT("Sheet2!A2:A10")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
In Excel 2007 you can use, from Sheet4, say,Formula:Please Login or Register to view this content.
which has the same effect as:Formula:Please Login or Register to view this content.
If you define the DV List as a Table, you can use:Formula:Please Login or Register to view this content.
Note that the first two options offer static Data Validation Lists, whereas the last is dynamic because it is Table based.
See the example workbook.
Regards, TMS
@Alan: thanks for the feedback.
I perhaps shouldn't say this, but it's only fair, I picked up some examples here: How to use a table name in data validation lists and conditional formatting formulas
And this: A Dynamic Dependent Drop Down List in Excel
Regards, TMS
Hmmm...
Yes, those work but I could swear that I've tried that (method) before and it would not accept that syntax. Had to use either a named range or the INDIRECT function.
However, all other DV options will reject direct references to other sheets.
So, all these years I've been misinformed!
@Tony: you live and learn
I think Microsoft as well: http://support.microsoft.com/kb/211548
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
Not the best written piece of information I have seen
For information, the sample workbook that I uploaded was created and tested in Excel 2007. Complete with direct and indirect "external" worksheet references (within the same workbook).
AFAIAA, you have always been able to overcome this limitation by specifying a Named Range.
King James Version text (John 20:24-29) is:
24 But Thomas, one of the twelve, called Didymus, was not with them when Jesus came.
25 The other disciples therefore said unto him, We have seen the LORD. But he said unto them, Except I shall see in his hands the print of the nails, and put my finger into the print of the nails, and thrust my hand into his side, I will not believe.
26 And after eight days again his disciples were within, and Thomas with them: then came Jesus, the doors being shut, and stood in the midst, and said, Peace be unto you.
27 Then saith He to Thomas, Reach hither thy finger, and behold my hands; and reach hither thy hand, and thrust it into my side: and be not faithless, but believing.
28 And Thomas answered and said unto him, My LORD and my God.
29 Jesus saith unto him, Thomas, because thou hast seen Me, thou hast believed: blessed are they that have not seen, and yet have believed.
As I said, the workbook uploaded was created and tested in Excel 2007. No doubt you can download it and prove for yourself that it can be done ... in Excel 2007.
In reality, a Dynamic Named Range was, and is, a better way of setting up a Data Validation List. Just demonstrating the fact that it CAN be done using references to "external" sheets, both directly and indirectly.
Regards, TMS
I do not doubt what you have said, I only wonder if this has been changed with a service pack or update from the original 2007 behaviour, where I have seen it fail.
Yeah, I saw that page.
The scenario described on that page is not exactly the same. On that page they describe using the Data Validation>Custom option which will not allow direct references to other sheets in Excel 2007. That is still true. That also applies to all of the other selectable options EXCEPT for the List option.
Apparently you CAN use references to other sheets as the source for the Data Validation>List option. Although, I have to say, ever since Excel 2007 came out I was under the impression that you could not even do that. So, as I noted in my other post, I've been misinformed about that since Excel 2007 was released.
My apologies. It was not directed at you personally ... but I just couldn't resist. My wife often tells me that I should try to resist, often and specifically when I say things out loud that I really should have just kept in my head.
But I would be grateful if people do test this ... I'm beginning to doubt it myself.
Regards, TMS
I guess I should make the point that you actually have to type the external sheet reference, for example,.Formula:Please Login or Register to view this content.
Whereas, you can navigate to the range on the same sheet ... perhaps that's been the show stopper?
Regards, TMS
Yes, if it's on the same sheet then you can point to the source.
With the DV userform open to the List option, in the Soruce refedit, when I type the equal sign then try to point (navigate) to the source on another sheet nothing happens. Then I have to manually type the source address (or named range) and that works.
So it would seem that the limitation is purely in the fact that you can't use cell selection, that is, navigate to the cells you want to use as the list. You have to manually type the reference in.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks