Populate tabs from a master sheet

New Contributor

I am looking for a way to populate tabs from one master tab. I do not have anything set up currently so I don't have specific names for these tabs so just assume its A-Z. In my case I have basic Excel and thats it. I am trying to take a list of A-Z (these will be changed once I receive a list of names) and have say 30 cells from top to bottom. I need however many A's in the A column to auto populate into another tab for all the A's and all the B's in the A column to populate into the B tab. I also need all the information in the rows of the A cell to be auto populated into the tab with all the A's. I have set up a small example. Any ideas?




5 Replies
best response confirmed by Jacob_Herrmann (New Contributor)


=IF('master tab'!$A1="A",'master tab'!A1,"")

You can try a simple formula like this which i entered in sheet A in cell A1 and copied across range A1:E20.

master sheet.JPG

sheet A data from master tab.JPG

In sheet B the formula could be:

=IF('master tab'!$A1="B",'master tab'!A1,"")


@Quadruple_Pawn  Sweet that does the bulk of it I believe, but I have a couple more questions. 1. Can I get this to auto populate or do I have to just drag the formula down the column and rows every time to update it? 2. Is there a way to get rid of the empty cells between instances (see attached) if not its not a big deal they can just hide or resize the empty ones. Its likely that I wont be managing this once its set up so I want it to be as easy to use as possible. Thank you for the help. 

Capture 3.JPG


You only have to drag the formula across the rows and columns once (for a certain range). For example if you drag the formula across range A1:F1000 it auto updates according to the entries in the master tab in this range. Only if the size of the master tab exceeds this range you have to drag the formulas across the new range.


With the suggested formula you can hide, resize or delete the empty rows. An alternative could be a macro that pulls the data from the master sheet without empty rows. If you work with Office365 or 2021 you can apply the FILTER function which auto populates the sheets without empty rows as well.

@Quadruple_Pawn Alright I got the filer thing to work on my sample sheet. Now I have what is essentially the final Excel sheet but still don't have any drawing numbers so I just put in random numbers from 1-10. For some reason when I apply the same method to this sheet it doesn't seem to work. I changed the A1 to B10 and the A to 1 for the first tab. When I drag the equation down it does not auto populate. Not sure why.




master sheet.JPG

It should work if you replace A2 by B10 and "1" by 1. I've tried the below formula and it works in my sheet.

=IF('master tab'!$B10=1,'master tab'!B10,"")

sheet 1.JPG

master table.JPG