Validation list using multiple workbooks (2000)

Topic

New Reply

I am using the validation option for a list located in another workbook (called ratings). There is a primary list and an indirect list used in another workbook based on the list located in the ratings workbook. The validation list works fine when the ratings workbook is open, however, it does not work when the workbook is closed. Is there a way the validation option can still operate even when its using a list from a closed workbook. If not, what would be the best way to go about pulling a list from a workbook that does not have to be open. I am also defining some names off that workbook (thus using the primary and indirect lists on the current workbook). Might that also have anything to do with not being able to pull the list off of the closed workbook. Thanks and look forward to the responce.

Reply | Quote

Replies

I would think the easiest way is to use a direct link (rather than indirect) into the file and then base your validation on this “extracted info”:
something like:
=INDEX(‘D:filename.xls’!Areas,ROW())

will work with closed files. Then name this extracted range in the current workbook and use that for validation

Steve

Reply | Quote

What I meant by indirect is the command that I typed into the validation list source box to link a group of cells to contain the sublists of another group of cells that contains the primary list. So in the validation source box for the “primary list” cells I typed ( =[L:filename.xls]worksheet!,range) starting at cell D11. Then I validated another set of cells with INDIRECT(D11) typed into the source box. How exactly does that INDEX formula work? Is this the command I should put into the validation source box of the “primary” cells (instead of =[L:filename.xls]worksheet!,range). What goes in the ROW() part of the formula?

Reply | Quote

1) INDIRECT function only works with workbooks that are OPEN. If the workbook referenced is closed, it gives an error
2) I used row() to get the row number. I assumed (maybe this was a bad assumption) that you would put this new table in a place starting with row 1 and going down
INDEX ( with only 1 parameter) gives the xth item in the list. So in row 1 it would give the 1st item, in row 2 the 2nd item in the list.

You essentially create the table DIRECTLY in your workbook, that is LINKED to the other one (closed workbook)

You would put in your validation list the name you give the LINKED table.
Steve

Reply | Quote