Forum Discussion
Separate Excel tabs populate calendar or master list or front sheet
Excerpts from your post are in italics; my replies in regular text.
I'm looking for a bit of help. I'd like to create a workbook, with multiple sheets, containing similar data, (but owned by different team members, hence the need for separate sheet/tabs).
I'd then like to create a front sheet, that is automatically populated with some but not all of the data from the multiple tabs.
The purpose: there are 4 people in my team, each in charge of one of the tabs, and my boss would like a consolidated view of certain elements of our work (high level detail - dates, description, client).
What you're asking is very possible. Much of how easy it will be has to do with how consistently the other (let's call them "subordinate") sheets are laid out.
My challenge is we're only operating with 2008 version of Excel - crazy I know! I do have office 365... but no idea whether that makes any difference....
So multiple questions:
1. Is this even possible in this version of excel
2. If not, which version is this possible in?
3. And then the killer question - how do I do this!
1&2. Yes, as already noted, it's possible. It's long been fairly easy to create a reference on one sheet (the summary sheet) to another (the subordinate sheet). There are straight forward ways; there are sexy ways. As noted, a lot depends on how your subordinate sheets are laid out.
And why are you not using Office 365 if you have it??!!
3. Look into the INDIRECT function -- which would be useful if subordinate sheets are laid out in an identical manner; e.g., cell C3 ALWAYS includes the next review date (or some such). Here's a link to a great resource of INDIRECT guidance (and a lot of other functions and capabilities). https://exceljet.net/excel-functions/excel-indirect-function
I'm not precious on how the front sheet data is displayed, ideally it would show in a calendar view, but I'd also take a list.
Extra challenge here is that I'd like it to display in date order.
I like how the word "precious" snuck in there 😉
So you do realize there are various kinds of calendars, right? Some could even resemble a list view; and sorting in date order is relatively easy.
Phew! Any help gratefully appreciated and happy to explain my challenge further if required.
A copy (without confidential or personal info) of your current spreadsheets--or a mockup of them--would be helpful.
Hello mathetes thanks for your rapid response. I guess it's fairly obvious I'm an Excel novice huh?
So, I'll copy your format of response.... you're in italics this time
What you're asking is very possible. Much of how easy it will be has to do with how consistently the other (let's call them "subordinate") sheets are laid out.
My data is fairly clean and consistent. It's text heavy, which might be a challenge? I will attach a sharing safe-version as an example... it has one suggested summary sheet and three subordinates (much better word to describe!). The columns are consistently named and match the summary sheet. There's a lot of data I don't need to reflect in the summary sheet, but needs to be kept in the subordinates for other reporting purposes. For sharing, I've stripped it out.
1&2. Yes, as already noted, it's possible. It's long been fairly easy to create a reference on one sheet (the summary sheet) to another (the subordinate sheet). There are straight forward ways; there are sexy ways. As noted, a lot depends on how your subordinate sheets are laid out.
And why are you not using Office 365 if you have it??!!
I am indeed using 365! Don't worry! It's certainly life-changing for my team! I think what I meant, is that the version of Excel via 365 wouldn't be any younger? I'm certainly no expert though. We'd keep this file on 365 too, which has actually triggered the want of it!
3. Look into the INDIRECT function -- which would be useful if subordinate sheets are laid out in an identical manner; e.g., cell C3 ALWAYS includes the next review date (or some such). Here's a link to a great resource of INDIRECT guidance (and a lot of other functions and capabilities). https://exceljet.net/excel-functions/excel-indirect-function
Thank you very much for the Indirect recommendation - I will take a good look at this. I didn't know where to start looking... new to VMLOOKUP, APPENDING etc...
I like how the word "precious" snuck in there 😉
So you do realize there are various kinds of calendars, right? Some could even resemble a list view; and sorting in date order is relatively easy.
And I actually started this challenge by using various calendar templates, then couldn't work out how to populate the calendar from various subordinate sheet sources... and now here I am!
I'm glad to hear this isn't rocket science, but clearly not my area of expertise 😉
It may be Power Query that you need. (Which, on my Mac, I don't have.)
You don't say what exactly you are expecting here, but I'm assuming you are wanting all rows, but not all columns, from those three subordinate sheets to get pulled into the front sheet, in date order. Or maybe it's only one month's worth of rows from each of the subordinate.
Whatever it is, I'm going to defer to somebody who can explain the way Power Query would handle this.
(I would be advocating a shared database, from which it would be easy to pull a weekly or monthly consolidated "look-ahead" list, but that is not how you've approached it, and for I assume fully valid reasons.)
- Thanks - really appreciate the advice so far