• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hitlist from visible cells only

C

CVDK

New Member
Hi all,

I have a hitlist where I've used small(range,n) to create a hitlist of the 10 largest drop in revenue on our customers. The story could have ended right there, only now someone asked if I could make the hitlist a bit more dynamic and combine it with autofilter and show the hitlist for the selected/visible lines only.


The thing is the list has a number of account managers each being responsible for a number of customers. They would like the hitlist to be dynamic in the sense that when using filter to see the customers of only one key account the hitlist should reflect the filtering and show a hitlist of only the visible cells.


I thought using subtotals could somehow do the trick, but so far I've not been succesfull. Also I've tried googling to see if anyone had tried the same thing but nothing useful has come up.


Anyone?
 
CVDK,

Welcome to this Chandoo's Forum. Actually your problem is not clear to me. Please upload a sample file and define what kind of result you want to get.


Regards,


Muneer
 
Hi, CVDK!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
You'll want to add a helper column to your main data which will be able to detect filtered data. Let's say that your current col containing values is col B. This formula will make it so only visible rows display data:

=IF(SUBTOTAL(103,B2),B2,"")


Then, adjust the SMALL formula you're already using to look at your helper column. Task complete! Filter/hide the data as much as you want and you'll only get the "top 10" for data that is visible. =)
 
Thank you Luke, I'll check it out. I think I've already tried out something like that but I better do it again.


Right now I'm working on filling arrays in VBA so this feature has to wait a couple of days.


@SIRJB7 Thank you for your response. However, this is my first post, though I've been enjoying Chandoo's tips etc. for a while. I've always found what I was searching for somewhere on the net, at Chandoo or elsewhere. As you can se I've been googling (for a while) in order to find a solution without any luck. I'm not posting for fun, even though it may seem so to you.
 
Hi,

You may also want to check out the following:

http://chandoo.org/wp/2010/05/11/exclude-hidden-rows-from-totals/


Cheers,

Sajan.
 
Hi,

You may also want to search for the following on this site:


exclude-hidden-rows-from-totals


(I tried several times to post the link but the spam filters are working extra hard today to catch good and bad posts equally!)


Cheers,

Sajan.
 
@Sajan

I've un-spammed one of your posts. I wish there was a way to "whitelist" users. =/
 
Thanks Luke!


(By the way, today I got the message that I was going too fast for the site!! Actually, I was copying/pasting the text in the hopes that I could catch the spam filters "off guard"!! :p )


Thanks again!


-Sajan.
 
@Luke M

Hi!

I only knew that there is a blacklist with one user. You, of course.

Regards!


@Sajan

Hi!

That message is displayed when trying to post within the next 30 seconds from last post. Usually happens when copying and pasting, or adding something missing in previous comment, or when someone answers Luke M.

Tried this?

Post anything, edit, then paste what rejected previously. If it works don't tell anyone.

Regards!
 
You must log in or register to reply here.
Back
Top