Google-sheets – Select a sheet to print from, using a dropdown

google sheets

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?

Best Answer

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.

Related Solutions

Google Sheets – How to Auto-Update a Reference to Another Sheet

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been working for me across a lot of sheets.

My formula previously looked something like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")

And now it looks like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")

Update:

This method no longer works since Google no longer permits now() inside importrange(). See comment from Hugh below.

Google Sheets: Lookup and Copy Data from Different Sheets

Your plans seem sensible to me and I think reasonably straightforward to implement. A formula such as:

=importrange("1NsEAt1biV822J4RnqOmb4MW6GCjrkUzfZjbssnZmkSQ)","Sheet1!A1:F8")

in each of the employee sheets in the Director's cut should import Sheet1!A1:F8 from each individual employee's document (once access has been granted). The long string of characters in the middle of the above formula is the 'key' and is unique to each document.

When all the relevant details are in the Director's copy it should then also be quite easy to process them further for analysis purposes.

Best Answer

Related Solutions

Google Sheets – How to Auto-Update a Reference to Another Sheet

Update:

Google Sheets: Lookup and Copy Data from Different Sheets

Related Topic