Forum Discussion
Excel Auto Update
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.
kjrobinson310 I think you have 2 problems:
1) you are using TODAY() which is only today's date but you should use NOW() so you can subtract hours from now (do you want to subtract 5 hours or 7 hours?)
2) you have VLOOKUP( , , ,0) meaning it is looking for an exact match. try using a 1 or deleting the ,0 part. That should work as long as your list is in order from small to large. If the list won't be in order like that and you must use ",0" exact match then add INT() around the check value so:
OPTION 1:
=VLOOKUP(D26-TIME(7,0,0),D28:F30,2)
OPTION 2:
=VLOOKUP(INT(D26-TIME(7,0,0)),D28:F30,2,0)
and don't forget to change D26 to be =NOW()
