i would like to use an IF formula to query two ranges of cells (on separate
worksheets in the same workbook) but don't know how to enter more than one
range in the formula. please can you help

The details would be specific to your particulars, but here's an
example.........

=IF(AND(SUM(Sheet2!B:B)=4,SUM(Sheet3!C:C)=9),"Got it","Didn't get there")

hth
Vaya con Dios,
Chuck, CABGx3



"Mark" wrote:

i would like to use an IF formula to query two ranges of cells (on separate
worksheets in the same workbook) but don't know how to enter more than one
range in the formula. please can you help

I have a similar request. The answers suggested here are almost what I need.

I need info in a cell on one sheet to forward to another sheet, IF the value
is over a certain control number. BUT I don't want the number carried over
if the value is less than that number. EX:

Sheet 1, H10 is 103. My control number is 100. So I want this entire row
(row 10) to carry over to sheet 2. (A summary of all rows where column H10
exceeded 100%.

Sheet 1, H11 is 98. So i don't want anything carrying over from this row.

I've tried many "IF" combos. But they all require the "false" part of the
equation.

So I end up with a blank entry on sheet 2 for rows that didn't exceed the
control.

I guess what I want to do is . . .

IF("sheet1'!H10100, (if val in sheet 1 in cell H10 is over 100) . . .

then copy row 10 of sheet 1 over to next available row in sheet 2.

BUT . . . if it's <100, do nothing.

Then, go to next row on sheet 1.

Problem is, I need the 2nd part of the formula to "do nothing".

I tried another approach by writing a macro that went thru sheet two, and
deleted all the blank rows, after the data was all filled in in sheet 1. BUT
.. . .

Then I had what I wanted, until I needed to edit sheet 1. If I edit H? in
sheet 1, well. See my problem?

Also, I had to put the formula in each cell, each colum on sheet 2 to get
the data from sheet 1 over. As in . . .

Column 1, Row 10, formula to pull over matching cell data from sheet 1 IF .
.. .
Ditto on all other cells.

So . . . Can I write an IF formula that doesn't require the "then", or the
"or"?

And can I have an entire row (or column for that matter) carry over if a
parameter is met in one of the cells of that row?

WOW! I hope that makes sense!

Have you considered Data Filter AutoFilter Custom (on your column of
interest) GreaterThan 100



Vaya con Dios,
Chuck, CABGx3



"Wayne Knazek" wrote:

I have a similar request. The answers suggested here are almost what I need.

I need info in a cell on one sheet to forward to another sheet, IF the value
is over a certain control number. BUT I don't want the number carried over
if the value is less than that number. EX:

Sheet 1, H10 is 103. My control number is 100. So I want this entire row
(row 10) to carry over to sheet 2. (A summary of all rows where column H10
exceeded 100%.

Sheet 1, H11 is 98. So i don't want anything carrying over from this row.

I've tried many "IF" combos. But they all require the "false" part of the
equation.

So I end up with a blank entry on sheet 2 for rows that didn't exceed the
control.

I guess what I want to do is . . .

IF("sheet1'!H10100, (if val in sheet 1 in cell H10 is over 100) . . .

then copy row 10 of sheet 1 over to next available row in sheet 2.

BUT . . . if it's <100, do nothing.

Then, go to next row on sheet 1.

Problem is, I need the 2nd part of the formula to "do nothing".

I tried another approach by writing a macro that went thru sheet two, and
deleted all the blank rows, after the data was all filled in in sheet 1. BUT
. . .

Then I had what I wanted, until I needed to edit sheet 1. If I edit H? in
sheet 1, well. See my problem?

Also, I had to put the formula in each cell, each colum on sheet 2 to get
the data from sheet 1 over. As in . . .

Column 1, Row 10, formula to pull over matching cell data from sheet 1 IF .
. .
Ditto on all other cells.

So . . . Can I write an IF formula that doesn't require the "then", or the
"or"?

And can I have an entire row (or column for that matter) carry over if a
parameter is met in one of the cells of that row?

WOW! I hope that makes sense!

Thanks, Chuck. This is all a bit new to me. I'll check out and try your
suggestion. :)

"CLR" wrote:

Have you considered Data Filter AutoFilter Custom (on your column of
interest) GreaterThan 100



Vaya con Dios,
Chuck, CABGx3

You're welcome Wayne.......the AutoFilter should do what you want, giving all
the rows exceeding a certain value in a certain column.......

When finished, just do Data Filter AutoFilter again and it toggles off,
returning all data to normal........

And if it's something you have to do frequently, it can be automated with
macros..

Vaya con Dios,
Chuck, CABGx3





"Wayne Knazek" wrote:

Thanks, Chuck. This is all a bit new to me. I'll check out and try your
suggestion. :)

"CLR" wrote:

Have you considered Data Filter AutoFilter Custom (on your column of
interest) GreaterThan 100



Vaya con Dios,
Chuck, CABGx3

You need to be a bit more explicit on what you want, but maybe something
like

=IF(OR(ISNUMBER(MATCH(A1,H1:H100,0)),ISNUMBER(MATC H(A1,M1:M100,0))),"matched
","not matched")

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"Mark" wrote in message
...
i would like to use an IF formula to query two ranges of cells (on
separate
worksheets in the same workbook) but don't know how to enter more than one
range in the formula. please can you help