Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: INDIRECT reference to named cell in closed workbook

  1. #1
    New Member
    Join Date
    Oct 2003
    Location
    NY
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    14,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Shortening external references

    Last edited by smozgur; Dec 30th, 2008 at 05: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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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