0

I have a 'master sheet' with lots of furniture data in it, in column A there are the suppliers names. What I would like is to be able to have my master sheet with all the info and then sub sheets named by supplier; in these sub sheets I would like to reference the master sheet and pull out all of the items that are from that supplier.

For example: I would have a sheet called 'Ikea' which would look in the master sheet and search the A column for all entries of 'Ikea'. If present, copy or reference that row 1:12 in the 'ikea' sheet.

I would like to do it all dynamically using references rather than copying the data. Also, I would like it to auto update rather than having to run a macro to recalculate it each time. Can this be done with formulars rather than macros?

Improve this question
2
  • What version of Excel are you using? What have you tried so far?
    – CharlieRB
    Jun 11, 2012 at 17:03
  • its going to be on 2007, so far i havnt implemented anything my persoanl approach would be to do this using vba but it wouldn't be dynamicly brought in id have to run the macro each time
    – sam
    Jun 11, 2012 at 17:08

2 Answers 2

Reset to default
1

To extend CharlieRB's answer: set up your pivot table on a new tab. (In the screenshot below I've put the pivot table on the same tab just for clarity.) Be sure to put the "Supplier" field in the "Report Filter" section of the Pivot Table Field List form. I made the Pivot table source cover all of columns A:C on the Master sheet. This allows for automatic inclusion of additional rows upon refresh of the pivot table.

enter image description here

Next, with any cell of the pivot table selected, choose the "Options" menu. On the "PivotTable" submenu of the "Options" menu, click the "Options" dropdown:

enter image description here

Choose "Show Report Filter Pages", and select "Supplier". You'll get a tab for each supplier as you can see in the screen shot above.

Improve this answer
0

Rather than a complex number of formulas or code that has to be triggered, how about using a pivot table on a new tab. You can use it to view your data from the master table in a variety of ways and it can be filtered quickly. On top of that, it can easily be updated with new data from the master table.

If you need, here is a link to Get started with PivotTable reports in Excel 2007.

Improve this answer
1
  • CharlieRB is exactly right. And, once you have your pivot table, you can get individual sheets for each supplier by selecting the pivot table, then select the "Options" tab, then select "Options" under the "Pivot Table" submenu, and finally "Show Report Filter Pages".
    – F106dart
    Jun 11, 2012 at 18:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .