Nov 24 2017
06:39 AM
- last edited on
Jul 25 2018
10:28 AM
by
TechCommunityAP
Nov 24 2017
06:39 AM
- last edited on
Jul 25 2018
10:28 AM
by
TechCommunityAP
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
Nov 24 2017 08:37 PM
Nov 25 2017 09:22 AM - edited Nov 25 2017 10:56 AM
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.
=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.
=<YourMasterSheet>!<the whole data reference address>e.g. in my excel, my DataList =MASTER!$A$1:$C$201
=<YourMasterSheet>!<the data reference address of your unique names column>e.g. in my excel, my CheckerColumnList = MASTER!$B$1$B$201
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()),"")
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 yyyy xxxxxx is simplied. This is just to hide those data with the name not matching the worksheet name.
Limitations:
Advantages:
PS. Correction of the reply in red color.