Dynamic Calendar that predicts when something will need to be ordered again

Hello! I have an excel sheet where I input when something has been ordered and it keeps a log for all the times that I've ordered it in the past and I want to add a function on the second sheet where it pulls the date on the same row and in relation to the thing I ordered so that when I input the thing I ordered again in the future, the second sheet will update automatically and grab the newest date. This is done to track the hours spent on the thing ordered every time it is ordered and there are two columns for each month because sometimes things are requested for order but they arent ordered so thats why the second column is there. Attached is an example of the excel sheets that I've been using and if you need me to explain anything more thoroughly or have any recommendations on how to improve it in anyway aside from pulling the data from the previous sheet then that would be greatly appreciated as well!

My first impression was that this was hugely manual and needed a fair bit of automation.

1. It was very confusing that the first date was late 2020 and your calendar started in Jan 2019.

2. I deleted 2019. However, you will see in a minute, that if you need to add it back in, it is now simple.

3. I deleted columns E & F. They are not needed.


4. I deleted G1:BB1. It's not used, not needed and the column is so wide that it's not much use, anyway.

5. All cell references now refer to the modified sheet. I moved your total rows out of the way for the moment.

6. In G1, add 01/01/2020 and in I2, 01/02/2020. Format (CTRL-1, custom) as "mmm/yy"

7. Select both cells. Copy/paste to BB1. Now the value in G1 sets the range of dates for the sheet. If you want to add 2019 back in change G1 to 01/01/2019 and everything changes in a millisecond.

8. in G2: =IFERROR(IF(AND(YEAR($C2)=YEAR(G$1),MONTH($C2)=MONTH(G$1)),$F2,""),"") and copy down to G20

9. In H2: IFERROR(IF(AND(YEAR($D2)=YEAR(G$1),MONTH($D2)=MONTH(G$1)),$F2,""),"") and copy down to H7

These two formulae pick up the date from column C/D and match it with the month/year from the header.

10. Select G7:H7 and copy/paste to BB7.

Now the whole table is automated. However, I don't really understand what you want to do AFTER this point. So now, I'm guessing.

11. I used a range of formulae to return the data, mostly similar to this one (different shading = different formula), all copied down.

=IFERROR(INDEX('Input Data'!D:D,AGGREGATE(15,6,ROW('Input Data'!$D$3:$D$20)/('Input Data'!$D$3:$D$20<>""),ROWS(A$2:A2))),"")

Are the cycle times always 365? If not, we'll need to add a bit more on elsewhere.

Currently your totals column is floating at the end. We can address that shortly. But for now, how does this look? Did I guess your intentions correctly?

I took another look at this. I think I must have fixed any concerns I had about the cycle time, as the formula looks OK to me. I relocated yout totals to the top of the sheet (I always think thetop of a sheet is the place for a summary!!).

It now pulls through a list of unique products (column A), the complementary info (col B) and the LAST date of purchase (col C).

With regard to the cycle times... where are these stored? In the same table as the input sheet? Elsewhere (if so where)?

1. If the cycle times are in the same table as the input sheet, do nothing - except tell me.

2. if they are elsewhere, add a table/sheet to THIS version of the file and re-post it.

3. To minimise inefficiencies, it might be an idea to use Named ranges in places here and there. Do you know how to set these up? If not, I will do it and explain.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.