Results 1 to 6 of 6

INDIRECT reference to named cell in closed workbook

This is a discussion on INDIRECT reference to named cell in closed workbook within the Excel Questions forums, part of the Question Forums category; I have the following as the source for a data validation list: =INDIRECT(CONCATENATE("[Workbook1.xls]Sheet1!",A1)) where A1 will contain the name of ...

  1. #1
    New Member
    Join Date
    Oct 2003
    Location
    NY
    Posts
    29

    Default INDIRECT reference to named cell in closed workbook

    I have the following as the source for a data validation list:

    =INDIRECT(CONCATENATE("[Workbook1.xls]Sheet1!",A1))

    where A1 will contain the name of the range I want to use as the list. This works fine as long as Workbook1 is open.

    Is there a way to do this if Workbook1 is closed? I can't seem to get it to work even if I use the full path for the workbook.

    e.g. =INDIRECT(CONCATENATE("c:Documents\data\[PipePrice.xls]Pipe!",C12))

    Any advice would be appreciated

    Paul

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    the search feature of the board is remarkably effective

    I'd suggest searching for something like "indirect closed".

    (& while I'm here, the morefunc addin has an indirect.ext() function that works for closed workbooks).

  3. #3
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default

    This ref has some stuff on utilising Indirect and Closed workbooks:

    Shortening external references

    Last edited by smozgur; Dec 30th, 2008 at 06:02 PM. Reason: changed referred thread URL with the actual forum link
    "Don't Ruin an Apology with an Excuse"...

  4. #4
    New Member
    Join Date
    Oct 2003
    Location
    NY
    Posts
    29

    Default Re: INDIRECT reference to named cell in closed workbook

    Looks like the morefunc add-in is probably the way to go.

    Thanks to both of you. In the future I guess I should spend a little time looking for similiar posts before starting a new thread.

    Paul

  5. #5
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: INDIRECT reference to named cell in closed workbook

    Not always... it's amazing how some people can discover some new element that no-one else saw.

    But, sometimes people love re-inventing the wheel

    I guess thats what Undergrads are there for :o


    "Don't Ruin an Apology with an Excuse"...

  6. #6
    New Member
    Join Date
    Oct 2003
    Location
    NY
    Posts
    29

    Default Re: INDIRECT reference to named cell in closed workbook

    Quote Originally Posted by Paulfakins
    I have the following as the source for a data validation list:

    =INDIRECT(CONCATENATE("[Workbook1.xls]Sheet1!",A1))

    where A1 will contain the name of the range I want to use as the list. This works fine as long as Workbook1 is open.
    O.K. Here we go:

    I followed the advice I was given and loaded the Morefunc Add-In. I have used the INDIRECT command successfully to access information in a closed workbook, However;

    When I try to use it in Data Validation, I cannot get it to work.

    Here is the syntax I am using in the data Validation Source:

    =INDIRECT(CONCATENATE("'C:\temp\[book1.xls]Sheet1'!",,A1))

    When I enter this information I get a message box that reads:

    The Source currently evaluates to an error. Do you wish to proceed?

    Am I entering the formula incorrectly?



    Background:

    Book1 is a Price Book. It contains about 50 named ranges which consist of various categories of parts. Through a series of Data Validation Lists, the user steps through category selections and the final list (the one that uses the range from Book1) shows only the parts in the sub-category they have selected.

    I have had all of this working quite well as long as the Price Book(Book1) is open. I only hit this snag when I tried making this work with the workbook closed.


    Any advice would be appreciated. I would even be willing to consider moving the price data to Access, but I am not familiar with how to retrieve data from Access with XL.

    Thank you,

    Paul

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com