Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation list using multiple workbooks (2000)

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Validation list using multiple workbooks (2000)

    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

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation list using multiple workbooks (2000)

    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?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Validation list using multiple workbooks (2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •