Pope Flick Posted July 14, 2006 Share Posted July 14, 2006 So I'm working on a big datebase that is tracking identical elements over 33 sheets. let's say each row contains information for a TV show. Each sheet is for each seperate season of said show. Columns are are tracking identical information for each show over all the sheets. Not every show is entered, as the information we're getting is growing. What I need is a 34th sheet, that pulls the information from each and every row were a certain column has text in it. In other words, if column in one of the "season sheets" named "GM" has "GM" in it, the entire episodes infor gets placed on that 34th sheet. If it is removed from it season sheet, it automatically drops off this new 34th sheet, and vice versa. I can change this column to any value, etc the "GM' isn't as important as it being somethign this workbook will handle like that. In short: if one of these episodes is tagged "GM' it gets listed on a master sheet that lists all episodes over the course of 32 years. A cross reference. YES - I know there are MUCH better programs to do this with, but I'm stuck with excel. I understand formulas pretty well, but am not sure how to write it. Thanks in advance, you can post or pm me - if it's even possible, which my boss says it is. Quote Link to comment Share on other sites More sharing options...
tonorator Posted July 14, 2006 Share Posted July 14, 2006 are you tied to having each season on a separate sheet? if you had all of the seasons on one worksheet, you could use the autofilter command to look at all of the rows by season, by show name, etc. you could filter all the shows anyway you like to quickly find what you are looking for. you can also autofilter on multiple fields. another way to approach it would be to set up a separate worksheet where all of the shows in each individual worksheet show up in one big list ... i.e. if you add a show to any worksheet, it is automatically added to the single worksheet. then you could autofilter on the single worksheet. you could do this manually, by copying all of the individual rows into the one sheet and then using autofilter. i'll look and see if there is a way to update a master sheet that would have all rows that are in each individual sheet ... Quote Link to comment Share on other sites More sharing options...
Cyclones Posted July 14, 2006 Share Posted July 14, 2006 So I'm working on a big datebase that is tracking identical elements over 33 sheets. let's say each row contains information for a TV show. Each sheet is for each seperate season of said show. Columns are are tracking identical information for each show over all the sheets. Not every show is entered, as the information we're getting is growing. What I need is a 34th sheet, that pulls the information from each and every row were a certain column has text in it. In other words, if column in one of the "season sheets" named "GM" has "GM" in it, the entire episodes infor gets placed on that 34th sheet. If it is removed from it season sheet, it automatically drops off this new 34th sheet, and vice versa. I can change this column to any value, etc the "GM' isn't as important as it being somethign this workbook will handle like that. In short: if one of these episodes is tagged "GM' it gets listed on a master sheet that lists all episodes over the course of 32 years. A cross reference. YES - I know there are MUCH better programs to do this with, but I'm stuck with excel. I understand formulas pretty well, but am not sure how to write it. Thanks in advance, you can post or pm me - if it's even possible, which my boss says it is. Download ASAP Utilities, that will allow you to do some of the things you are referring to. ITs free. Quote Link to comment Share on other sites More sharing options...
tonorator Posted July 14, 2006 Share Posted July 14, 2006 (edited) check out this link. the VBA code here will take all of the sheets in your excel file and put all the rows of data into a single sheet that you have to create, called Consolidate. this will automatically take all rows of data in every sheet and put them all into one sheet. once you have them all in one sheet, just use the autofilter command to only view what you would like to see. you will need to clear out the Consolidate sheet each time before you run the macro code, or else you will have all of the shows listed once for each time you run the macro (they keep appending on to the bottom). i tried this on a sample file i created and it works great. (edited to add: you could write a macro that also clears the sheet out for you each time, and then run this macro before consolidating, or just combine it with the consolidation macro to do it in one step) give it a shot and let me know if it helps or if you have any questions. hopefully it will get you what you are looking for. Edited July 14, 2006 by tonorator Quote Link to comment Share on other sites More sharing options...
tonorator Posted July 14, 2006 Share Posted July 14, 2006 i didn't mention that the append macro adds all the rows to the new Consolidate sheet, starting at row 2. this is cool because you can keep your headers on the Consolidate sheet all the time ... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.