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
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
Hi Dexter, I just wanted to let you know that this is what I've done and it works perfectly, and a lot easier than anything I was trying to do before! Thank you very much for your help it is definitely appreciated!
Ella