Results 1 to 15 of 16
Thread: Concatenating Data (2007)

20080428, 20:11 #1
 Join Date
 Feb 2008
 Posts
 1,516
 Thanks
 137
 Thanked 8 Times in 8 Posts
Concatenating Data (2007)
I have a formula in column E that where have concatenated the data in column D, but cannot get the first part .i.e B551 to concatenate to column B and the row number that the formula is in , in this instance the row number is B551. The formula shows as text and does not show a value
="B551[M_ECMUIT.xls]Sheet1!N"&D551&""
It would be appreciated if you could assist
Howard

20080428, 21:02 #2
 Join Date
 Aug 2001
 Location
 Boston, Massachusetts, USA
 Posts
 167
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Concatenating Data (2007)
Can you attach an example because the explanation is confusing without looking at the actual data?
thanks
christine

20080428, 21:36 #3
 Join Date
 Feb 2008
 Posts
 1,516
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Concatenating Data (2007)
Hi Christine
Thanks for the reply. Attached please find sample data as requested
Regards
Howard

20080428, 21:44 #4
 Join Date
 Aug 2001
 Location
 Boston, Massachusetts, USA
 Posts
 167
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Concatenating Data (2007)
Still not really sure what you're trying to accomplish cause the explanation is not clear, but it sounded like you needed a simple concat formula.
[b] [C] [D] RESULTS FORMULA
43,180 BR1 1573 43179.611573 =B3&D3
0 BR1 1574 01574 =CONCATENATE(B4,D4)thanks
christine

20080428, 21:45 #5
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Concatenating Data (2007)
I'm assuming you want the data value in cell B551 in front of the resulting formula. If that's the case you need to move the B551 from between the " "'s
= B551 & "[M_ECMUIT.xls]Sheet1!N"&D551&""

20080428, 21:49 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Concatenating Data (2007)
Do you mean this?
<code>
=B3&""&INDIRECT("'G:WorkfileZAccnts[BR1.xls]Sheet1'!N"&D3)</code>

20080429, 05:22 #7
 Join Date
 Feb 2008
 Posts
 1,516
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Concatenating Data (2007)
Hi Hans
Thanks for the help. Your formula works well. I just need once small change. If the value in the source workbook in this instance is blank, it comes up #Ref. Kindly amend formula, that where there is a blank value in the source workbook i.e G:WorkfileZAccnts[BR1.xls]Sheet1, then the formula must return a zero
Regards
Howard

20080429, 05:39 #8
 Join Date
 Feb 2008
 Posts
 1,516
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Concatenating Data (2007)
Hi Hans
Forgot to ask you when you amend your formula, to formate as comma with zero decimal places for eg if positive 212,500 if negative 212500
thanks
Howard

20080429, 07:58 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Concatenating Data (2007)
Does this do what you want? I obviously can't test it.
=B3&""&IF(D3="","",TEXT(INDIRECT("'G:WorkfileZAccnts[BR1.xls]Sheet1'!N"&D3),"#,##0"))

20080429, 20:16 #10
 Join Date
 Feb 2008
 Posts
 1,516
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Concatenating Data (2007)
Hi Hans
Thanks for the help. the formula works for some values but not all, Please check my formulas & Correct
I have attached a sample file & will be posting a seperate file containing the source workbook
Your assistance will be most appreciated
Regards
Howard

20080429, 20:22 #11
 Join Date
 Feb 2008
 Posts
 1,516
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Concatenating Data (2007)
Hi Hans
Please find Source Data. Sent you destination workbook containing formulas to correct
Regards
Howard

20080429, 21:47 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Concatenating Data (2007)
I thought you wanted to concatenate values, but it appears that you want to perform calculations, so I don't understand your original question any more. Could you try to explain more clearly what you want?

20080430, 05:55 #13
 Join Date
 Feb 2008
 Posts
 1,516
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Concatenating Data (2007)
Hi Hans
I only need you to look at the formula in column E. I need the value in column N in the workbook "BR1" based in the row number in column D in the sample workbook. for eg if the row number in D1 is D48 , then I need the value of N48 in BR1. Where the value in say N48 is blank, then a zero must be shown
Regards
Howard

20080430, 10:31 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Concatenating Data (2007)
But your formula in column E makes no sense if you want to use it for a calculation in column F. Moreover, the formulas are inconsistent. That's why I need why you want to concatenate and what you want to accomplish.

20080430, 12:06 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Concatenating Data (2007)
Are you looking for something like:
=IF(B1="","",INDIRECT("'[br1.xls]Sheet1'!N"&D1))
I agree with Hans and don't see where concantenation enters into the formula based on this description or even why you want to convert the value as text...
Steve