Can't refer to a filtered range of cells

[shroeder]

I have a workbook with two sheets. One sheet is a labeled List with filters applied to two of the columns. In the other worksheet, I'm trying to create an IF function that matches any of the data in a particular cell with anything in one of the filtered columns in the other sheet.

I can get it to work when referring to just one cell in the other sheet, but I get error messages when I refer to the whole column of cells.

In other words, this works:

=IF(E3='Loop Library"!F44,"Fujimoto")

But this does not

=IF(E3='Loop Library'!F34:F49,"Fujimoto")

Keep in mind that F34:F49 is a filtered column

 

[Aladin Akyurek]

On 2002-09-26 22:13, shroeder wrote:
I have a workbook with two sheets. One sheet is a labeled List with filters applied to two of the columns. In the other worksheet, I'm trying to create an IF function that matches any of the data in a particular cell with anything in one of the filtered columns in the other sheet.

I can get it to work when referring to just one cell in the other sheet, but I get error messages when I refer to the whole column of cells.

In other words, this works:

=IF(E3='Loop Library"!F44,"Fujimoto")

But this does not

=IF(E3='Loop Library'!F34:F49,"Fujimoto")

Keep in mind that F34:F49 is a filtered column

The second formula is conceptually incorrect... A single cell (E3) cannot be equated to a range in a formula that computes with scalar values.

It looks like you want:

=IF(ISNUMBER(MATCH(E3,'Loop Library'!F:F)),"Fujimoto","")

This uses the unfiltered column as reference: Does it work as you intend? If not, what is the condition that you use when you filter the data?

 

[PaddyD]

see if this gives you the results you need:

=IF(ISNUMBER(MATCH(E3,'Loop Library'!F34:F49,0)),"Fujitmoto")
This message was edited by PaddyD on 2002-09-26 22:23

 

[shroeder]

I combined both your ideas and the winner is:

=IF(ISNUMBER(MATCH(E3,'Loop Library'!F$34:F$49,0)),"Fujimoto","")


You guys just ended three days of agony. If you ever need free music loops come see me at :

http://www.bbm.net

and I'll hook you up.

Thanks a lot!