Forum Discussion
How to create a sub list from a master list
I've set up a master list of names and performance data on one sheet in a workbook, there are 16 unique names in the master list and 200 entries in the master list in total
I want to set up sub sheets within the workbook to display the same list layout as the master sheet list but each sub sheet only showing all the results for each of the unique names that appear in the master list
I need all the sub sheet lists to populate automatically once all the entries have been updated into the master list and I can't have any spaces appearing between the results that appear in the lists on the sub sheets
Thanks in advance
- Willy LauSteel ContributorIt can't be fully automatically. At least, you need to manually create a subsheet for each unique name.
- Willy LauSteel Contributor
I am sorry for the last reply without anyway for you to get a try.
I tried to do the following things. Honestly, I am also not familar with Excel formula. However, what I have done seem help. I don't attach the excel, but show you the steps.
- Create a name formula, named "SheetName":
=MID(CELL("filename",INDIRECT(ADDRESS(1,1))),FIND("]",CELL("filename",INDIRECT(ADDRESS(1,1))))+1,255)
This formula I found in this source: https://exceljet.net/formula/get-sheet-name-only.
However, when you create name, Excel by default add the worksheet name in the reference. I do not want to create a lot of name, so I modified the formula. "A1" to "INDIRECT(ADDRESS(1,1))", which dynamically refer to the current worksheet. - Create a name formula, named "DataList":
=<YourMasterSheet>!<the whole data reference address>
e.g. in my excel, my DataList =MASTER!$A$1:$C$201 - Create a name formula, named "CheckerColumnList":
=<YourMasterSheet>!<the data reference address of your unique names column>
e.g. in my excel, my CheckerColumnList = MASTER!$B$1$B$201 - As you said, you want those sub sheets have the same list layout. Create 1 sub sheets manually, use one of the unique names as the worksheet name.
e.g. in my excel, I just name those worksheets from A to P. So, at this moment, I create a sub sheet, and named it as "A" - In my sub sheet, at the checker column (name column in your worksheet), I enter something, e.g. "1", from the first row to the 201th row (size as same as the master file). Hence, in my case, A!$B$2:$B$201 will be all "1"
- Now, forcus back to the first row of the checker column, e.g. A!$B$2.
a) I enter the following formula*:
b) After input the formula, to confirm it, I press [Ctrl]+[Shift]+[Enter]. This is to indicate that I use array formula.=IF(LARGE(--(CheckerColumnList=SheetName)*ROW(CheckerColumnList),ROW()-1)=0,"",INDEX(DataList,LARGE(--(CheckerColumnList=SheetName)*ROW(CheckerColumnList),ROW()-1),COLUMN()))=IFERROR(INDEX(DataList,SMALL(IF((--(CheckerColumnList=SheetName)*ROW(CheckerColumnList))=0,"X",(--(CheckerColumnList=SheetName)*ROW(CheckerColumnList))),ROW()-1),COLUMN()),"")
c) Use the Fill-Handle to fill the formula to all cell of your checker column (name column in your worksheet), e.g. in my excel, A!$B$2:$B$201 are all filled with the formula in Step 6a.
d) Select the whole checker column, and use the Fill-Handle to fill the formula to all column. e.g. in my excel, highlight A!$B$2:$B$201, and use the Fill-Handle to fill to $A$2:$A$201 and $C$2:$C$201
e) it should be the result you want for a unique name - Copy the sub sheet to create another sub sheet, and using the unique name for those new created worksheet.
Note:
* the formula is doing:--(CheckerColumnList=SheetName)
It uses the checker column content to compare with the sheet name. If the sheet name match, it will be 1. Otherwise, 0 (zero).
*ROW(CheckerColumnList)
As "--(CheckerColumnList=SheetName)" generate an array with all matching row to 1. Then, multiply by the row number. It will be the row number exactly. The non-matching row will be 0 (zero)
LARGE(--(CheckerColumnList=SheetName)*ROW(CheckerColumnList),ROW()-1)SMALL(IF((--(CheckerColumnList=SheetName)*ROW(CheckerColumnList))=0,"X",(--(CheckerColumnList=SheetName)*ROW(CheckerColumnList))),ROW()-1)I cannot know any formula to find the 1st row that its value is bigger than 0(zero). Hence, I use LARGE formula to find those row. Therefore, the non-matching row will be at the bottom.If the row is equal to 0 (zero), this part of formula will replace it to "X", which will make error to the formula. SMALL formula will ignore them. As my data has column header row (the 1st row only), I use "ROW()-1" for the LARGE formula to find the Nth largest value.INDEX(DataList,LARGE(--(CheckerColumnList=SheetName)*ROW(CheckerColumnList),ROW()-1),COLUMN())
INDEX function helps to extract the data from the master sheet. "COLUMN()" will dynamically help to extract coresponding column data from the DataList.
IF(xxxx=0,"",yyyy)IFERROR(xxxxxx,"")xxxx and yyyyxxxxxx is simplied. This is just to hide those data with the name not matching the worksheet name.Limitations:
Data come out in descending order from your master sheet. This is a big limitation and this may make this solution approach not fit to your situation. Finally, it will not be a solution.- Difficult to changes the formula later on, as the sub-sheet data is treated as one array list?(I am not familar with this).
- Worksheet name is required to be named as same as those 16 unique names.
Advantages:
- No helper columns
- No extra cell for dynamic matching the name (we do this by Name formula)
PS. Correction of the reply in red color.
- Create a name formula, named "SheetName":