If cell is blank then equal (not using conditional formatting)

**StuartMansfield** · 09-06-2012, 03:43 AM

I have created a training package where I have used the conditional formatting to change a cells colour as the certificates for the staff is due to run out. I have created this down the columns.

However if there is no text in a range of cells i would like the cell to say "Awaiting Training" instead of being blank.

Is there an easy way of doing this using a formula as i'm not good with VB?

**dilipandey** · 09-06-2012, 03:55 AM

Hi Stuart,

Welcome to the forum.

you can use isblank function here.. =if(isblank(<reference to range of cells>),"Awaiting Training",<reference to range of cells>)

In case of any issues, upload the sample workbook... thanks.

regards,
DILIPandey

<click on below 'star' if this helps>

**vlady** · 09-06-2012, 03:56 AM

what is your existing formula?

you can integrate this one

=IF(COUNTA(A1:X1)=0,"Awaiting Training", your existing formula)

**Kevin UK** · 09-06-2012, 04:01 AM

Hi dilipandey

I think Stuart requires it as in conditional formatting, I thought of using a IF function but it means starting a new column!

**StuartMansfield** · 09-06-2012, 04:15 AM

Thanks, but everyone's right I son't want it as conditional formatting.

DILIPandey thanks for your reply but I put this in and its just placed the word <Value> in the cell I typed it into. All the blank cells are still blank.

**Kevin UK** · 09-06-2012, 04:21 AM

If you don't want it as conditional formatting,try:

If your data is in A1 in B2: =IF(A1="","Awaiting Training",A1)
Drag down.

**dilipandey** · 09-06-2012, 07:17 AM

Hi dilipandey
I think Stuart requires it as in conditional formatting, I thought of using a IF function but it means starting a new column!

Hi Kevin UK, I think OP has already mentioned below:-

If cell is blank then equal (not using conditional formatting)

which clears that he does not want this solution as conditional formatting. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**dilipandey** · 09-06-2012, 07:19 AM

DILIPandey thanks for your reply but I put this in and its just placed the word <Value> in the cell I typed it into. All the blank cells are still blank.

Hi Stuart, do you want the blank cell to be automatically filled in... if that is the case, you need to rely on macros (VBA).. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>