ignoring hidden cells for functions

[JohnJayJones]

http://i.imgur.com/8n9Z3PU.png

Ok so here is a small section of a large set of items that I wish to manipulate. What I'm looking to do here is to find the first occurrence of an Item Name's DATE REQUIRED to be displayed where the Have Received is FALSE. I figured it would make it easier by sorting the data to exclude any items that have been received (Have Received = TRUE), but my formulas still work on these hidden rows. Is there a work around for something like this? I could copy a subset of the table and do it that way, but the original table is meant to be expansive so lines can be added in and I would always need to re-copy if I did that approach.

Sorry if this is cryptic, but any help would be appreciated!

Thanks

[JasperD]

Short answer: Depends on the formulas.

But if you can replace your formula with =Subtotal, then using for example 109 instead of 9 would ignore the hidden rows.

Less cryptic:

=SUBTOTAL(9,F5:F14)
would sum all numbers between F5 and F14, regardless of they're hidden or not.

=SUBTOTAL(109,F5:F14)
would sum only the visible numbers between F5 and F14.

Otherwise you'll have to rely on a UDF, I think.

*edit*
If you're summing, use =SUMIFS instead to include the "hasreceived" check.

[shg]

Subtotal(9, ...) excludes rows hidden via filter.

Subtotal(109, ...) excludes rows hidden manually or via filter.

[JohnJayJones]

Appreciate the responses, but how would I use subtotal to do what I want to do? I don't believe it can extract what I'm looking for alone.
I have a user made function that performs the search, but it will include all hidden rows as well and it's interfering with everything because t
here must be duplicates in the table and I only need those rows which have not been received for a given item

[JasperD]

You do realize that in order to help you, you need to paste your udf code here, right?

You originally asked about 'functions' in general.
Some of the functions that are used a lot can be replaced by subtotal, so that's why that was the first step in trying to help you.

Please be clear, concise and paste your function here, so someone can help you add 'specialcells(xlcelltypevisible)' in the right location ;-)

[JohnJayJones]

My apologies! Here is what I am using to get what I need, but does not ignore any hidden rows

Please Login or Register  to view this content.

[JohnJayJones]

Any ideas how to go about this? I need the same functionality of looking up this way, but those hidden cells have got to be excluded

[TnTinMN]

Have you considered using the Range.Find Method to find your match?

If you set the LookIn argument to Excel.XlFindLookIn.xlValues, it will not look at hidden cells.

[JohnJayJones]

TnTinMN, I just looked into this and I have a solution for my needs by using it. I feel the way I approached it is pretty sloppy, BUT it works flawlessly!

Thanks for the help!

[TnTinMN]

TnTinMN, I just looked into this and I have a solution for my needs by using it. I feel the way I approached it is pretty sloppy, BUT it works flawlessly!

Thanks for the help!

Great! I'm glad you got a solution that works.

Who cares if its sloppy, you did it yourself and learned something process and that's what counts.

You can always ask for suggestions on how it could be improved.