activate formula when adjacent cell populated
Dear All,
I have copied a simple formula (A1+1095) down the length of column B (working with excel 2003). A1 contains a date, the formular in column B produces a date 3 years later, with conditional formatting added to indicate how close the future date is getting. The problem I have is that where there is no date yet entered into a cell in column A, the remainder of column B automatically displays 30/12/1902 and formats the cells red (for the rest of the entire column). Is there a way to direct the formula in column B to only become active once a date has been entered into the adjacent cell in column A? - I was thinking along the lines of an IF statement or similar as I am prohibitted from using a macro. Advice gratefully recieved. Simon. |
activate formula when adjacent cell populated
Hi Simon
Modify the formula to =IF(A1,A1+1095,"") -- Jacob (MVP - Excel) "Simon" wrote: Dear All, I have copied a simple formula (A1+1095) down the length of column B (working with excel 2003). A1 contains a date, the formular in column B produces a date 3 years later, with conditional formatting added to indicate how close the future date is getting. The problem I have is that where there is no date yet entered into a cell in column A, the remainder of column B automatically displays 30/12/1902 and formats the cells red (for the rest of the entire column). Is there a way to direct the formula in column B to only become active once a date has been entered into the adjacent cell in column A? - I was thinking along the lines of an IF statement or similar as I am prohibitted from using a macro. Advice gratefully recieved. Simon. |
activate formula when adjacent cell populated
Sorry, NOT Datediff!
=IF(A1="","",DATE(YEAR(A1)+3,MONTH(A1),DAY(A1)) "Arvi Laanemets" kirjutas sõnumis news: ... Hi =IF(A1="","",A1+1095) Or =IF(A1="","",DATEDIFF(YEAR(A1)+3,MONTH(A1),DAY(A1) ) Arvi Laanemets "Simon" kirjutas sõnumis news: ... Dear All, I have copied a simple formula (A1+1095) down the length of column B (working with excel 2003). A1 contains a date, the formular in column B produces a date 3 years later, with conditional formatting added to indicate how close the future date is getting. The problem I have is that where there is no date yet entered into a cell in column A, the remainder of column B automatically displays 30/12/1902 and formats the cells red (for the rest of the entire column). Is there a way to direct the formula in column B to only become active once a date has been entered into the adjacent cell in column A? - I was thinking along the lines of an IF statement or similar as I am prohibitted from using a macro. Advice gratefully recieved. Simon. |
activate formula when adjacent cell populated
Try this:
=IF(A1="","",A1+1095) You may need to adjust your CF formula to avoid zero values. Hope this helps. Pete On May 5, 11:50*am, Simon wrote: Dear All, I have copied a simple formula (A1+1095) down the length of column B (working with excel 2003). A1 contains a date, the formular in column B produces a date 3 years later, with conditional formatting added to indicate how close the future date is getting. The problem I have is that where there is no date yet entered into a cell in column A, the remainder of column B automatically displays 30/12/1902 and formats the cells red (for the rest of the entire column). Is there a way to direct the formula in column B to only become active once a date has been entered into the adjacent cell in column A? - I was thinking along the lines of an IF statement or similar as I am prohibitted from using a macro. Advice gratefully recieved. Simon. |
All times are GMT +1. The time now is 06:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com