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?

1 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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.