Results 1 to 10 of 10

20080503, 08:29 #1
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 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

20080503, 10:51 #2
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 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

20080503, 10:57 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20080503, 10:58 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20080504, 10:06 #5
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 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

20080504, 10:09 #6
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 Posts
Re: Concatenating Data in Linked Formula (Excel 20
Hi Hans
Workbook "Data"ttachment
Regards
Howard

20080504, 11:16 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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 972003 workbook Data.xls)

20080504, 11:53 #8
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 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

20080504, 12:05 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)

20080504, 13:00 #10
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 Posts
Re: Concatenating Data in Linked Formula (Excel 20
Hi Hans
Thanks for all the help & explanations.
Regards
Howard