Do NOT use this post see: shroeder wrote: Thought I had thi

shroeder

New Member
This formula works fine:

IF(ISNUMBER(MATCH(E2,'Loop Library'F\$34:F\$49,0)),"Fujimoto"," ")

Becuase it's only looking at three cells within the F34:F49 range that are the result of a filter.

But now when I re-filter those columns in 'Loop Library' doc, with new criteria, the corresponding formulas don't see the filter, they just see the entire range. Here are two formulas that have that problem and I can't figure out what's different from the one that works:

IF(ISNUMBER(MATCH(E2,'Loop Library'F\$120:F\$140,0)),"Chip Martin"," ")

IF(ISNUMBER(MATCH(E2,'Loop Library'F\$6:F\$103,0)),"Mike Bielenberg"," ")
This message was edited by Ian Mac on 2002-10-03 06:02

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sorry, forgot the "!" after 'Loop Library'

I'm getting foggy.

'Loop Library' is a worksheet comprised of about 45 columns, three of which are relevent to the problem at hand. In two of those columns are text values, "<title of music piece>", and "<composer name>". The third column is SUM of how many units were sold.

First, I filter out all "0" values in the SUM column so I only see pieces I owe royatlies for.

Then I sort the "<composer name>" column so I only see rows that contain a particular composer's name.

I want the formulas in the other worksheet to then check only the cells that are visible in the "<title of music piece>" column.

Make sense?

That was weird. Anyway...

'Loop Library' is a worksheet comprised of about 45 columns, three of which are relevent to the problem at hand.Two of those columns are text values: "title of music piece" and "composer name". The third column is a SUM of how many units sold.

First, I filter out all "0"'s in the SUM column so I only see music pieces I owe royalties for

Second, I sort the "composer name" column so I only see sales made by one individual.

The formulas in the other worksheet then refer to cells that remain visible in the "title of music piece" column.

Does that help?

On 2002-10-03 03:40, shroeder wrote:
This formula works fine:

IF(ISNUMBER(MATCH(E2,'Loop Library'F\$34:F\$49,0)),"Fujimoto"," ")

Becuase it's only looking at three cells within the F34:F49 range that are the result of a filter.

But now when I re-filter those columns in 'Loop Library' doc, with new criteria, the corresponding formulas don't see the filter, they just see the entire range. Here are two formulas that have that problem and I can't figure out what's different from the one that works:

IF(ISNUMBER(MATCH(E2,'Loop Library'F\$120:F\$140,0)),"Chip Martin"," ")

IF(ISNUMBER(MATCH(E2,'Loop Library'F\$6:F\$103,0)),"Mike Bielenberg"," ")

You really need to include filter criteria in the computation like the one you're attempting...

By the way, what is in E2 when Fujimoto is the result?

Do not use " " (space) when you mean "" (blank).

Replies
1
Views
714
Replies
0
Views
1K
Replies
0
Views
776
Replies
1
Views
945

1,203,317
Messages
6,054,711
Members
444,742
Latest member
jmartin9247

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back