I have a simple Excel spreadsheet (attachment called Book1.xls). As you can see, I have 2 sheets in the workbook. Sheet 1 contains a list of items together with a quantity for each item. Sheet 2 contains an Auto-Filtered display of the list on Sheet 1. What I need to do is set the list of results on Sheet 2 to show only the items on Sheet 1 that have got a quantity value assigned to them. The way I have done this is to use a simple IF statement in the cells on Sheet 2 that shows the values from Sheet 1 for each item that has a quantity, and shows a blank for all items that do not have a quantity value. I have got that formula working well.
The problem I am having is that when I use the auto-filter to filter to show "NonBlanks" only, it doesn't refresh itself automatically - ie I have to manually select NonBlanks from the filter drop down on Sheet 2 to get it to update itself. I would like this to happen automatically, so that if/when for example I add a quantity for an item on Sheet 1 that previously had no quantity value, when I go to Sheet 2, the auto-filtered list will automatically be updated, without me having to click NonBlanks on the dropdown box every time.
How can I do this?
(I have allocated 500 Points to this question)