more re index and match
 

Was sent this formula by Double Z.. one issue with it... on my sheet 2 my
array is actually A1:N57
to match to the drop down list in E 11 on sheet 2( I am using info From
A1:A57 store names sheet 2) then indexing same to match the date H3 sheet1
so that the info for Route # needed in I 13( Sunday to Saturday stored in
H1:N57 on sheet two)will populate... problem is formula will not correctly
match the selections after I get to entering Thursday in H:3 please help some
more?

=index(sheet2!$A$1:$D$50,match(sheet1!$E$11,index( sheet2!$A$1:$D$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$D$50,1,),0))

more re index and match
 

Kathy,

If Thursday is in cell E1, it won't be found since your match is only
looking through column D. Try this instead:

=index(sheet2!$A$1:$N$50,match(sheet1!$E$11,index( sheet2!$A$1:$N$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$N$50,1,),0))

HTH,
Bernie
MS Excel MVP

"kathy" wrote in message
...
Was sent this formula by Double Z.. one issue with it... on my sheet 2 my
array is actually A1:N57
to match to the drop down list in E 11 on sheet 2( I am using info From
A1:A57 store names sheet 2) then indexing same to match the date H3
sheet1
so that the info for Route # needed in I 13( Sunday to Saturday stored in
H1:N57 on sheet two)will populate... problem is formula will not correctly
match the selections after I get to entering Thursday in H:3 please help
some
more?

=index(sheet2!$A$1:$D$50,match(sheet1!$E$11,index( sheet2!$A$1:$D$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$D$50,1,),0))

more re index and match
 

Try this:

=INDEX(Sheet2!$A$1:$N$57,MATCH(Sheet1!$E$11,Sheet2 !$A$1:$A$57,0),MATCH(Sheet1!$H$3,Sheet2!$A$1:$N$1, 0))

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
Was sent this formula by Double Z.. one issue with it... on my sheet 2 my
array is actually A1:N57
to match to the drop down list in E 11 on sheet 2( I am using info From
A1:A57 store names sheet 2) then indexing same to match the date H3
sheet1
so that the info for Route # needed in I 13( Sunday to Saturday stored in
H1:N57 on sheet two)will populate... problem is formula will not correctly
match the selections after I get to entering Thursday in H:3 please help
some
more?

=index(sheet2!$A$1:$D$50,match(sheet1!$E$11,index( sheet2!$A$1:$D$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$D$50,1,),0))

more re index and match
 

yes this one did finally work.. one more query for you.. H3 is the day of the
week.. eg sunday thru Saturday I'd like to enter the actual date in the cell
next I 3 then have the day of the week pop up in H3 but that spoils the
match formula.. any ideas or advice on how I could accomplish this?

"T. Valko" wrote:

Try this:

=INDEX(Sheet2!$A$1:$N$57,MATCH(Sheet1!$E$11,Sheet2 !$A$1:$A$57,0),MATCH(Sheet1!$H$3,Sheet2!$A$1:$N$1, 0))

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
Was sent this formula by Double Z.. one issue with it... on my sheet 2 my
array is actually A1:N57
to match to the drop down list in E 11 on sheet 2( I am using info From
A1:A57 store names sheet 2) then indexing same to match the date H3
sheet1
so that the info for Route # needed in I 13( Sunday to Saturday stored in
H1:N57 on sheet two)will populate... problem is formula will not correctly
match the selections after I get to entering Thursday in H:3 please help
some
more?

=index(sheet2!$A$1:$D$50,match(sheet1!$E$11,index( sheet2!$A$1:$D$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$D$50,1,),0))

more re index and match
 

Try this:

I3 = some date like 10/27/2008

Enter this formula in H3:

=TEXT(I3,"dddd")

That will return the long weekday name like Monday. If you want the short
weekday name like Mon just remove one of the d's.

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
yes this one did finally work.. one more query for you.. H3 is the day of
the
week.. eg sunday thru Saturday I'd like to enter the actual date in the
cell
next I 3 then have the day of the week pop up in H3 but that spoils the
match formula.. any ideas or advice on how I could accomplish this?

"T. Valko" wrote:

Try this:

=INDEX(Sheet2!$A$1:$N$57,MATCH(Sheet1!$E$11,Sheet2 !$A$1:$A$57,0),MATCH(Sheet1!$H$3,Sheet2!$A$1:$N$1, 0))

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
Was sent this formula by Double Z.. one issue with it... on my sheet 2
my
array is actually A1:N57
to match to the drop down list in E 11 on sheet 2( I am using info From
A1:A57 store names sheet 2) then indexing same to match the date H3
sheet1
so that the info for Route # needed in I 13( Sunday to Saturday stored
in
H1:N57 on sheet two)will populate... problem is formula will not
correctly
match the selections after I get to entering Thursday in H:3 please
help
some
more?

=index(sheet2!$A$1:$D$50,match(sheet1!$E$11,index( sheet2!$A$1:$D$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$D$50,1,),0))

more re index and match
 

SWEET!!!! your help is so much appreciated... This will eliminate probably
half hour of data entry every monday.. Thank you!

"T. Valko" wrote:

Try this:

I3 = some date like 10/27/2008

Enter this formula in H3:

=TEXT(I3,"dddd")

That will return the long weekday name like Monday. If you want the short
weekday name like Mon just remove one of the d's.

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
yes this one did finally work.. one more query for you.. H3 is the day of
the
week.. eg sunday thru Saturday I'd like to enter the actual date in the
cell
next I 3 then have the day of the week pop up in H3 but that spoils the
match formula.. any ideas or advice on how I could accomplish this?

"T. Valko" wrote:

Try this:

=INDEX(Sheet2!$A$1:$N$57,MATCH(Sheet1!$E$11,Sheet2 !$A$1:$A$57,0),MATCH(Sheet1!$H$3,Sheet2!$A$1:$N$1, 0))

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
Was sent this formula by Double Z.. one issue with it... on my sheet 2
my
array is actually A1:N57
to match to the drop down list in E 11 on sheet 2( I am using info From
A1:A57 store names sheet 2) then indexing same to match the date H3
sheet1
so that the info for Route # needed in I 13( Sunday to Saturday stored
in
H1:N57 on sheet two)will populate... problem is formula will not
correctly
match the selections after I get to entering Thursday in H:3 please
help
some
more?

=index(sheet2!$A$1:$D$50,match(sheet1!$E$11,index( sheet2!$A$1:$D$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$D$50,1,),0))

more re index and match
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
SWEET!!!! your help is so much appreciated... This will eliminate probably
half hour of data entry every monday.. Thank you!

"T. Valko" wrote:

Try this:

I3 = some date like 10/27/2008

Enter this formula in H3:

=TEXT(I3,"dddd")

That will return the long weekday name like Monday. If you want the short
weekday name like Mon just remove one of the d's.

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
yes this one did finally work.. one more query for you.. H3 is the day
of
the
week.. eg sunday thru Saturday I'd like to enter the actual date in the
cell
next I 3 then have the day of the week pop up in H3 but that spoils
the
match formula.. any ideas or advice on how I could accomplish this?

"T. Valko" wrote:

Try this:

=INDEX(Sheet2!$A$1:$N$57,MATCH(Sheet1!$E$11,Sheet2 !$A$1:$A$57,0),MATCH(Sheet1!$H$3,Sheet2!$A$1:$N$1, 0))

--
Biff
Microsoft Excel MVP


"kathy" wrote in message
...
Was sent this formula by Double Z.. one issue with it... on my sheet
2
my
array is actually A1:N57
to match to the drop down list in E 11 on sheet 2( I am using info
From
A1:A57 store names sheet 2) then indexing same to match the date H3
sheet1
so that the info for Route # needed in I 13( Sunday to Saturday
stored
in
H1:N57 on sheet two)will populate... problem is formula will not
correctly
match the selections after I get to entering Thursday in H:3 please
help
some
more?

=index(sheet2!$A$1:$D$50,match(sheet1!$E$11,index( sheet2!$A$1:$D$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$D$50,1,),0))