# Thread: Cell value if (2002)

1. ## 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. ## Re: Cell value if (2002)

1) Do you mean the text "&nbsp;" 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. ## 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. ## 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?

Always empty

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Cell value if (2002)

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

Sorted, many thanks!!

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