Filtering out zero values automaticly

Mkaloudis · Apr 26th, 2002, 07:30 AM

I have a continuously updating database that I need to constantly average and chart. I want to omit cells that contain zero values both in the average and in the chart. I've used the filter and autofilter command, which works fine until new data is added to the end of my data base. Whne the new data is added (automaticly from the internet) the filter I had in place becomes undone and all the zero values are shown again. I then have to re-filter the data. How can I set the workbook up so that the filter remains in place for all the data, even after the new data is added?

Thanks
Mkaloudis
Mkaloudis@ameritech.net

Mark W. · Apr 26th, 2002, 07:40 AM

As new data is added to your database (and, your database range is extended) you'll always have to reapply an existing AutoFilter in order for it to evalulate the additional record. Have you considered adding a column to your database (for charting purposes) that contained a formula like...

=IF(A1,A1,#N/A)

...where A1 is a value that you wish to chart?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 07:30 AM	#1
Mkaloudis New Member Join Date: Apr 2002 Posts: 2	I have a continuously updating database that I need to constantly average and chart. I want to omit cells that contain zero values both in the average and in the chart. I've used the filter and autofilter command, which works fine until new data is added to the end of my data base. Whne the new data is added (automaticly from the internet) the filter I had in place becomes undone and all the zero values are shown again. I then have to re-filter the data. How can I set the workbook up so that the filter remains in place for all the data, even after the new data is added? Thanks Mkaloudis Mkaloudis@ameritech.net

Apr 26th, 2002, 07:40 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	As new data is added to your database (and, your database range is extended) you'll always have to reapply an existing AutoFilter in order for it to evalulate the additional record. Have you considered adding a column to your database (for charting purposes) that contained a formula like... =IF(A1,A1,#N/A) ...where A1 is a value that you wish to chart?