Select a sheet to print from, using a dropdown

Question

I have a "workbook" with multiple sheets. Each sheet is a schedule for the week for employees with a bunch of math stuff to help a general manager see their labor.

I'm trying to create a single sheet that would reside in the beginning or end of the workbook which would allow the General Manager to select a week (select a sheet) with the appropriate week and load the data in print friendly format.

So Dropdown is easy, and I can pull info from a single sheet, but how do I make the values change? I would think the formula would be SHEET!C2:C74 which pulls data from that particular sheet and that range, now how can I change it dynamically based on all the sheets in the workbook?

Answer 1

You can use indirect for this. Suppose the dropdown is in cell A1 of the summary sheet, and its possible values are Monday, ..., Friday. You also have sheets named "Monday", ..., "Friday".

Then all you need to do is to put the formula

=indirect(A1&"!A:Z")

somewhere on the summary sheet, e.g., in A2. It will import the columns A-Z of whatever sheet is named in the cell A1. Adjust the range as needed.