1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Q: draw value from a 2nd workbook, based on a variable cell ref in 1st wb

Discussion in 'Business Applications' started by toboyinafg, Dec 14, 2004.

Thread Status:
Not open for further replies.
  1. toboyinafg

    toboyinafg Thread Starter

    Joined:
    Nov 23, 2004
    Messages:
    7
    i have 2 workbooks entitled [price] and [fruit] respectively

    [price], sheet1!, cell A1 is a variable which can contains a cell address, eg $D$1

    [fruit], sheet1!, contains information in cells as follows:
    D1 = apples
    D2 = bananas
    D3 = cherries

    in the [price] workbook, i want to have a formula in say, cell B1, which would use the cell reference in A1 to find the value in the [fruit] workbook.

    so, if [price]sheet1!A1 = $D$1

    then [price]sheet1!B1 = apples

    note that the result "apples" was pulled from another workbook, [fruit]sheet1!$D$1.

    if [price]sheet1!A1 = $D$2,
    then B2 = bananas

    and so on...


    in the [price] workbook,

    As A1 is a variable, i do not want to physically retype the value within A1, but rather draw reference to it inside of a formula. i hope this is clear as mud.

    Is this possible?

    Thanks,
    markus
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    In Sheet1!B1 (of Price), use

    =INDIRECT("[Fruit.xls]Sheet1!"&A1)

    , where A1 contains the cell reference -- $D$1, $D$2, $D$whatever.

    As explained yesterday, this (the INDIRECT function) will only work when both files are open ; to get it to work when the source file if closed, you need to download Laurent's add-in. Please do not start threads over.

    Rgds,
    Andy
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/307604

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice