Populate Excel Child Sheets From Master Sheet

Question

Hi,

I have an excel document with a master sheet, as well as several child sheets. My problem is that I need a way to copy content from the master sheet to a corresponding child sheet. Now, for any given row, the value in column B is what decides which child sheet the row in question is copied to.

For example, my columns are Composition, Sector, Portfolio, Client Name and Comments. I would want every row with an entry "CFI" in the "Sector" column to be copied over to the first available row in the "CFI" child sheet. I would want every row with an entry "HYFI" in the "Sector" column to be copied over to the first available row in the "HYFI" child sheet, etc etc.

Are there any ways of doing this with built in excel functions? Unless there are efficient macros to do this, I would prefer them as a last resort.

Finally, is there any way to do this "real time"? Where if a new entry was added to the master sheet, it would be automatically filed away into the corresponding child sheet as well.

Thanks!

Answer 1

There are a few options, none of them great IMO. The key to a good solution is that the data only live in one place. When it exists in more than one place, you'll spend more energy reconciling than your data is worth. Having said that, I would consider pivot tables. Put all your data on the master sheet, then put a pivot table on each of the child sheets that is filtered on Sector. Make the PT range dynamic so that it expands as you add data.

Or better yet, don't have child sheets at all. Just filter the data in place using an Autofilter.

Maybe the right question is "Why do you want them on different sheets?"

Answer 2

I've written a macro to do this, but it's embedded in an Addin. Also, my macro creates new sheets and doesn't append to existing sheets. If you still want it (but you say a macro is a last resort) then go to my website below, go to Stuff page and download CoolStuff addin.