Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell value if (2002)

    I have data in I19 always. There could be data in I21, I23 ....... I37. If there is no data in I21, I23 ....... I37, then those cells should contain &nonbreakingspace; (But I can't post that here?)

    In cell K50, I want:

    If there is data only in I19 > concatenate("Ref ",I19)

    If there is data in any of the cells I21, I23 ....... I37 > concatenate("Refs ",I19," - ",I......) being the last cell in the series.

    Please.... I have no idea if this is achievable?

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

    Re: Cell value if (2002)

    1) Do you mean the text " " or do you mean a non-breaking space (ASCII character 160)?
    2) How about cells I22, I24, ..., I36?

    (You can't enter the text &nbsp; normally because it is a HTML code - the Lounge displays it as a non-breaking space...) <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value if (2002)

    << You can't enter the text * normally because it is a HTML code - the Lounge displays it as a non-breaking space.

    Aah, that's what happened.....

    The cells if not overwritten with data will contain the text &nb...sp, so that the table in outlook does not fall apart.
    The even numbered cells are to be ignored completely.

    Thanks

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

    Re: Cell value if (2002)

    > The even numbered cells are to be ignored completely.

    OK, I understand that, but will the even-numbered cells be blank (empty), or can they contain something?

  5. #5

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

    Re: Cell value if (2002)

    Try this array formula (confirmed with Ctrl+Shift+Enter):
    <code>
    ="Ref "&I19&IF(MAX((I21:I37<>"&nbsp;")*ROW(I21:I37)*(MOD(ROW(I21:I37),2)=1))>0," - "&INDEX(I1:I37,MAX((I21:I37<>"&nbsp;")*ROW(I21:I37)*(MOD(ROW(I21:I37),2)=1))),"")</code>

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value if (2002)

    Hans,
    I am assuming that the &INDEX(I1 is a typo... Changed that
    But I am getting #VALUE!
    In the fx, the value if true shows #REF.?

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

    Re: Cell value if (2002)

    No, it was not a typo, it was intended that way.

    Have you confirmed the formula with Ctrl+Shift+Enter?

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value if (2002)

    <img src=/S/smash.gif border=0 alt=smash width=30 height=26> <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23> Ok, not a typo (sorry) and no I did not <img src=/S/smash.gif border=0 alt=smash width=30 height=26>

    But, I have merged cells above I19 and array formula's don't like merged cells? It would not be easy to change the layout of the upper form.

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

    Re: Cell value if (2002)

    Could you attach a small sample workbook? You can clear everything except I19:I37 (and replace sensitive data with dummy data).

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value if (2002)

    No. it was K50 being merged that was causing the problem!!

    Sorted, many thanks!!
    Attached Files Attached Files

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

    Re: Cell value if (2002)

    The problem is not that cells above I19 are merged, but that the cell with the formula (K50) is merged. To get around this:
    1) Select K50.
    2) Select Format | Cells...
    3) Activate the Alignment tab.
    4) Clear the "Merge Cells" check box.
    5) Select "Center across selection" in the Horizontal alignment dropdown.
    6) Click OK. This will leave K50:M50 selected.
    7) Select K50.
    8) Press F2 to edit the formula.
    9) Press Ctrl+Shift+Enter.

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value if (2002)

    Cheers Hans.

    =CONCATENATE("QTY "&G50&". TOTAL VALUE "&I50&". "&K50)
    =CONCATENATE("QTY "&G50&". TOTAL VALUE "&Format(Range("I50"), "£#,##0.00")&". "&K50)

    The top one works, but the bottom one does not, any idea's?

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

    Re: Cell value if (2002)

    Format and Range are VBA functions, not worksheet functions. Use the TEXT function instead.

    You don't have to use CONCATENATE if you're already concatenating the strings with &. Use either CONCATENATE or &, not both.

    So try this:
    <code>
    ="QTY "&G50&". TOTAL VALUE "&TEXT(I50,"£#,##0.00")&". "&K50</code>

Posting Permissions

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