Forum Discussion
Solved
Making a list in another workbook
Hi all,
I'm hoping you can help me. 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. I also need to be able to use the drop down list in workbook A while workbook B is closed. 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.
Can anyone help with how to have the drop down work in book A while book B isn't open? Sorry I hope this makes sense thank you in advance!
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
Ella_Carter_Account Ella, I'm sure you have good reason for requiring the source list to be in a different workbook, and while it is perhaps feasible, it will be prone to issues and errors resulting from a number of common business practices. Would you mind elaborating on why it must reside in a separate workbook? Perhaps there's another way to handle (e.g. hidden sheets, protected ranges, etc.).
Dexter
Hello Dexter, thank you for your reply - It's because we need to send it to clients so it can't look messy and from what I can tell the admin before me might have had a different workbook but they left and the workbook was deleted. I wonder if the hidden sheets or protected ranges would work to conceal the table for the drop down to keep it clean looking for clients and would maybe be a bit easier on my side? I am still getting to grips with Excel and I'm trying to learn as much as I can so I really appreciate your help! 🙂
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.
