Dynamic of Excel data

[David Beahan]

A table lists all products with sales for a period. I wish to filter that
list to only show the products that have a sales value. i.e. not products
with no sales. Easy with a filter but... I want the resultant table (the
filtered one) to update when a change is made in the primary table. I don't
want to have to complete the data in the primary table and then filter the
data to another location. Can this be done?

 

[Ms-Exl-Learner]

Try Pivot Table…

If this post helps, Click Yes!

 

[Ashish Mathur]

Hi,

Try this:

1. Select the range (including the header row) and assign it a name (Insert

Name > Define), say dummy1

After assigning a name, select the range again (including the header
row) and convert it to a List (Ctrl+L)
2. Click on any blank cell and save the file on the desktop (try.xls)
3. Now go to Data > Import external Data > New database query
4. Select Excel files and click on Next
5. Select desktop in the right hand side window
6. Select try.xls and click on OK
7. Now select the Sales value column and give the filter criteria as Greater
than and 0
8. Click on Next and Next
9. Select Return data to MS Excel
10. Now if any change takes place (new rows added or records edited), just
right click on any cell in the output range and select Refresh

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com