formula using multiple ranges

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

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(MATCH(A1,M1:M100,0))),"matched
","not matched")

--
HTH

Bob Phillips

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

"Mark" <Mark@discussions.microsoft.com> wrote in message
news:698DDD8D-42E3-42BD-AFA1-DD1E6C1A01F7@microsoft.com...
> 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'!H10>100, (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'!H10>100, (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

Hey Chuck!

Yeah, it worked great! What I'll do as I get a little more familiar with
Excel is . . . (hopefully) create a button that will allow users to toggle
the filter on and off, so they can make edits on "main" page, and they will
update 2nd & 3rd sheets, etc.

FYI: I find this to be one of the best forums I've ever been involved with.
Very active!

I've been a major contributor to many forums in my area of expertise. I'm a
Quality Engineer. Just never had to work extensively with Excel.

Syntax is a little strange at times. But once I grasp the logic, I hope to
be able to contribute here as well.

Thanks again.

Oh, any suggestions on a goods source of study re: writing formulae in
Excel? (other than the VB macros in the editor. That's a different story all
together! LOL)

Glad you got it working Wayne, and thanks for the feedback. I agree on the
value of these newsgroups and suggest that further reading here will do
wonders to improve anyone's Excel skills. Read the OP's problem, then the
various solutions offered. Eventually try to come up with your own solution
before reading the responses....good exercize. Search to look up areas of
specific interest. I have a hunch we'll be seeing more of you.

Vaya con Dios,
Chuck, CABGx3





"Wayne Knazek" wrote:

> Hey Chuck!
>
> Yeah, it worked great! What I'll do as I get a little more familiar with
> Excel is . . . (hopefully) create a button that will allow users to toggle
> the filter on and off, so they can make edits on "main" page, and they will
> update 2nd & 3rd sheets, etc.
>
> FYI: I find this to be one of the best forums I've ever been involved with.
> Very active!
>
> I've been a major contributor to many forums in my area of expertise. I'm a
> Quality Engineer. Just never had to work extensively with Excel.
>
> Syntax is a little strange at times. But once I grasp the logic, I hope to
> be able to contribute here as well.
>
> Thanks again.
>
> Oh, any suggestions on a goods source of study re: writing formulae in
> Excel? (other than the VB macros in the editor. That's a different story all
> together! LOL)