Populate Required Fields with data from various worksheets based on drop down list

I hope I am doing this correctly, this is my first forum help request.

I am creating an Excel Spreadsheet that will be used analyze the performance of employees on a monthly basis.

I have 14 Worksheets. 1 Which we are calling the report card, 12 others for each month of the year and 1 for reference tables.

On the Report card sheet I have a drop down list that pulls data from a Lookup Table based on the name of the employee. Works like a champ. Is there any way to have excel pull data from a different worksheet based on a selection from another drop down list.

I.e. I want to be able to select employee JOE BLACK from drop down menu 1 and select the desired month of review (Different Worksheet for each month) from drop down menu 2. I then I want to auto populate my required fields on the report card from the worksheet specified in drop down menu 2 with data relative to the employee in drop down menu 1.

I hope this makes sense.

T

Attached is a representation of the file I am trying to develop.

The Employee is selected in C1. I then select the month in C3.

I want C1 to determine the employee, which it does. I want C3 to determine which sheet data gets pulled from.

If I select Mr. Black in C1 and June in C3, my data should be 1, 2, 3, 4 in fields C5, C6, C7, C8 respectively.

If I select Mr. Black in C1 and July in C3, my data should be 17, 18, 19, 20 in fields C5, C6, C7, C8 respectively.

I was thinking that it would be a indirect function, but cannot seem to figure out how to make that function work using two variables.

Okay. I did some more digging on figured it out.

Thank you to everyone who read this post. I appreciate it.

Here is the result for future reference.

I named each table on the various table according to the month the data was derived. I used the VLOOKUP function based on the Employee name with a nested Indirect that referenced the table equal to the month chosen in the month list.

The formula looked like this: =VLOOKUP(C1,INDIRECT(C3),2,FALSE)

Looking back, this is such an easy solution.

Have a good day.

T

CMAN, Thanks for the input.

I understand this entire formula =VLOOKUP($C$1,INDIRECT($C$3&"!A2:F5"),ROWS($A$1:A1)+1,0) with the exception of ROWS($A$1:A1)+1 what does this do for me. Obviously it works, I just do not know why. That range is not a ref for anything so why is it there?

Thanks for the help.

Got it. Makes perfect sense. I appreciate it.