Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Concatenating Data in Linked Formula (Excel 2007)

    I have the following formula below that links to data in another workbook

    =-'I:Workfile[M_ECMHO.xls]sheet1'!$R$1637-'I:Workfile[M_ECMHO.xls]sheet1'!$T$1637-_
    'I:Workfile[M_ECMHO.xls]sheet1'!$AD$1637

    I have the row number in column D that must be linked to the other workbook in the same row as the linked formula. for Eg if the formula above is in B14 and D14 contains 1629, then the formula must concatenate the data in D14

    Your assistance will be most appreciated

    Thanks

    Howard

  2. #2
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Concatenating Data in Linked Formula (Excel 20

    Hi Hans

    Thanks for the help. Formula comes up with #Ref

    I have checked the values in M_ECMHO and there are values in these rows

    Please check formula & advise

    Regards

    Howard

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating Data in Linked Formula (Excel 20

    Is M_ECMHO.xls open? INDIRECT only works with references to another workbook if that workbook is open in the same instance of Excel.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating Data in Linked Formula (Excel 2007)

    Does this do what you want?
    <code>
    =-INDIRECT("'I:Workfile[M_ECMHO.xls]sheet1'!$R$"&D14)
    -INDIRECT("'I:Workfile[M_ECMHO.xls]sheet1'!$T$"&D14)
    -INDIRECT("'I:Workfile[M_ECMHO.xls]sheet1'!$AD$"&D14)
    </code>
    I split the formula over three lines for readability, but it's one formula.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Concatenating Data in Linked Formula (Excel 20

    Hi Hans

    Thanks for the reply. I still get #Ref even though the workbook M_ECMHO is open.

    I have attached a sample workbook "CON Data" containing the indirect formula as well as the source workbook "DATA"

    It would be appreciated if you would test formula and advise

    Regards

    Howard
    Attached Files Attached Files

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Concatenating Data in Linked Formula (Excel 20

    Hi Hans

    Workbook "Data"ttachment

    Regards

    Howard
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating Data in Linked Formula (Excel 20

    One reason that it doesn't work might be that the file name in the formula is incorrect. The "data" workbook that you attached is named Data.xlsx (with the new Excel 2007 extension) but the formula refers to Data.xls (with the extension used by Excel 2003 and before). If I correct this, the formula returns the correct value. (I saved the data workbook as an Excel 97-2003 workbook Data.xls)
    Attached Images Attached Images
    • File Type: png x.png (3.0 KB, 0 views)

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Concatenating Data in Linked Formula (Excel 20

    Hi Hans

    Thanks for the help. works perfectly now.

    If have the file name in column C and I want to concatenate this, how do I incorporate this?

    I have tried to do this in the formula below, but with no success

    =indirect(VLOOKUP(A144,"'I:Workfile["&C144.xls]sheet1'!$L$"&d144:$P$&"d144,5)

    It would be appreciated if you would assist

    Regards

    Howard

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating Data in Linked Formula (Excel 20

    You have to keep in mind what you're using INDIRECT for. INDIRECT is used to refer to a range indirectly, not to a formula. So you can't apply INDIRECT to VLOOKUP, i.e. your formula makes no sense.
    Moreover, you didn't pay attention to what you were concatenating.

    The best way to create a formula with INDIRECT is to start with a normal formula.
    Then change a reference zzzz to INDIRECT("zzzz").
    Next, take apart the string "zzzz", taking care to test at each intermediate step. Don't try to do it all at once.

    =VLOOKUP(A144,INDIRECT("'I:Workfile["&C144&".xls]Sheet1'!$L$"&D144&":$P$"&D144),5)

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Concatenating Data in Linked Formula (Excel 20

    Hi Hans

    Thanks for all the help & explanations.

    Regards

    Howard

Posting Permissions

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