I have a sumproduct equation which finds the respective numbers on one
sheet and pulls it into a designated location on another sheet. Now
the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
pulls a number (which makes sense because it a sumproduct formula),
but I now have text in this column, is there away to make this
equation pull a text, or whatever is in that cell over? Maybe this is
not the correct equation, can anyone help me with the correct equation
which has these criteria and pulls text as the end result?

=SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
(1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)

The equation basically is stating that if range B ='s (let say Ryan)
and range C ='s age, and range I ='s the month born and range J ='s
the year born and range H ='s right handed, then the G range will find
the correct match for this criteria. (this is an example. This
equation will find the correct match out of a huge list of names,
ages, months, year, etc etc and put it into a specific designated
location on another sheet. It works too, so the question is getting a
text instead of a number over on the G range).

ryan

=INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
('Pulled from Access'!$B$7:$B$5420=Data!$A11)
*('Pulled from Access'!$C$7:$C$5420=Data!$B11)
*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
2007")))
*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
*('Pulled from Access'!$H$7:$H$5420="R"),0))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<(E-Mail Removed)> wrote in message
news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
>I have a sumproduct equation which finds the respective numbers on one
> sheet and pulls it into a designated location on another sheet. Now
> the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> pulls a number (which makes sense because it a sumproduct formula),
> but I now have text in this column, is there away to make this
> equation pull a text, or whatever is in that cell over? Maybe this is
> not the correct equation, can anyone help me with the correct equation
> which has these criteria and pulls text as the end result?
>
> =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
>
> The equation basically is stating that if range B ='s (let say Ryan)
> and range C ='s age, and range I ='s the month born and range J ='s
> the year born and range H ='s right handed, then the G range will find
> the correct match for this criteria. (this is an example. This
> equation will find the correct match out of a huge list of names,
> ages, months, year, etc etc and put it into a specific designated
> location on another sheet. It works too, so the question is getting a
> text instead of a number over on the G range).
>
> ryan

Didn't work said NA in the formula setup.



On Jan 9, 3:38 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> =INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
> ('Pulled from Access'!$B$7:$B$5420=Data!$A11)
> *('Pulled from Access'!$C$7:$C$5420=Data!$B11)
> *('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
> 2007")))
> *('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
> *('Pulled from Access'!$H$7:$H$5420="R"),0))
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <ryan.fitzpatri...@safeway.com> wrote in message
>
> news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
>
> >I have a sumproduct equation which finds the respective numbers on one
> > sheet and pulls it into a designated location on another sheet. Now
> > the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> > pulls a number (which makes sense because it a sumproduct formula),
> > but I now have text in this column, is there away to make this
> > equation pull a text, or whatever is in that cell over? Maybe this is
> > not the correct equation, can anyone help me with the correct equation
> > which has these criteria and pulls text as the end result?
>
> > =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> > (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> > Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> > ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> > Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
>
> > The equation basically is stating that if range B ='s (let say Ryan)
> > and range C ='s age, and range I ='s the month born and range J ='s
> > the year born and range H ='s right handed, then the G range will find
> > the correct match for this criteria. (this is an example. This
> > equation will find the correct match out of a huge list of names,
> > ages, months, year, etc etc and put it into a specific designated
> > location on another sheet. It works too, so the question is getting a
> > text instead of a number over on the G range).
>
> > ryan

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(E-Mail Removed) wrote:
>
> Didn't work said NA in the formula setup.
>
> On Jan 9, 3:38 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > =INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
> > ('Pulled from Access'!$B$7:$B$5420=Data!$A11)
> > *('Pulled from Access'!$C$7:$C$5420=Data!$B11)
> > *('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
> > 2007")))
> > *('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
> > *('Pulled from Access'!$H$7:$H$5420="R"),0))
> >
> > --
> > HTH
> >
> > Bob
> >
> > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> >
> > <ryan.fitzpatri...@safeway.com> wrote in message
> >
> > news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
> >
> > >I have a sumproduct equation which finds the respective numbers on one
> > > sheet and pulls it into a designated location on another sheet. Now
> > > the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> > > pulls a number (which makes sense because it a sumproduct formula),
> > > but I now have text in this column, is there away to make this
> > > equation pull a text, or whatever is in that cell over? Maybe this is
> > > not the correct equation, can anyone help me with the correct equation
> > > which has these criteria and pulls text as the end result?
> >
> > > =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> > > (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> > > Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> > > ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> > > Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
> >
> > > The equation basically is stating that if range B ='s (let say Ryan)
> > > and range C ='s age, and range I ='s the month born and range J ='s
> > > the year born and range H ='s right handed, then the G range will find
> > > the correct match for this criteria. (this is an example. This
> > > equation will find the correct match out of a huge list of names,
> > > ages, months, year, etc etc and put it into a specific designated
> > > location on another sheet. It works too, so the question is getting a
> > > text instead of a number over on the G range).
> >
> > > ryan

--

Dave Peterson

I did the ctr-shift -enter and the brackets were put onto the formula
but it still says #NA.

=INDEX('Pulled from Access'!D72500,MATCH(1,('Pulled from Access'!$B
$7:$B$5420=Data!$A11)*('Pulled from Access'!$C$7:$C$5420=Data!
$B11)*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&"
1,2007")))*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))*('Pulled
from Access'!$H$7:$H$5420="R"),0))

ryan

On Jan 9, 4:45 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> correctly, excel will wrap curly brackets {} around your formula. (don't type
> them yourself.)
>
>
>
> ryan.fitzpatri...@safeway.com wrote:
>
> > Didn't work said NA in the formula setup.
>
> > On Jan 9, 3:38 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > > =INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
> > > ('Pulled from Access'!$B$7:$B$5420=Data!$A11)
> > > *('Pulled from Access'!$C$7:$C$5420=Data!$B11)
> > > *('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
> > > 2007")))
> > > *('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
> > > *('Pulled from Access'!$H$7:$H$5420="R"),0))
>
> > > --
> > > HTH
>
> > > Bob
>
> > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> > > <ryan.fitzpatri...@safeway.com> wrote in message
>
> > >news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
>
> > > >I have a sumproduct equation which finds the respective numbers on one
> > > > sheet and pulls it into a designated location on another sheet. Now
> > > > the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> > > > pulls a number (which makes sense because it a sumproduct formula),
> > > > but I now have text in this column, is there away to make this
> > > > equation pull a text, or whatever is in that cell over? Maybe this is
> > > > not the correct equation, can anyone help me with the correct equation
> > > > which has these criteria and pulls text as the end result?
>
> > > > =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> > > > (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> > > > Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> > > > ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> > > > Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
>
> > > > The equation basically is stating that if range B ='s (let say Ryan)
> > > > and range C ='s age, and range I ='s the month born and range J ='s
> > > > the year born and range H ='s right handed, then the G range will find
> > > > the correct match for this criteria. (this is an example. This
> > > > equation will find the correct match out of a huge list of names,
> > > > ages, months, year, etc etc and put it into a specific designated
> > > > location on another sheet. It works too, so the question is getting a
> > > > text instead of a number over on the G range).
>
> > > > ryan
>
> --
>
> Dave Peterson

Maybe you don't have a row that has all those matches???

If you're positive you do, I'd double check it.

Pick out the row that should be the match (say row 222). Then put these
formulas in empty cells:

='Pulled from Access'!B222=Data!$A11
='Pulled from Access'!C222=Data!$B11
='Pulled from Access'!I222=MONTH(DATEVALUE(Data!D$10&" 1,2007"))
='Pulled from Access'!J222=MID($I$8,3,2)
='Pulled from Access'!H222="R"

If they all return TRUE, then I don't know an answer.

If any return False, you'll know that something isn't the way you expected it.




(E-Mail Removed) wrote:
>
> I did the ctr-shift -enter and the brackets were put onto the formula
> but it still says #NA.
>
> =INDEX('Pulled from Access'!D72500,MATCH(1,('Pulled from Access'!$B
> $7:$B$5420=Data!$A11)*('Pulled from Access'!$C$7:$C$5420=Data!
> $B11)*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&"
> 1,2007")))*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))*('Pulled
> from Access'!$H$7:$H$5420="R"),0))
>
> ryan
>
> On Jan 9, 4:45 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > them yourself.)
> >
> >
> >
> > ryan.fitzpatri...@safeway.com wrote:
> >
> > > Didn't work said NA in the formula setup.
> >
> > > On Jan 9, 3:38 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > > > =INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
> > > > ('Pulled from Access'!$B$7:$B$5420=Data!$A11)
> > > > *('Pulled from Access'!$C$7:$C$5420=Data!$B11)
> > > > *('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
> > > > 2007")))
> > > > *('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
> > > > *('Pulled from Access'!$H$7:$H$5420="R"),0))
> >
> > > > --
> > > > HTH
> >
> > > > Bob
> >
> > > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> >
> > > > <ryan.fitzpatri...@safeway.com> wrote in message
> >
> > > >news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
> >
> > > > >I have a sumproduct equation which finds the respective numbers on one
> > > > > sheet and pulls it into a designated location on another sheet. Now
> > > > > the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> > > > > pulls a number (which makes sense because it a sumproduct formula),
> > > > > but I now have text in this column, is there away to make this
> > > > > equation pull a text, or whatever is in that cell over? Maybe this is
> > > > > not the correct equation, can anyone help me with the correct equation
> > > > > which has these criteria and pulls text as the end result?
> >
> > > > > =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> > > > > (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> > > > > Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> > > > > ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> > > > > Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
> >
> > > > > The equation basically is stating that if range B ='s (let say Ryan)
> > > > > and range C ='s age, and range I ='s the month born and range J ='s
> > > > > the year born and range H ='s right handed, then the G range will find
> > > > > the correct match for this criteria. (this is an example. This
> > > > > equation will find the correct match out of a huge list of names,
> > > > > ages, months, year, etc etc and put it into a specific designated
> > > > > location on another sheet. It works too, so the question is getting a
> > > > > text instead of a number over on the G range).
> >
> > > > > ryan
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

I think the problem is that the row can be any row where ever that
criteria is meet, since the list gets bigger because of data being
inputed. This is why there is a range for the criteria, because the
respective cell that i'm looking for could be in that range. Does the
index and match formula do the same as this sumproduct formula, except
that it looks for text instead of numbers.

=SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
$B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
$7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$G$7:$G$5420)

The end result of this formula is in the G range where there is a
number. In the F range I have text i.e. first names, so I would like
this equation or whatever equation that finds text to do the same
function as this but list text. I didn't think it would be that
difficult. So I changed it orginally to below to look for the first
names (text).

=SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
$B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
$7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$F$7:$F$5420)

Now does the index equation do what I'm looking for? Thanks for you
help by the way.

=INDEX(D72500,MATCH(1,($B$7:$B$5420=Data!$A11)*($C$7:$C$5420=Data!
$B11)*($I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,2007")))*($J$7:$J
$5420=MID($I$8,3,2))*($H$7:$H$5420="R"),0))

ryan
On Jan 10, 9:17 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Maybe you don't have a row that has all those matches???
>
> If you're positive you do, I'd double check it.
>
> Pick out the row that should be the match (say row 222). Then put these
> formulas in empty cells:
>
> ='Pulled from Access'!B222=Data!$A11
> ='Pulled from Access'!C222=Data!$B11
> ='Pulled from Access'!I222=MONTH(DATEVALUE(Data!D$10&" 1,2007"))
> ='Pulled from Access'!J222=MID($I$8,3,2)
> ='Pulled from Access'!H222="R"
>
> If they all return TRUE, then I don't know an answer.
>
> If any return False, you'll know that something isn't the way you expected it.
>
>
>
> ryan.fitzpatri...@safeway.com wrote:
>
> > I did the ctr-shift -enter and the brackets were put onto the formula
> > but it still says #NA.
>
> > =INDEX('Pulled from Access'!D72500,MATCH(1,('Pulled from Access'!$B
> > $7:$B$5420=Data!$A11)*('Pulled from Access'!$C$7:$C$5420=Data!
> > $B11)*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&"
> > 1,2007")))*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))*('Pulled
> > from Access'!$H$7:$H$5420="R"),0))
>
> > ryan
>
> > On Jan 9, 4:45 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > > them yourself.)
>
> > > ryan.fitzpatri...@safeway.com wrote:
>
> > > > Didn't work said NA in the formula setup.
>
> > > > On Jan 9, 3:38 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > > > > =INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
> > > > > ('Pulled from Access'!$B$7:$B$5420=Data!$A11)
> > > > > *('Pulled from Access'!$C$7:$C$5420=Data!$B11)
> > > > > *('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
> > > > > 2007")))
> > > > > *('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
> > > > > *('Pulled from Access'!$H$7:$H$5420="R"),0))
>
> > > > > --
> > > > > HTH
>
> > > > > Bob
>
> > > > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> > > > > <ryan.fitzpatri...@safeway.com> wrote in message
>
> > > > >news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
>
> > > > > >I have a sumproduct equation which finds the respective numbers on one
> > > > > > sheet and pulls it into a designated location on another sheet. Now
> > > > > > the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> > > > > > pulls a number (which makes sense because it a sumproduct formula),
> > > > > > but I now have text in this column, is there away to make this
> > > > > > equation pull a text, or whatever is in that cell over? Maybe this is
> > > > > > not the correct equation, can anyone help me with the correct equation
> > > > > > which has these criteria and pulls text as the end result?
>
> > > > > > =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> > > > > > (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> > > > > > Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> > > > > > ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> > > > > > Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
>
> > > > > > The equation basically is stating that if range B ='s (let say Ryan)
> > > > > > and range C ='s age, and range I ='s the month born and range J ='s
> > > > > > the year born and range H ='s right handed, then the G range will find
> > > > > > the correct match for this criteria. (this is an example. This
> > > > > > equation will find the correct match out of a huge list of names,
> > > > > > ages, months, year, etc etc and put it into a specific designated
> > > > > > location on another sheet. It works too, so the question is getting a
> > > > > > text instead of a number over on the G range).
>
> > > > > > ryan
>
> > > --
>
> > > Dave Peterson
>
> --
>
> Dave Peterson

I understand why the formula contains a range--several ranges, in fact.

If you say that there is a match (on the same row) for all those criteria, then
it shouldn't be that difficult to test those formulas I suggested in the
previous post.

What happens when you find the row that matches and use those formulas?





(E-Mail Removed) wrote:
>
> I think the problem is that the row can be any row where ever that
> criteria is meet, since the list gets bigger because of data being
> inputed. This is why there is a range for the criteria, because the
> respective cell that i'm looking for could be in that range. Does the
> index and match formula do the same as this sumproduct formula, except
> that it looks for text instead of numbers.
>
> =SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
> $B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
> $7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$G$7:$G$5420)
>
> The end result of this formula is in the G range where there is a
> number. In the F range I have text i.e. first names, so I would like
> this equation or whatever equation that finds text to do the same
> function as this but list text. I didn't think it would be that
> difficult. So I changed it orginally to below to look for the first
> names (text).
>
> =SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
> $B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
> $7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$F$7:$F$5420)
>
> Now does the index equation do what I'm looking for? Thanks for you
> help by the way.
>
> =INDEX(D72500,MATCH(1,($B$7:$B$5420=Data!$A11)*($C$7:$C$5420=Data!
> $B11)*($I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,2007")))*($J$7:$J
> $5420=MID($I$8,3,2))*($H$7:$H$5420="R"),0))
>
> ryan
> On Jan 10, 9:17 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Maybe you don't have a row that has all those matches???
> >
> > If you're positive you do, I'd double check it.
> >
> > Pick out the row that should be the match (say row 222). Then put these
> > formulas in empty cells:
> >
> > ='Pulled from Access'!B222=Data!$A11
> > ='Pulled from Access'!C222=Data!$B11
> > ='Pulled from Access'!I222=MONTH(DATEVALUE(Data!D$10&" 1,2007"))
> > ='Pulled from Access'!J222=MID($I$8,3,2)
> > ='Pulled from Access'!H222="R"
> >
> > If they all return TRUE, then I don't know an answer.
> >
> > If any return False, you'll know that something isn't the way you expected it.
> >
> >
> >
> > ryan.fitzpatri...@safeway.com wrote:
> >
> > > I did the ctr-shift -enter and the brackets were put onto the formula
> > > but it still says #NA.
> >
> > > =INDEX('Pulled from Access'!D72500,MATCH(1,('Pulled from Access'!$B
> > > $7:$B$5420=Data!$A11)*('Pulled from Access'!$C$7:$C$5420=Data!
> > > $B11)*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&"
> > > 1,2007")))*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))*('Pulled
> > > from Access'!$H$7:$H$5420="R"),0))
> >
> > > ryan
> >
> > > On Jan 9, 4:45 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > > > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > > > them yourself.)
> >
> > > > ryan.fitzpatri...@safeway.com wrote:
> >
> > > > > Didn't work said NA in the formula setup.
> >
> > > > > On Jan 9, 3:38 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > > > > > =INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
> > > > > > ('Pulled from Access'!$B$7:$B$5420=Data!$A11)
> > > > > > *('Pulled from Access'!$C$7:$C$5420=Data!$B11)
> > > > > > *('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
> > > > > > 2007")))
> > > > > > *('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
> > > > > > *('Pulled from Access'!$H$7:$H$5420="R"),0))
> >
> > > > > > --
> > > > > > HTH
> >
> > > > > > Bob
> >
> > > > > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> >
> > > > > > <ryan.fitzpatri...@safeway.com> wrote in message
> >
> > > > > >news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
> >
> > > > > > >I have a sumproduct equation which finds the respective numbers on one
> > > > > > > sheet and pulls it into a designated location on another sheet. Now
> > > > > > > the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> > > > > > > pulls a number (which makes sense because it a sumproduct formula),
> > > > > > > but I now have text in this column, is there away to make this
> > > > > > > equation pull a text, or whatever is in that cell over? Maybe this is
> > > > > > > not the correct equation, can anyone help me with the correct equation
> > > > > > > which has these criteria and pulls text as the end result?
> >
> > > > > > > =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> > > > > > > (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> > > > > > > Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> > > > > > > ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> > > > > > > Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
> >
> > > > > > > The equation basically is stating that if range B ='s (let say Ryan)
> > > > > > > and range C ='s age, and range I ='s the month born and range J ='s
> > > > > > > the year born and range H ='s right handed, then the G range will find
> > > > > > > the correct match for this criteria. (this is an example. This
> > > > > > > equation will find the correct match out of a huge list of names,
> > > > > > > ages, months, year, etc etc and put it into a specific designated
> > > > > > > location on another sheet. It works too, so the question is getting a
> > > > > > > text instead of a number over on the G range).
> >
> > > > > > > ryan
> >
> > > > --
> >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

I put this equation into a blank cell, it showed false.

='Pulled from Access'!B222=Data!$A11


On Jan 10, 11:23 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I understand why the formula contains a range--several ranges, in fact.
>
> If you say that there is a match (on the same row) for all those criteria, then
> it shouldn't be that difficult to test those formulas I suggested in the
> previous post.
>
> What happens when you find the row that matches and use those formulas?
>
>
>
> ryan.fitzpatri...@safeway.com wrote:
>
> > I think the problem is that the row can be any row where ever that
> > criteria is meet, since the list gets bigger because of data being
> > inputed. This is why there is a range for the criteria, because the
> > respective cell that i'm looking for could be in that range. Does the
> > index and match formula do the same as this sumproduct formula, except
> > that it looks for text instead of numbers.
>
> > =SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
> > $B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
> > $7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$G$7:$G$5420)
>
> > The end result of this formula is in the G range where there is a
> > number. In the F range I have text i.e. first names, so I would like
> > this equation or whatever equation that finds text to do the same
> > function as this but list text. I didn't think it would be that
> > difficult. So I changed it orginally to below to look for the first
> > names (text).
>
> > =SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
> > $B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
> > $7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$F$7:$F$5420)
>
> > Now does the index equation do what I'm looking for? Thanks for you
> > help by the way.
>
> > =INDEX(D72500,MATCH(1,($B$7:$B$5420=Data!$A11)*($C$7:$C$5420=Data!
> > $B11)*($I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,2007")))*($J$7:$J
> > $5420=MID($I$8,3,2))*($H$7:$H$5420="R"),0))
>
> > ryan
> > On Jan 10, 9:17 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > Maybe you don't have a row that has all those matches???
>
> > > If you're positive you do, I'd double check it.
>
> > > Pick out the row that should be the match (say row 222). Then put these
> > > formulas in empty cells:
>
> > > ='Pulled from Access'!B222=Data!$A11
> > > ='Pulled from Access'!C222=Data!$B11
> > > ='Pulled from Access'!I222=MONTH(DATEVALUE(Data!D$10&" 1,2007"))
> > > ='Pulled from Access'!J222=MID($I$8,3,2)
> > > ='Pulled from Access'!H222="R"
>
> > > If they all return TRUE, then I don't know an answer.
>
> > > If any return False, you'll know that something isn't the way you expected it.
>
> > > ryan.fitzpatri...@safeway.com wrote:
>
> > > > I did the ctr-shift -enter and the brackets were put onto the formula
> > > > but it still says #NA.
>
> > > > =INDEX('Pulled from Access'!D72500,MATCH(1,('Pulled from Access'!$B
> > > > $7:$B$5420=Data!$A11)*('Pulled from Access'!$C$7:$C$5420=Data!
> > > > $B11)*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&"
> > > > 1,2007")))*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))*('Pulled
> > > > from Access'!$H$7:$H$5420="R"),0))
>
> > > > ryan
>
> > > > On Jan 9, 4:45 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > > > > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > > > > them yourself.)
>
> > > > > ryan.fitzpatri...@safeway.com wrote:
>
> > > > > > Didn't work said NA in the formula setup.
>
> > > > > > On Jan 9, 3:38 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > > > > > > =INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
> > > > > > > ('Pulled from Access'!$B$7:$B$5420=Data!$A11)
> > > > > > > *('Pulled from Access'!$C$7:$C$5420=Data!$B11)
> > > > > > > *('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
> > > > > > > 2007")))
> > > > > > > *('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
> > > > > > > *('Pulled from Access'!$H$7:$H$5420="R"),0))
>
> > > > > > > --
> > > > > > > HTH
>
> > > > > > > Bob
>
> > > > > > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> > > > > > > <ryan.fitzpatri...@safeway.com> wrote in message
>
> > > > > > >news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
>
> > > > > > > >I have a sumproduct equation which finds the respective numbers on one
> > > > > > > > sheet and pulls it into a designated location on another sheet. Now
> > > > > > > > the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> > > > > > > > pulls a number (which makes sense because it a sumproduct formula),
> > > > > > > > but I now have text in this column, is there away to make this
> > > > > > > > equation pull a text, or whatever is in that cell over? Maybe this is
> > > > > > > > not the correct equation, can anyone help me with the correct equation
> > > > > > > > which has these criteria and pulls text as the end result?
>
> > > > > > > > =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> > > > > > > > (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> > > > > > > > Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> > > > > > > > ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> > > > > > > > Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
>
> > > > > > > > The equation basically is stating that if range B ='s (let say Ryan)
> > > > > > > > and range C ='s age, and range I ='s the month born and range J ='s
> > > > > > > > the year born and range H ='s right handed, then the G range will find
> > > > > > > > the correct match for this criteria. (this is an example. This
> > > > > > > > equation will find the correct match out of a huge list of names,
> > > > > > > > ages, months, year, etc etc and put it into a specific designated
> > > > > > > > location on another sheet. It works too, so the question is getting a
> > > > > > > > text instead of a number over on the G range).
>
> > > > > > > > ryan
>
> > > > > --
>
> > > > > Dave Peterson
>
> > > --
>
> > > Dave Peterson
>
> --
>
> Dave Peterson

Then row 222 doesn't have the match.

You were supposed to find the row that looked like the match would work. If you
don't want to try that, then I guess I'm out of ideas.

(E-Mail Removed) wrote:
>
> I put this equation into a blank cell, it showed false.
>
> ='Pulled from Access'!B222=Data!$A11
>
> On Jan 10, 11:23 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > I understand why the formula contains a range--several ranges, in fact.
> >
> > If you say that there is a match (on the same row) for all those criteria, then
> > it shouldn't be that difficult to test those formulas I suggested in the
> > previous post.
> >
> > What happens when you find the row that matches and use those formulas?
> >
> >
> >
> > ryan.fitzpatri...@safeway.com wrote:
> >
> > > I think the problem is that the row can be any row where ever that
> > > criteria is meet, since the list gets bigger because of data being
> > > inputed. This is why there is a range for the criteria, because the
> > > respective cell that i'm looking for could be in that range. Does the
> > > index and match formula do the same as this sumproduct formula, except
> > > that it looks for text instead of numbers.
> >
> > > =SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
> > > $B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
> > > $7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$G$7:$G$5420)
> >
> > > The end result of this formula is in the G range where there is a
> > > number. In the F range I have text i.e. first names, so I would like
> > > this equation or whatever equation that finds text to do the same
> > > function as this but list text. I didn't think it would be that
> > > difficult. So I changed it orginally to below to look for the first
> > > names (text).
> >
> > > =SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
> > > $B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
> > > $7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$F$7:$F$5420)
> >
> > > Now does the index equation do what I'm looking for? Thanks for you
> > > help by the way.
> >
> > > =INDEX(D72500,MATCH(1,($B$7:$B$5420=Data!$A11)*($C$7:$C$5420=Data!
> > > $B11)*($I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,2007")))*($J$7:$J
> > > $5420=MID($I$8,3,2))*($H$7:$H$5420="R"),0))
> >
> > > ryan
> > > On Jan 10, 9:17 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > Maybe you don't have a row that has all those matches???
> >
> > > > If you're positive you do, I'd double check it.
> >
> > > > Pick out the row that should be the match (say row 222). Then put these
> > > > formulas in empty cells:
> >
> > > > ='Pulled from Access'!B222=Data!$A11
> > > > ='Pulled from Access'!C222=Data!$B11
> > > > ='Pulled from Access'!I222=MONTH(DATEVALUE(Data!D$10&" 1,2007"))
> > > > ='Pulled from Access'!J222=MID($I$8,3,2)
> > > > ='Pulled from Access'!H222="R"
> >
> > > > If they all return TRUE, then I don't know an answer.
> >
> > > > If any return False, you'll know that something isn't the way you expected it.
> >
> > > > ryan.fitzpatri...@safeway.com wrote:
> >
> > > > > I did the ctr-shift -enter and the brackets were put onto the formula
> > > > > but it still says #NA.
> >
> > > > > =INDEX('Pulled from Access'!D72500,MATCH(1,('Pulled from Access'!$B
> > > > > $7:$B$5420=Data!$A11)*('Pulled from Access'!$C$7:$C$5420=Data!
> > > > > $B11)*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&"
> > > > > 1,2007")))*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))*('Pulled
> > > > > from Access'!$H$7:$H$5420="R"),0))
> >
> > > > > ryan
> >
> > > > > On Jan 9, 4:45 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > > > > > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > > > > > them yourself.)
> >
> > > > > > ryan.fitzpatri...@safeway.com wrote:
> >
> > > > > > > Didn't work said NA in the formula setup.
> >
> > > > > > > On Jan 9, 3:38 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > > > > > > > =INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
> > > > > > > > ('Pulled from Access'!$B$7:$B$5420=Data!$A11)
> > > > > > > > *('Pulled from Access'!$C$7:$C$5420=Data!$B11)
> > > > > > > > *('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
> > > > > > > > 2007")))
> > > > > > > > *('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
> > > > > > > > *('Pulled from Access'!$H$7:$H$5420="R"),0))
> >
> > > > > > > > --
> > > > > > > > HTH
> >
> > > > > > > > Bob
> >
> > > > > > > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> >
> > > > > > > > <ryan.fitzpatri...@safeway.com> wrote in message
> >
> > > > > > > >news:9151b7db-5d00-45fe-a680-(E-Mail Removed)...
> >
> > > > > > > > >I have a sumproduct equation which finds the respective numbers on one
> > > > > > > > > sheet and pulls it into a designated location on another sheet. Now
> > > > > > > > > the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
> > > > > > > > > pulls a number (which makes sense because it a sumproduct formula),
> > > > > > > > > but I now have text in this column, is there away to make this
> > > > > > > > > equation pull a text, or whatever is in that cell over? Maybe this is
> > > > > > > > > not the correct equation, can anyone help me with the correct equation
> > > > > > > > > which has these criteria and pulls text as the end result?
> >
> > > > > > > > > =SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
> > > > > > > > > (1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
> > > > > > > > > Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
> > > > > > > > > ('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
> > > > > > > > > Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)
> >
> > > > > > > > > The equation basically is stating that if range B ='s (let say Ryan)
> > > > > > > > > and range C ='s age, and range I ='s the month born and range J ='s
> > > > > > > > > the year born and range H ='s right handed, then the G range will find
> > > > > > > > > the correct match for this criteria. (this is an example. This
> > > > > > > > > equation will find the correct match out of a huge list of names,
> > > > > > > > > ages, months, year, etc etc and put it into a specific designated
> > > > > > > > > location on another sheet. It works too, so the question is getting a
> > > > > > > > > text instead of a number over on the G range).
> >
> > > > > > > > > ryan
> >
> > > > > > --
> >
> > > > > > Dave Peterson
> >
> > > > --
> >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson