Problem with copying dates and search for a date in a column

[DarkoDeign2]

Hi,

Col B3 has a date
Col B4:B1500 contains a formula that checks if B4 and D4 iempty. If both cells are empty do nothing. If D4 is empty then B4 should show nothing. But if D4 has text, the B4 should show the value from the closest cell above that contains a date. The formula in this file has a slighty different function and is not working for me, as it show the wrong date. What should I change in the formulas in col B4:B1500?

Book1
	B		D
2	Date		Topic
3	2023-07-26		BBQ Party
4	1900-01-00		Pay electrical bills
5	1900-01-00		Renew subrscription of newspaper
6	1900-01-00		Get a hair cut
7
8	1900-01-00		sd
Sheet1

Cell Formulas
Range	Formula
B4:B8	B4	=IF(D4="","",IF(B4="","Enter date", B3))

 

[Ezguy4u]

Dark, I am not sure that your formula should work the way you want it to because you reference B4 in B4, So let's start with a question. What date do you want to pay your electrical bills?

 

[DarkoDeign2]

I would like to pay my electrical bill the same date as the cell above (2023-07-26)

 

[Ezguy4u]

Darko how do you feel about a VBA program? There may be somebody out there that can solve your problem with formulas, but I am not one of them.

 

[Peter_SSs]

Can you show us what your sheet looks like before any formulas are entered into column B?

Is there only one cell in column B with a date (B3 in your sample)? If more than one, please include more than one in your sample data.

 

[DarkoDeign2]

It is only one cell B3 that has a date. The rest of the cells in col B are empty.

 

[Peter_SSs]

Thanks for the additional information

cells in col B are empty.

In that case there is no need for the formula to check if column B is empty.

Does this do what you want?

DarkoDeign2_1.xlsm
	B		D
1
2	Date		Topic
3	26/07/2023		BBQ Party
4	26/07/2023		Pay electrical bills
5	26/07/2023		Renew subrscription of newspaper
6	26/07/2023		Get a hair cut
7
8	26/07/2023		sd
Sheet1

Cell Formulas
Range	Formula
B4:B8	B4	=IF(D4="","",LOOKUP(9^9,B$3:B3))

 

[DarkoDeign2]

Thanks! this works fine.
I would like to continue to build on the formula. In the example there is a blank row on row 7.
If there are blank rows I would like the next cell B8 to show the date from the closest cell in col b and add 7 days to that date.
So in this case I would like the date in cell B8 to be 03/08/2023.

 

[Peter_SSs]

7 days added to 26 July give 2 August for me, not 3 August.

Try below.

BTW, best to give us the full requirement to start with. Building bit by bit often means that the right strategy is not commenced from the beginning and all the early work is wasted when a completely different approach is eventually required by one of the added bits.

DarkoDeign2_1.xlsm
	B		D
1
2	Date		Topic
3	26/07/2023		BBQ Party
4	26/07/2023		Pay electrical bills
5	26/07/2023		Renew subrscription of newspaper
6	26/07/2023		Get a hair cut
7
8	2/08/2023		sd
Sheet1

Cell Formulas
Range	Formula
B4:B8	B4	=IF(D4="","",LOOKUP(9^9,B$3:B3)+7*(B3=""))

 

[DarkoDeign2]

Yes, I totally get that. Thank you very much for the help