got me stumped

[nmc7714]

Hi,

i have a slight problem....
i need a spreadsheet that hides rows depending on selections from drop down menus... i am quite new to using excel this way, and so far have only managed to solve a part of this problem.

i have several headings,
myob, name,photographer, viewer, spend....and a few others that dont really matter for this.

i need to be able to filter the rows to show a particular photographer's customer... hiding all other photographers... this i have managed to do, sort of... but i also need to be able to filter these results down by viewer too, so i can analyze different result combinations.... a solution would be wonderful...because i'ts starting to annoy me slightly lol.
thanks in advance

 

[Andrew Poulsom]

Welcome to the Board.

Have you tried Data|Filter|AutoFilter?

 

[nmc7714]

i have looked at these briefly, but from the results, i need to make calculations... and when i tried to calculate ranges, it still included the hiden rows... not sure if i did something wrong...

eg..
filter photographer to nm
and viewer to nm
then in a cell show the total amount of sales from this combination from the spend column...
when i did it, it still calculated everything

 

[Andrew Poulsom]

Have a look at the SUBTOTAL function in Excel Help. It will ignore rows that are hidden as a result of a filter.

You may also want to consider a pivot table:

http://peltiertech.com/Excel/Pivots/pivottables.htm

 

[nmc7714]

thanks,
i'll have a look when i get chance... if it removes the need for the scripting or whatever it is, it sounds good.

 

[nmc7714]

that did the trick, it works enough to get the job done. Thanks a lot.