Should be a simple task :-/

[ahab1981]

Hi, I have been asked to create a spreadsheet for colleague in spite of being a relative novice! The problem is...
I have two tables and I need for table1 to auto-populate according to table2's data. Table 2 is always the same except for one column which shows quantity. The idea will be that people can look over the long table2 and fill in what they need, this will create a shorter, more manageable table 1 (on another tab).

Unfortunately, just simply filtering table2 is not an option.

I'm sure there is an easy way to do this but having looked through forums and blogs, I can't find one. Any help really appreciated.

Cheers

Tom

 

[SpillerBD]

true. pivot tables are extremely powerful and would obtain this solution in a snap. one reason I sometimes look for the equation solution is that if the original data changes the pivot table needs to be manually refreshed or the input range changed. If I am making a spreadsheet for someone else in the organization they sometimes forget to do the refresh. Heck, I forget too. So sometimes the equations are a better way to automate the output. But not always. Pivot tables should certainly be considered as an option.

When setting up the Pivot consider making it refresh when the file is opened.
For others, adding an event procedure may be helpful.... http://www.mrexcel.com/forum/excel-...esh-pivot-tables-when-souce-data-changes.html

 

[ahab1981]

Thanks Spiller- I've done some work with pivot tables before, but as R surmised, it needs to automatically refresh or I think I could have just duplicated the table and applied a filter. Thanks for the advice though and when I have some spare time I'll look up the pivot table training that you signposted.

Cheers

Tom