Forum Discussion
Excel Auto Update
Good morning,
I have an excel spreadsheet that is an 'on call' schedule. I have 30 different department schedules on 30 different tabs in a document. And then I have one tab that is setup as a 'daily' schedule, this sheet pulls the data from the other 30 tabs dependent on the day. What information that is pulled from these other tabs has been based on what todays (=today()) date is. I have discovered an error that I don't know how to fix. The 'today' function technically updates at midnight, so if someone opens this document at 1 am, they are getting the next days call schedule information which would be incorrect as the shifts are usually 7p-7a or 7a-7p. Is there a way for me to make it so the document cells don't auto update until a different time of day? like 6 or 7 am? Thank you for your help.
I attached a picture showing the date and then the vlookup formula I am using. I have erased the other info for privacy.
4 Replies
I'd try something like this:
=VLOOKUP(C1-TIME(5,0,0),Administration!A5:B550,2,0)
In effect, somebody looking at this at 1 a.m. would be seeing what they would have seen at 8 p.m. on the previous day with the formula as you currently have.
I'm assuming that C1 is the cell containing the =TODAY() function. I tried this just on a single cell, not as the argument in a VLOOKUP function, but when I subtracted hours from a TODAY cell, enough to push it back a day, it worked. Wouldn't change the value showing in C1, but would get the right thing for someone at 1 a.m.
mathetes I tried the formula both within the vlookup and outside and can't get it to return any information. I played around with the hours as well, making it far enough so that if someone was to open it now, it would change, but still nothing.
D26 is where the =today is at
date of 7/29 is cell d28
and i'm trying to return the 'on call' info in the cell nexdt to the 7/30 cell.
And if the suggestions from mtarler for some reason don't work (we often work as a tag team here on the techcommunity boards)....if those don't work for you, might I suggest that instead of an image you upload a sample of your actual workbook after putting names like Luke Skywalker and Mickey Mouse in instead of the real names. And anything else that is proprietary or private info should be changed so as to be anonymous.
