Change the every time

[Mamdouh Elfors]

hi
I have to change the formula in column D every time
How do I make the formula automatic?
thanks

 

[vletm]

Mamdouh Elfors
Should You formula already know - how to change it itself?
= is there any clear logic - what should those formulas do?

 

[Mamdouh Elfors]

in column H, if it has a value that isn't 0, column D value in the same row takes the first date from column J.

Then skip two rows in column D and set the value of the current cell the same date.

Then you scan column H for the next value that isn't 0 and if you find the next non-zero value, you set column D of that row to the second date in column J.

Then skip two rows in column D and set the value of the current cell the same date.

and so on ...

 

[Peter Bartholomew]

= LET(
  k,    SEQUENCE(ROWS(Table1)),
  idx,  FILTER(k,Table1[Gift]<>0),
  date, XLOOKUP(k,idx,Date,0)+XLOOKUP(k-3,idx,Date,0),
  IF(date, date,""))

 

[Mamdouh Elfors]

Peter
thank you for your effort
I have excel 2016
Some functions in the formula do not exist

 

[bosco_yip]

In D3, formula copied down :

=IF(H3=0,"",INDEX(J$2:J$32,INT((COUNTIF(H$3:H3,"<>0")-1)/2)+1))

Regards

 

[Mamdouh Elfors]

bosco_yip
Thank you amazing

 

[Peter Bartholomew]

@mamdouh
Sorry about that; I only saw, from another of your posts, that you use Office 2016. I have found that the thought and planning process is so different when using 365 to the full that I have deleted earlier versions from my computers.

 

[Mamdouh Elfors]

@mamdouh
Sorry about that; I only saw, from another of your posts, that you use Office 2016. I have found that the thought and planning process is so different when using 365 to the full that I have deleted earlier versions from my computers.

@Peter Bartholomew
Thank you for your response. I understand the differences between 365 and 2016 are great.