Lookup into another worksheet

Hi All,

I am currently in the process of creating a report based on action codes, time & occurances. I have successfully configured it using VLOOKUP's & COUNTIF's (Overview tab & Data tab).

What I am aiming to achieve is to create a dropdown on the overview page with Week 1,2,3,4,5 etc. Then depending on which week is selected the vlookup will look into "WeekX.xls" and display this data (all .xls documents will be in the same format/layout)

Is this achievable to add to my current formulas?

Thanks in advance for any replies/help

Go to the Developers Ribbon, then click on Insert and pull a Combo Box into your spreadsheet. Right click on the combo box and select "Format Control" from the pop-up menu. Input range equals the column of values you want to retreive and an additional column preceding it numbered 1 to however many values you want to look up. "Cell Link" is the cell that will retreive the numeric placement of the list of values in your vlookup list. Example: Apple = 1st in list, Bravo = 2nd in list, Charlie = 3rd in list so going down the values would be 1,2,3.

You might want to hide the cell link cell behind the combo box.

Hi Alexander,

Thank you for your reply - so if "week 2" was selected how do I get my current VLOOKUP/COUNTIF to look into "week2.xls" & extact the data just asif it was in the same worksheet?

Example of current lookup =IFERROR(VLOOKUP(B8,Data!A:C,3,FALSE),"")

Thanks,