Schedule Planning to the future.

Hello,

I will start off by saying I'm using Google Sheets. I'm not sure if that changes what the answer will be, but here we are.

At work, we're trying to get a monthly tracker going and so far, I've created a script that pulls the headcount for each department every night at 5pm and puts a timestamp for the day on it. This is great for seeing the the headcount every morning wen we come in for the meetings, however, because the future dates haven't been populated yet, we can't see into he future from a planning perspective.

What I am trying to do is make it so that if the day in the future hasn't happened yet, it looks to the last day that did happen and pulls that number. The problem I'm running into though stems from trying to use arrays that then see themselves and blank out because I also incorporated a formula that if within the schedule, that shift is not supposed to be here, the cell remains blank. Any thoughts on the better way to do this? I am at a loss, but also fairly new to spreadsheets, so I'm not sure if there's a better way.

This is the formula that I currently have:

=IFERROR(IF(INDEX(Schedule!$A$2:$F$9,MATCH(O$1,Schedule!$A$2:$A$9,FALSE),2)="x",INDEX(History!$A$4:$G,MATCH(O$2,History!$G$4:$G,FALSE),1)+INDEX('Roll Up'!$P$19:$S24,MATCH(O$1,'Roll Up'!$P$19:$P,FALSE),2),""),ArrayFormula(LOOKUP(2,1/(3:3<>0),3:3)))

History is the auto-populated data just for reference.

Please let me know if I can help with anything else!

The greaty thing about Google sheets is that it is easy to share a copy of your file - keep one sheeet with what you have, and create a sheet without formulas to would show what you want.

Copy the link to your file and paste the link as text - throw in a couple of spaces to break the text of the link into pieces and you should be able to post the message, and we'll be able to figure out how to fix the text to make the link work. If you ever need to post your email address, just type it out like you were speaking it: my dot email at domain dot com

I guess I can do it this way.

The link is in this excel doc. Wouldn't let me post, even with spaces.