Jul 07 2022 09:50 AM
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?
Jul 07 2022 10:19 AM
Solution=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.
In sheet B the formula could be:
=IF('master tab'!$A1="B",'master tab'!A1,"")
Jul 07 2022 10:48 AM
@OliverScheurich 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.
Jul 07 2022 11:46 AM
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.
Jul 07 2022 12:49 PM
@OliverScheurich 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.
Jul 07 2022 01:33 PM
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,"")
Jul 07 2022 10:19 AM
Solution=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.
In sheet B the formula could be:
=IF('master tab'!$A1="B",'master tab'!A1,"")