Dependent lists/Indirect and multiple workbooks

Hi all - first post here and any help is appreciated.

I have 4 separate forms (workbooks) set up that have multiple dependent dropdown selections using data validation and the INDIRECT function and also using VLOOKUP to access other data based on the selections.

In each of the 4 forms, I have the named lists/table on a hidden separate worksheet in the same workbook. Now, the department using these wants to be able to just have one master workbook to update rather than having to do the hidden sheet on each of the 4 forms.

I've discovered that INDIRECT apparently cannot reference a separate workbook unless it is open. Ultimately, these 4 forms (and the source workbook) will be stored on a shared drive for multiple managers to access and use (the source will be psswd protected). I'm trying to figure a better way to either set up my table so I can use VLOOKUP only or to get around using INDIRECT for my data validation for the dependent dropboxes.

I am sure I am missing something very simple, but I'm pulling my hair out here. I thought I had this all wrapped up (and I'm impressed I got this far!!) I'm attaching a fictional example of the potential source data workbook. On the 4 forms themselves (separate workbooks), the first cell would have a dropbox with the DEPARTMENTS (from data validation), the 2nd cell should have only those positions corresponding to the DEPT selected (currently done with INDIRECT using named ranges), and the other cells should auto-fill based on the selected position (currently done with VLOOKUP).

Like I said, I've got it working perfectly now, but I can't figure the step of moving the source data to a separate workbook. THANKS SO MUCH for any advice.

longer (dozens of positions per department and many more columns of date per position). Not sure if that matters, but that's why I originally used INDIRECT for the dependent dropboxes.

Thanks again for any advice!

I'm not sure that I completely understand your what you are trying to do,
However, one thing that might help is HYPERLINK.
This will allow you to set up a link in one workbook that will cause another workbook to be opened which in turn will allow all your INDIRECT formulas to find their targets.

Mark.

First, what I am trying to do is move my source data from where it currently is - 4 separate sheets in 4 separate workbooks (forms) - to one sheet in a
5th workbook to allow the HR dept. to only have to update in one place (on the new 5th workbook) and have the 4 separate form workbooks be updated as well. Does that make sense? Everything is working just fine as is, but I can't figure out the moving of the source to a separate workbook.

I have not used HYPERLINK before. All 5 workbooks will eventually be stored in one folder on a shared drive. The 5th source workbook will be password protected so no one outside HR can open/modify it. Will this affect using HYPERLINK?

Thanks!

What I might do is have hyperlinks to the 4 source worksheets placed in 4 convienient cells in the HR workbook.
The HR person can just click on each of these cells in turn to open the 4 source workbooks, the HR workbook will then update itself.
The source workbooks can perhaps be locked (and password protected) to prevent inappropriate alterations...
Note that the default text displayed for the hyperlink is the address of the workbook to be linked, but this can be edited to display any text you like.
Mark.