Forum Discussion
Making a list in another workbook
Ella_Carter_Account Thank you for expanding, Ella, & sorry for the delayed response. If sharing a workbook with your client, they would need to access both workbooks in order for it to work correctly. Emailing the file wouldn't work, etc.
But you can certainly present a tidy workbook despite having the source list for the drop down residing within the same workbook. Below you can see the drop down continues working after the sheet holding source is hidden and the workbook protected. The second step isn't necessary, but will prohibit someone from unhiding a hidden sheet. You might have noticed that you are able to password protect the workbook to stop someone from un-protecting it.
This is a much simpler and less error-prone way to accomplish your goal, barring no other considerations exist which haven't been mentioned, of course.
Hope this helps,
Dexter
I have to make a drop down list in Excel (which i can do), but the list is too long to just type it in. I can't have the list written in a table in this workbook (Workbook A) and make the drop down in the desired cell, so the source list needs to be in a different workbook
Are you at liberty to explain the reason further, exactly WHY you can't have that list in the same workbook as the dropdown? After all, if the user can see the dropdown, then the contents of the list can't be secret. So what's the issue? Can it not just be on a separate sheet in the same workbook?
I have managed to figure out how to get the list from B to A but can only use the drop down while B is open.
I'm assuming you have gotten it to work by means of the INDIRECT function, which, yes, will work only when the other sheet is open. I understand from some other threads in this forum that there are ways that VBA or macros can get around that necessity to have the other sheet open, but I'm not a user of VBA or macros, so if that is indeed a solution, it'll have to wait for others to offer it.