Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 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

  2. #2
    2 Star Lounger
    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

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Concatenating Data (2007)

    Hi Christine

    Thanks for the reply. Attached please find sample data as requested



    Regards

    Howard
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    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

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

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

    Re: Concatenating Data (2007)

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

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 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:WorkfileZ-Accnts[BR1.xls]Sheet1, then the formula must return a zero

    Regards

    Howard

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 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

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

    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. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 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
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Concatenating Data (2007)

    Hi Hans

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

    Regards

    Howard
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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

Page 1 of 2 12 LastLast

Posting Permissions

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