Filtering data using a drop down menu

[HectorLPerez]

here is what my spreadsheet looks like

STATE PROGRAM FUNDING Budget
NY Mobile 311 $ 2,132
NY Mobile 311 $ 6,563
NJ Stationary 311 $ 8,125

Total $16,820

CT Mobile 425 $5,623
FL Stationary 425 $2,244
CA Stationary 425 $8,265
NY Mobile 425 $1,898

Total $18,030

NJ Mobile 366 $16,000
CA Stationary 366 $2,000

Total $18,000



I need to create drop down menus so that if I need to run a report on all
the programs in NY, it will automatically filter out all the rest. or if i
need to run a report on what type of program etc. For some reason, I cant
figure out the filter option. and the rows in between that show the totals
for each Funding Code is also throwing me off. I have seen another office
use this format with no problems but they didnt create the spreadsheet so
they cant help me.

 

[Don Guillett]

If you select the engire range and then autofilter on NY or mobile or both
& you can use subtotals at the top. DO look in the help index for SUBTOTALS
to see the difference between filtered/non filtered data. A dropdown could
be tied to a macro to automatically do this. I would use a defined name for
the range to account for additions/deletions.

 

[HectorLPerez]

This is a master sheet that has tons of more information than what I listed.
I would definitely need something that will still work as I add or delete
information, columns or rows. The drop down/macro idea seems like it would
work. How would I do that?

 

[Don Guillett]

You need to tie the dropdown to a worksheet_change event found in the sheet
module.

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[HectorLPerez]

The auto filter seems to do exactly what I am hooping to do but it doesnt
sort the whole spreadsheet. it gets cut off where the subtotal is.

 

[Don Guillett]

select the entire range