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

shroeder

New Member
Joined
Aug 20, 2002
Messages
17
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
'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?
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,223,480
Messages
6,172,510
Members
452,463
Latest member
Debz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top