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!