I have two worksheets in one workbook. Both contain tables, one condenses
data from the other. I am needing to pull specific data from a column as
result of data in two other columns with the end result in the second
worksheet. Normally I would use the VLookup; however I have two ranges and
this is not possible. So far this is the formula I have come up with, yet I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), I1:I65536

PLEASE HELP!

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one condenses
data from the other. I am needing to pull specific data from a column as
result of data in two other columns with the end result in the second
worksheet. Normally I would use the VLookup; however I have two ranges and
this is not possible. So far this is the formula I have come up with, yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), I1:I65536

PLEASE HELP!

If the F7 = 1 and R7 = QL you want I1 to appear in the cell your formula is in?

=IF(AND(F7=1,R7="QL"),I1,"")

You would then copy the formula down if what I said in the 1st line is true

"chelles" wrote:

I have two worksheets in one workbook. Both contain tables, one condenses
data from the other. I am needing to pull specific data from a column as
result of data in two other columns with the end result in the second
worksheet. Normally I would use the VLookup; however I have two ranges and
this is not possible. So far this is the formula I have come up with, yet I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), I1:I65536

PLEASE HELP!

This didnt work. I am not sure how to expalin what I need. I am fairly new to
as advanced of work in excel as this. I also noticed that what I had earlier
was written wrong, but either way neither formulas worked. I adjusted the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one condenses
data from the other. I am needing to pull specific data from a column as
result of data in two other columns with the end result in the second
worksheet. Normally I would use the VLookup; however I have two ranges and
this is not possible. So far this is the formula I have come up with, yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), I1:I65536

PLEASE HELP!

This didnt work. I am not sure how to expalin what I need. I am fairly new to
as advanced of work in excel as this. I also noticed that what I had earlier
was written wrong, but either way neither formulas worked. I adjusted the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.



"tim m" wrote:

If the F7 = 1 and R7 = QL you want I1 to appear in the cell your formula is in?

=IF(AND(F7=1,R7="QL"),I1,"")

You would then copy the formula down if what I said in the 1st line is true

"chelles" wrote:

I have two worksheets in one workbook. Both contain tables, one condenses
data from the other. I am needing to pull specific data from a column as
result of data in two other columns with the end result in the second
worksheet. Normally I would use the VLookup; however I have two ranges and
this is not possible. So far this is the formula I have come up with, yet I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), I1:I65536

PLEASE HELP!

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am fairly new
to
as advanced of work in excel as this. I also noticed that what I had
earlier
was written wrong, but either way neither formulas worked. I adjusted the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one
condenses
data from the other. I am needing to pull specific data from a column
as
result of data in two other columns with the end result in the second
worksheet. Normally I would use the VLookup; however I have two ranges
and
this is not possible. So far this is the formula I have come up with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), I1:I65536


PLEASE HELP!

I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am fairly new
to
as advanced of work in excel as this. I also noticed that what I had
earlier
was written wrong, but either way neither formulas worked. I adjusted the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one
condenses
data from the other. I am needing to pull specific data from a column
as
result of data in two other columns with the end result in the second
worksheet. Normally I would use the VLookup; however I have two ranges
and
this is not possible. So far this is the formula I have come up with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), I1:I65536


PLEASE HELP!

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"chelles" wrote in message
...
I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am fairly
new
to
as advanced of work in excel as this. I also noticed that what I had
earlier
was written wrong, but either way neither formulas worked. I adjusted
the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing
to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one
condenses
data from the other. I am needing to pull specific data from a
column
as
result of data in two other columns with the end result in the
second
worksheet. Normally I would use the VLookup; however I have two
ranges
and
this is not possible. So far this is the formula I have come up
with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
I1:I65536


PLEASE HELP!

Thank you so much Bob and Rag. It worked PERFECT!!!!

"RagDyeR" wrote:

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"chelles" wrote in message
...
I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am fairly
new
to
as advanced of work in excel as this. I also noticed that what I had
earlier
was written wrong, but either way neither formulas worked. I adjusted
the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing
to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one
condenses
data from the other. I am needing to pull specific data from a
column
as
result of data in two other columns with the end result in the
second
worksheet. Normally I would use the VLookup; however I have two
ranges
and
this is not possible. So far this is the formula I have come up
with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
I1:I65536


PLEASE HELP!

I do have a couple quick questions.

1. How do I lock formulas so that they cannot be deleted without locking the
sheet?

2.In the final formula:
=SUMPRODUCT(--('Report Data'!$F$7:$F$65535=1),--('Report
Data'!$R$7:$R$65535="QL"),'Report Data'$I$7:$I$65535)

What is the significance of the dashes "--"?


"chelles" wrote:

Thank you so much Bob and Rag. It worked PERFECT!!!!

"RagDyeR" wrote:

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"chelles" wrote in message
...
I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am fairly
new
to
as advanced of work in excel as this. I also noticed that what I had
earlier
was written wrong, but either way neither formulas worked. I adjusted
the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing
to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one
condenses
data from the other. I am needing to pull specific data from a
column
as
result of data in two other columns with the end result in the
second
worksheet. Normally I would use the VLookup; however I have two
ranges
and
this is not possible. So far this is the formula I have come up
with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
I1:I65536


PLEASE HELP!

AFAIK, you'll need code to protect the formulas without protecting the
sheet.

As for the double unary (--), it changes the True and False returns from the
SumProduct formula to 1's and 0's, so that they can be used in the
calculations.

I personally prefer the asterisk form:

=SUMPRODUCT(('Report Data'!$F$7:$F$65535=1)*('Report
Data'!$R$7:$R$65535="QL")*'Report Data'!$I$7:$I$65535)

Since it will create an error message if any of the data is corrupted, and
not just calculate the "good" data, without letting you know that
'something' is remiss.

Anyway, for a more extensive explanation of the unary, try these links:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"chelles" wrote in message
...
I do have a couple quick questions.

1. How do I lock formulas so that they cannot be deleted without locking
the
sheet?

2.In the final formula:
=SUMPRODUCT(--('Report Data'!$F$7:$F$65535=1),--('Report
Data'!$R$7:$R$65535="QL"),'Report Data'$I$7:$I$65535)

What is the significance of the dashes "--"?


"chelles" wrote:

Thank you so much Bob and Rag. It worked PERFECT!!!!

"RagDyeR" wrote:

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"chelles" wrote in message
...
I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am
fairly
new
to
as advanced of work in excel as this. I also noticed that what I
had
earlier
was written wrong, but either way neither formulas worked. I
adjusted
the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data
needing
to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing
what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one
condenses
data from the other. I am needing to pull specific data from a
column
as
result of data in two other columns with the end result in the
second
worksheet. Normally I would use the VLookup; however I have two
ranges
and
this is not possible. So far this is the formula I have come up
with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
I1:I65536


PLEASE HELP!

AFAIK? I have no clue what this is and will probably kick myself.
Thank you for all of your help and explanations.

"Ragdyer" wrote:

AFAIK, you'll need code to protect the formulas without protecting the
sheet.

As for the double unary (--), it changes the True and False returns from the
SumProduct formula to 1's and 0's, so that they can be used in the
calculations.

I personally prefer the asterisk form:

=SUMPRODUCT(('Report Data'!$F$7:$F$65535=1)*('Report
Data'!$R$7:$R$65535="QL")*'Report Data'!$I$7:$I$65535)

Since it will create an error message if any of the data is corrupted, and
not just calculate the "good" data, without letting you know that
'something' is remiss.

Anyway, for a more extensive explanation of the unary, try these links:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"chelles" wrote in message
...
I do have a couple quick questions.

1. How do I lock formulas so that they cannot be deleted without locking
the
sheet?

2.In the final formula:
=SUMPRODUCT(--('Report Data'!$F$7:$F$65535=1),--('Report
Data'!$R$7:$R$65535="QL"),'Report Data'$I$7:$I$65535)

What is the significance of the dashes "--"?


"chelles" wrote:

Thank you so much Bob and Rag. It worked PERFECT!!!!

"RagDyeR" wrote:

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"chelles" wrote in message
...
I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am
fairly
new
to
as advanced of work in excel as this. I also noticed that what I
had
earlier
was written wrong, but either way neither formulas worked. I
adjusted
the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data
needing
to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing
what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
I have two worksheets in one workbook. Both contain tables, one
condenses
data from the other. I am needing to pull specific data from a
column
as
result of data in two other columns with the end result in the
second
worksheet. Normally I would use the VLookup; however I have two
ranges
and
this is not possible. So far this is the formula I have come up
with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
I1:I65536


PLEASE HELP!

As Far As I Know - http://www.acronymdictionary.co.uk/

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
AFAIK? I have no clue what this is and will probably kick myself.
Thank you for all of your help and explanations.

"Ragdyer" wrote:

AFAIK, you'll need code to protect the formulas without protecting the
sheet.

As for the double unary (--), it changes the True and False returns from
the
SumProduct formula to 1's and 0's, so that they can be used in the
calculations.

I personally prefer the asterisk form:

=SUMPRODUCT(('Report Data'!$F$7:$F$65535=1)*('Report
Data'!$R$7:$R$65535="QL")*'Report Data'!$I$7:$I$65535)

Since it will create an error message if any of the data is corrupted,
and
not just calculate the "good" data, without letting you know that
'something' is remiss.

Anyway, for a more extensive explanation of the unary, try these links:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"chelles" wrote in message
...
I do have a couple quick questions.

1. How do I lock formulas so that they cannot be deleted without
locking
the
sheet?

2.In the final formula:
=SUMPRODUCT(--('Report Data'!$F$7:$F$65535=1),--('Report
Data'!$R$7:$R$65535="QL"),'Report Data'$I$7:$I$65535)

What is the significance of the dashes "--"?


"chelles" wrote:

Thank you so much Bob and Rag. It worked PERFECT!!!!

"RagDyeR" wrote:

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"chelles" wrote in message
...
I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am
fairly
new
to
as advanced of work in excel as this. I also noticed that what
I
had
earlier
was written wrong, but either way neither formulas worked. I
adjusted
the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data
needing
to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome.
Summing
what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"chelles" wrote in
message
...
I have two worksheets in one workbook. Both contain tables,
one
condenses
data from the other. I am needing to pull specific data
from a
column
as
result of data in two other columns with the end result in
the
second
worksheet. Normally I would use the VLookup; however I have
two
ranges
and
this is not possible. So far this is the formula I have
come up
with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report
Data'!R7:R65536=QL),
I1:I65536


PLEASE HELP!

HAHAHA!
Like I said, a nice kick in the butt.

Thank you both for all of your help!

"Bob Phillips" wrote:

As Far As I Know - http://www.acronymdictionary.co.uk/

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
AFAIK? I have no clue what this is and will probably kick myself.
Thank you for all of your help and explanations.

"Ragdyer" wrote:

AFAIK, you'll need code to protect the formulas without protecting the
sheet.

As for the double unary (--), it changes the True and False returns from
the
SumProduct formula to 1's and 0's, so that they can be used in the
calculations.

I personally prefer the asterisk form:

=SUMPRODUCT(('Report Data'!$F$7:$F$65535=1)*('Report
Data'!$R$7:$R$65535="QL")*'Report Data'!$I$7:$I$65535)

Since it will create an error message if any of the data is corrupted,
and
not just calculate the "good" data, without letting you know that
'something' is remiss.

Anyway, for a more extensive explanation of the unary, try these links:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"chelles" wrote in message
...
I do have a couple quick questions.

1. How do I lock formulas so that they cannot be deleted without
locking
the
sheet?

2.In the final formula:
=SUMPRODUCT(--('Report Data'!$F$7:$F$65535=1),--('Report
Data'!$R$7:$R$65535="QL"),'Report Data'$I$7:$I$65535)

What is the significance of the dashes "--"?


"chelles" wrote:

Thank you so much Bob and Rag. It worked PERFECT!!!!

"RagDyeR" wrote:

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"chelles" wrote in message
...
I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I am
fairly
new
to
as advanced of work in excel as this. I also noticed that what
I
had
earlier
was written wrong, but either way neither formulas worked. I
adjusted
the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data
needing
to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome.
Summing
what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"chelles" wrote in
message
...
I have two worksheets in one workbook. Both contain tables,
one
condenses
data from the other. I am needing to pull specific data
from a
column
as
result of data in two other columns with the end result in
the
second
worksheet. Normally I would use the VLookup; however I have
two
ranges
and
this is not possible. So far this is the formula I have
come up
with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report
Data'!R7:R65536=QL),
I1:I65536


PLEASE HELP!

We appreciate the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"chelles" wrote in message
...
HAHAHA!
Like I said, a nice kick in the butt.

Thank you both for all of your help!

"Bob Phillips" wrote:

As Far As I Know - http://www.acronymdictionary.co.uk/

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chelles" wrote in message
...
AFAIK? I have no clue what this is and will probably kick myself.
Thank you for all of your help and explanations.

"Ragdyer" wrote:

AFAIK, you'll need code to protect the formulas without protecting the
sheet.

As for the double unary (--), it changes the True and False returns
from
the
SumProduct formula to 1's and 0's, so that they can be used in the
calculations.

I personally prefer the asterisk form:

=SUMPRODUCT(('Report Data'!$F$7:$F$65535=1)*('Report
Data'!$R$7:$R$65535="QL")*'Report Data'!$I$7:$I$65535)

Since it will create an error message if any of the data is corrupted,
and
not just calculate the "good" data, without letting you know that
'something' is remiss.

Anyway, for a more extensive explanation of the unary, try these
links:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
HTH,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"chelles" wrote in message
...
I do have a couple quick questions.

1. How do I lock formulas so that they cannot be deleted without
locking
the
sheet?

2.In the final formula:
=SUMPRODUCT(--('Report Data'!$F$7:$F$65535=1),--('Report
Data'!$R$7:$R$65535="QL"),'Report Data'$I$7:$I$65535)

What is the significance of the dashes "--"?


"chelles" wrote:

Thank you so much Bob and Rag. It worked PERFECT!!!!

"RagDyeR" wrote:

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"chelles" wrote in message
...
I got a value error on this as well.

"Bob Phillips" wrote:

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"chelles" wrote in message
...
This didnt work. I am not sure how to expalin what I need. I
am
fairly
new
to
as advanced of work in excel as this. I also noticed that
what
I
had
earlier
was written wrong, but either way neither formulas worked. I
adjusted
the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL),
SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of
data
needing
to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome.
Summing
what
matches with the two listed ranges above.



"Bob Phillips" wrote:

=INDEX(I1:I65535,MATCH(1,('Report
Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"chelles" wrote in
message
...
I have two worksheets in one workbook. Both contain
tables,
one
condenses
data from the other. I am needing to pull specific data
from a
column
as
result of data in two other columns with the end result
in
the
second
worksheet. Normally I would use the VLookup; however I
have
two
ranges
and
this is not possible. So far this is the formula I have
come up
with,
yet
I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report
Data'!R7:R65536=QL),
I1:I65536


PLEASE HELP!