1. ## 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

2. ## Re: Concatenating Data (2007)

Can you attach an example because the explanation is confusing without looking at the actual data?

Hi Christine

Regards

Howard

4. ## 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)

5. ## 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&""

6. ## Re: Concatenating Data (2007)

Do you mean this?
<code>
=B3&"-"&INDIRECT("'G:WorkfileZ-Accnts[BR1.xls]Sheet1'!N"&D3)</code>

7. ## 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:WorkfileZ-Accnts[BR1.xls]Sheet1, then the formula must return a zero

Regards

Howard

8. ## 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

9. ## Re: Concatenating Data (2007)

Does this do what you want? I obviously can't test it.

=B3&"-"&IF(D3="","",TEXT(INDIRECT("'G:WorkfileZ-Accnts[BR1.xls]Sheet1'!N"&D3),"#,##0"))

10. ## 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

11. ## Re: Concatenating Data (2007)

Hi Hans

Please find Source Data. Sent you destination workbook containing formulas to correct

Regards

Howard

12. ## 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?

13. ## 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

14. ## 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.

15. ## 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

Page 1 of 2 12 Last

#### Posting Permissions

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