PDA

View Full Version : Need to correct a VLOOKUP/ INDIRECT combo formula



spittingfire
10-19-2016, 04:29 AM
HI All,

I've attached an excel sheet with some sample data. In "Sheet1" in column J I've constructed a vlookup / indirect combo formula that is working for me on and off. I just need to get something stable that will work all the time no matter how many times the file is opened and closed.

What I'm trying to do is to lookup the time in cell N1 then match the name from column B to the sheet name then return the values from column AE of the matched sheet where the time matches AB on the matched sheet.

Initially the formula worked but then doesn't work anymore and I'm a bit lost.

Any help would be much appreciated.

Thanks17370

mana
10-19-2016, 05:19 AM
I tried, it works correctly.

spittingfire
10-19-2016, 05:32 AM
hmmm - yes I did to and it works fine for me to but I think once the half hour interval changes and I re-open the file all I see is N/A's and it's driving me nuts. I just don't know why.

For example when I opened the file during the 7:30 interval and inputted the formula everything worked as expected. Save and closed the file.

I then opened the file just after 8:00 AM and all I saw was N/A's. I closed the file. Opened it again around 8:35 AM and still seeing N/A's.

mana
10-19-2016, 05:50 AM
try this

=VLOOKUP($N$1,INDIRECT("'"&B2&"'!AB8:AE55"),4,1)

spittingfire
10-19-2016, 06:19 AM
Thanks mana - I will give that a try and report back.

spittingfire
10-19-2016, 06:36 AM
Awesome - that did the trick. Thanks again mana for your assistance.

mana
10-19-2016, 06:40 AM
N1;=VALUE(TEXT(CEILING(NOW(),1/48)-"00:30","h:mm"))+0.000001