Dynamic References Based on Two Criteria (INDIRECT OR VLOOKUP?)

[hotelguy]

Hello all,

I've been lurking on the forums for awhile now to fix most of my Excel headaches, but this particular problem has gotten me vexed enough to start up an account and ask for help! Who knows, maybe I can contribute a thing or two, as well. Anyway, glad to be here and thankful for any help that can be provided.

I have a running spreadsheet that tracks the same key bits of information over time. Each date is a new [EDIT] worksheet and each workbook contains data for 13 hours of the day for 16 variables. I am trying to create a simple summary page where I can type the date and time I want to pull data from and have each dataset populate from the appropriate workbook and time.

The [EDIT] worksheets are set up something like this (workbook is named 11.08.15 in this example)

A B C D E
1 8:00 AM Occupied Rooms Occupied %

2 9:00 AM 855 55%

3 10:00 AM 856 55.01%

4 11:00 AM 900 57%

5

The first worksheet (Titled "Copy This") is where I want to be able to dynamically pull summary information and looks something like this:

A B C D E

1 Enter Date Here: (I want to be able to enter a date here, which would reference the workbook by the same name)

2 Enter Time Here: (This is the second search criterion, and should identify the row from which to pull the data)

3 Occupied Rooms: (By entering the two criteria above, this should reference !B and the corresponding row from the time)

4 Occupied %: (Same as above)

5

I've tried an INDIRECT function to reference the variable worksheets, I've toyed with INDEX, MATCH and VLOOKUP but I can't hit the formula to be able to get the spreadsheet to do what I want.

Any help you can provide would be awesome!

Thanks,
hotelguy

[Richard Buttrey]

Hi,

INDIRECT() won't work with closed workbooks. Why not have a macro that opens the workbook and then builds the Indirect() functions?

[hotelguy]

Hi,

INDIRECT() won't work with closed workbooks. Why not have a macro that opens the workbook and then builds the Indirect() functions?

Made a rookie typo - it's all in one workbook and I'm referencing different sheets. Hopefully that can make it work without having to dive into macros (I'm net quite there yet!).

[FDibbins]

Hi, welcome to the forum

Often, a copy/paste doesnt work out quite as you wanted it to Can I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.