If you have no issue with the original data continuing to exist on the Master page, then this is very easy to accomplish. However, if you wish it to appear on the sub-sheets and be removed from the Master page, then you must use VBA. Excel is happy to read/copy/present information that exists almost anywhere pretty much in any other place you care to place it but its formulas do not take ANY actions on the materials. They simply look where told to for finding the material to use and give whatever result they were written for in the cell/s they are placed.
So, how? A function called FILTER()
:
=FILTER('Master sheet'!A2:I19,'Master sheet'!C2:C19="Infectious disease")
This use finds all the rows that have that Topic and present them on the sub-sheet. In this use, you specify the entire range so that you get all the columns of data. For some other use, you might specify a subset of the range. For more complicated needs (say, wanting columns 1,2,3,6,8, and 9 or wanting columns in some other order than on the Master sheet) one can wrap the collecting FILTER()
(above) with another and for the criteria give an array constant like {1,1,1,0,0,1,0,1,1}
or wrap it with INDEX()
instead and use an array constant to specify columns by number (even having them appear more than once).
As to sorting the results for use on the sub-sheets, one can wrap the formula with the SORT()
or SORTBY()
function.
A drawback, possibly (only you know), is that it is completely dynamic. If an entry is added, that's great, clearly. But entries could also be updated or deleted and there are lots of times either would bad for your intended use. They do not have to be bad, and might be strongly desired, but it's something to consider.
As a side note, since they were mentioned, the problem with using VLOOKUP()
, XLOOKUP()
, or INDEX/MATCH
is NOT the need to return the entire entry, but rather that they will only return ONE such entry, not all of them. For VLOOKUP()
, in the third parameter, you'd specify all the columns with an array constant ({1,2,3,4,5,6,7,8,9}
) or SEQUENCE(1,9)
. For XLOOKUP()
you just give the full range (and therefore the full list of columns to return) for its third parameter. For INDEX/MATCH
you get the row from the MATCH()
(or better XMATCH()
) and in the INDEX()
part, simply use ,
, ,0
, or the same array constant or SEQUENCE()
function from above. Sometimes, if specifying either rows or columns, the other value must be explicitly specified. But specifying a single row does not usually trigger that.
The reason you get only one row back with INDEX/MATCH
is because MATCH()
will not return an array for INDEX()
to use. If one had a way to do so, it would do the trick needed. But FILTER()
does it easily, and natively, so why bother?