Creating a master sheet that groups data across worksheets based on a shared value in common column

%3CLINGO-SUB%20id%3D%22lingo-sub-2273864%22%20slang%3D%22en-US%22%3ECreating%20a%20master%20sheet%20that%20groups%20data%20across%20worksheets%20based%20on%20a%20shared%20value%20in%20common%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2273864%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20creating%20an%20excel%20doc%20to%20map%20out%20and%20organize%20features%20for%20a%20project.%20I%20have%20the%20different%20features%20broken%20down%20into%20separate%20worksheets.%20N%20each%20worksheet%20there%20are%20three%20columns.%20A%20is%20the%20story%20number%2C%20B%20is%20the%20description%20and%20C%20is%20the%20sprint%20number.%20I%20would%20like%20to%20create%20a%20master%20sheet%20that%20will%20group%20the%20data%20from%20the%20various%20worksheets%20based%20on%20the%20shared%20value%20in%20column%20c.%20For%20example%2C%20everything%20that%20has%20a%20value%20of%209%20in%20column%20c%20across%20all%20of%20the%20worksheets%20would%20be%20grouped%20together.%20I%E2%80%99d%20also%20like%20each%20of%20them%20to%20link%20to%20their%20location%2C%20so%20if%20story%20100%20is%20scheduled%20for%20sprint%209%20and%20is%20in%20the%20search%20UI%20feature%20worksheet%2C%20and%20story%20101%20is%20scheduled%20for%20sprint%209%20and%20us%20in%20the%20email%20feature%20worksheet%2C%20they%20would%20be%20grouped%20together%20on%20the%20master%20and%20I%20would%20be%20able%20to%20see%20all%20three%20column%20entries%20for%20each.%20If%20I%20were%20to%20click%20on%20the%20search%20UI%20feature%20row%2C%20I%20would%20be%20taken%20to%20the%20Search%20feature%20worksheet.%20I%20would%20also%20like%20the%20master%20worksheet%20to%20update%20whenever%20any%20of%20the%20data%20on%20the%20other%20worksheets%20are%20updated.%20Is%20this%20possible%20in%20excel%3F%20This%20is%20a%20work%20document%20and%20I%20cannot%20attach%20anything.%20I%20am%20using%20a%20PC%2C%20windows%2010%20I%20believe.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2273864%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor
I’m creating an excel doc to map out and organize features for a project. I have the different features broken down into separate worksheets. N each worksheet there are three columns. A is the story number, B is the description and C is the sprint number. I would like to create a master sheet that will group the data from the various worksheets based on the shared value in column c. For example, everything that has a value of 9 in column c across all of the worksheets would be grouped together. I’d also like each of them to link to their location, so if story 100 is scheduled for sprint 9 and is in the search UI feature worksheet, and story 101 is scheduled for sprint 9 and us in the email feature worksheet, they would be grouped together on the master and I would be able to see all three column entries for each. If I were to click on the search UI feature row, I would be taken to the Search feature worksheet. I would also like the master worksheet to update whenever any of the data on the other worksheets are updated. Is this possible in excel? This is a work document and I cannot attach anything. I am using a PC, windows 10 I believe.
5 Replies

@JessieH83821517 

 

Unless I'm mistaken the FILTER function would work (IF, that is, you have the most recent version of Excel on your Windows machine). I use it to access external files, in a process quite analogous to what you're describing, as it also allows you to specify the criteria to be met for the rows to be "imported."

 

Here's a link to a helpful video introducing the FILTER function just last year. https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
Hi Mathetes,
Thanks for the response. I should have been clearer about what I was trying to do. Everything is actually in the same excel doc, the different features are in different tabs.
That makes it even easier. The FILTER function will work within workbooks as well. Have you watched the video?
I have watched the video. It does appear to be what I need, although a bit over my head. If you’re able to help, how was the master sheet created in the demo in the video and how would I be able to get that to work to pull in data from multiple tabs? Also, I don’t need the master to complete any calculations. I just need the rows to pull into the master and allow sorting. More rows will be added to the various tabs on a daily or weekly basis.
I have no idea how the master was created for the video; I doubt there was anything mysterious.

The real question is how YOUR data sets are created, how they're organized, whether they're designated as official Excel "Tables"... is it in any way possible for you to post either the actual workbook OR a mockup of the master and one or two of the subordinate sheets from which you want to extract data?