Sum until value reach within dates

[Ellmal]

Hi everyone,

It is my first time posting here so hello everyone!

I am creating dashboard for hosipitals to help them with meeting demand for scanners (CT, MRI etc). It is already massive spreadsheet with lots of tabs and being under developemnt for months but now I am strugglig with one tiny(BIG!) issue that prevents me to complete the dashboard.

I hope you can see above screenshot if not I attached the file.
This is changed and simplified version of the actual work due to confidentiality (apologies) but should do the job.

My problem is:
Each machine works certain number of hours per quarter (which is displayed in row 4) and it varies depends on year. Each machine need to be replaced after reaching max level of hours (shown in cell H2). What I need is to sum hours until it will reach max capacity. As in example I inputed data manually but apart from column A and B (marked in blue) rest should be automated. What I need is to find date for each machine to be replaced (Column C) and displayed it in columns D-S (this part I can do on my own). As you can see most crucial part is to find that bloody replacement date, then I can continue with the rest.

Notes: Spreadsheet is formula based and while it is not a problem with extra helper rows/columns I would rather try to avoid VBAs.

As seen in machine2(M2 - Column B7) etc there are different start dates do sum must start counting beginning of that date, as in example - before "Q4 2021" it didn't calculated sum and started from that date (Q4 2021-Q4 2022).

Let me know if you have any questions.

Thank you for help in advance, if someone could find a way it would be very helpful.

Regards,
Ellmal

 

[Oaktree]

I'd add a new column between C & D and label D5 (after inserting) "Match".

After inserting that column:

D6 = MATCH(B6,$E$5:$T$5,0)
E6 =CHOOSE(2+SIGN(COLUMN()-COLUMN($D$5)-$D6),"","ok",IF(OR(D6="n/a",D6="R"),"n/a",IF((E$4+SUMIF(D6:$E6,"ok",D$4:$E$4))>$I$2,"R","ok")))
C6 =INDEX($E$5:$T$5,MATCH("R",E6:T6,0))

Then, copy C6:D6 down and copy E6 down and right.

 

[Ellmal]

I'd add a new column between C & D and label D5 (after inserting) "Match".

After inserting that column:

D6 = MATCH(B6,$E$5:$T$5,0)
E6 =CHOOSE(2+SIGN(COLUMN()-COLUMN($D$5)-$D6),"","ok",IF(OR(D6="n/a",D6="R"),"n/a",IF((E$4+SUMIF(D6:$E6,"ok",D$4:$E$4))>$I$2,"R","ok")))
C6 =INDEX($E$5:$T$5,MATCH("R",E6:T6,0))

Then, copy C6:D6 down and copy E6 down and right.

Hi,

That worked like a charm! Thank you.

As a matter of fact it worked so great (they really loved it!), after showing it to management they want extra features, I've looked into the formula you made hoping I will be able to adjust, but it is too much for me.

I feel bad asking for more as you already helped me a lot but extras would be very helpful.

In short: they want to expand forecast by more years until 2036 which requires two replacements of one machine, let me ilustrate on the same example:

As you can see each machine still has the same capacity (in reality it is about 8 years), after reaching it and hitting replacement date ("R") it has two quarters of downtime ("D") and starting to work again ("ok") and after reaching same capacity again it need to be replaced again ("R"). Some machines will extend beyond the table time (in reality it is 2036) hence "Beyond Q4 2024". Column F is what you suggested as extra (which will be hidden in final version).

Again: extra columns/rows are welcome, even naming might be different: "ok", "R" etc. change to whatever suits best.

Thank you again for help, let me know if you have any questions.

Regards,
Ellmal

 

[Ellmal]

Again: I dont know how to edit posts, apologies.
I can see there was an issue with image loading so please find attached it again (hopefully it will work this time)