Pulling information from master sheet to other sheets if it fits in category

[SirHart]

Hello everyone,

Thank you ahead of time for your help on this. Managing this is beyond me and I appreciate the expert advice!

I have a 2003 excel spreadsheet that has a master sheet, and multiple "category" sheet, labeled "category 1", "category 2", etc. The master sheet gets updated frequently and I put information in the row. The category is included in the row along with the category in the "D" column.

Is there any formula that I can use in the "category" sheets that can pull the information in the whole row and place it into the category sheet everytime I update the Master sheet?

**I would like to be to keep a static row on top for the headers, if possible but not necessary.

I only have 4 categories so it would be no problem to do each sheet separate if needed.

Any help would be greatly appreciated! At a loss here.

 

[RonB1111]

Are you aware of auto filters? You'd only use the master sheet and whenever you wanted to look at just 1 category, you'd click the filter for that category and only rows in that category would be displayed. You can keep the header row always displayed as you scroll by "Freezing Panes".

Auto filter link: http://www.contextures.com/xlautofilter01.html

Freeze panes link: http://inpics.net/tutorials/excel2007/vis5.html

 

[SirHart]

Sadly auto filters won't work for this. The problem is more advanced. I need these separate sheets of each category to use another set of formulas to pull information.

Any idea on how to pull the information by category to its own sheet? Any help would be amazing!

 

[RonB1111]

Formula based solution:
See Aladin Akyurek's formula solution here: http://www.mrexcel.com/forum/showthread.php?t=621050

or,

Microsoft Query solution:
<table border="0" cellpadding="0" cellspacing="0" width="1156"><colgroup><col style="mso-width-source:userset;mso-width-alt:42276; width:867pt" width="1156"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:867pt" height="17" width="1156">1. Assign a range name to your data, then save the file, then go to a blank worksheet within that same workbook and click on a cell in col A that is below the form data.</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">2. (for Excel 2007+) click "Data - From Other Sources - From Microsoft Query"</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">(for Prior versions) click "Data-Get External Data - New Database Query"</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">3. Make sure "Use query wizard" box is not checked (at the bottom of the pop up window), then double-click "Excel Files" (or single click Excel Files then click OK)</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">4. Select your workbook and then click OK</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">5. The range name you assigned in 1 above will appear so double click it and close the small window</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">6. Click the "*" (1st item in the little box show all field names of your table) and all fields will be displayed, Alternatively, to selected only certain fields, double click the field you want.</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">7. Click the icon for "Show/Hide Criteria" and a criteria pane section will appear</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">8. Drag the "Category" field to the 1st box to the right of "Criteria field"</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">9. Enter in the Value box below the Category name or number you want to bring rows forward to excel</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">10. Click "File-Return Data to Microsoft Excel" </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:867pt" height="17" width="1156">11. You can set the query properties to refresh the data either every time the workbook is opened or every "x" minutes. You can also manually refresh anytime by right-clicking refresh.</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">12. Repeat this process in a new worksheet for the other 3 categories</td> </tr> </tbody></table>

 

[SirHart]

Thank you very much Ron. I was having problems finding the correct article before. Works great! I had been tearing my hair out on this one awhile!

Very much appreciated!