Hi



Can anyone help with this problem.

I have put together a prepaid expenses schedule that calculates the prepaid values across the year. When the prepayment is fully used, the next cell shows the text "Delete Line" and is highlighted using conditional formatting. If I copy the formula beyond this point it still shows "Delete Line" in all of the following cells. Is it possible to to show this message only the once and leave all copied cells to the right as blank cells.

Many thanks

TabH

Can you show us your file?

Please see attached file

TabH

Attached Files

Prepaid Expenses 140417.xlsx (11.8 KB, 14 views)

I see more problems here.

My first thought was to use conditional formatting to make all second and higher instances of "Delete Line" invisible by making the font equal to the background, but as you use different background (white and grey) that may not be an option. Could you possibly accept same background for all months?

A problem is that you doesn't seem to use real Excel dates in I7:U7 but text strings (I may be wrong here as I don't use US-settings)

I have problems understanding what it is you try to attempt. If we take a look at row 10, can you explain very simply what 47.83 and 14.88 in cells I10 and J10 mean.

You might want to take a look at the attached.

Attached Files

Prepaid Expenses_2.xlsx (16.6 KB, 15 views)

If you don't like the conditional formatting approach you can try the following formula in cell I10:

=IF(($G10-DATEDIF($E10,EOMONTH(I$7,0),"d")<0)*(ISNUMBER(H10) ),"Delete Line",IF(($G10-DATEDIF($E10,EOMONTH(I$7,0),"d")<0)*(ISTEXT(H10)), "",($G10-DATEDIF($E10,EOMONTH(I$7,0),"d"))*$H10))

and copy as required.

Hi Xor

Both the conditional formatting and the formula seem to work fine.

I appreciate your help.

Many thanks

TabH