Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
Hi.. need help to figure out how to:
two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
Kathy,
I obviously don't know the specifics of the layout of your other sheet, but a combination of the INDEX and MATCH functions can do the trick (as you alluded to in your post title). Let's say the sheet containing E11 and H3 is sheet1, and the other sheet is sheet2, and your list in sheet 2 is in the array $A$1:$D$50, the something like this should work: =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)) It is pretty messy, but I hope that helps. DoubleZ "kathy" wrote: Hi.. need help to figure out how to: two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
...thanks so much this does work.. I just need one more piece of advice.. for
the date I had to split off the day of the week from the actual date.. eg. H3 has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4 monday can populate in H3? "kathy" wrote: Hi.. need help to figure out how to: two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
If H4 is a real date, rather than text, then H3 can be =H4, formatted as
dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if you want H3 to be text. -- David Biddulph "kathy" wrote in message ... ..thanks so much this does work.. I just need one more piece of advice.. for the date I had to split off the day of the week from the actual date.. eg. H3 has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4 monday can populate in H3? "kathy" wrote: Hi.. need help to figure out how to: two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
problem after this though it nullifies the index match from cell I13
"David Biddulph" wrote: If H4 is a real date, rather than text, then H3 can be =H4, formatted as dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if you want H3 to be text. -- David Biddulph "kathy" wrote in message ... ..thanks so much this does work.. I just need one more piece of advice.. for the date I had to split off the day of the week from the actual date.. eg. H3 has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4 monday can populate in H3? "kathy" wrote: Hi.. need help to figure out how to: two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
You know what you're trying to match, but we don't.
-- David Biddulph "kathy" wrote in message ... problem after this though it nullifies the index match from cell I13 "David Biddulph" wrote: If H4 is a real date, rather than text, then H3 can be =H4, formatted as dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if you want H3 to be text. -- David Biddulph "kathy" wrote in message ... ..thanks so much this does work.. I just need one more piece of advice.. for the date I had to split off the day of the week from the actual date.. eg. H3 has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4 monday can populate in H3? "kathy" wrote: Hi.. need help to figure out how to: two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
ok we'll start over...as per previous query I needed cell I 13 on sheet 1 to
reference cell E11 on sheet one which drew ifs info from a drop down list on sheet 2 A1:A57 and the day of the week in H3 on sheet 1 and return a route # from sheet 2..H1:N57 so the formula below was provided and it is working upto input dates of Thursday after which it will not provide the route number.. my query was if I have to type in the day of the week for the reference is there any way to have a date formulated cell provide that as each sheet has to be dated with specifics say Oct 10 then friday to give me the correct route #.. sorry it is really hard to put this on paper but hopefully you can understand what I need.. "David Biddulph" wrote: You know what you're trying to match, but we don't. -- David Biddulph "kathy" wrote in message ... problem after this though it nullifies the index match from cell I13 "David Biddulph" wrote: If H4 is a real date, rather than text, then H3 can be =H4, formatted as dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if you want H3 to be text. -- David Biddulph "kathy" wrote in message ... ..thanks so much this does work.. I just need one more piece of advice.. for the date I had to split off the day of the week from the actual date.. eg. H3 has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4 monday can populate in H3? "kathy" wrote: Hi.. need help to figure out how to: two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
No, I don't understand what you're trying to do, but I thought that the
question was how to get the day of the week, given a date in another cell. That was what I answered. If someone else has a different understanding as to what you are trying to ask, they will hopefully answer. -- David Biddulph "kathy" wrote in message ... ok we'll start over...as per previous query I needed cell I 13 on sheet 1 to reference cell E11 on sheet one which drew ifs info from a drop down list on sheet 2 A1:A57 and the day of the week in H3 on sheet 1 and return a route # from sheet 2..H1:N57 so the formula below was provided and it is working upto input dates of Thursday after which it will not provide the route number.. my query was if I have to type in the day of the week for the reference is there any way to have a date formulated cell provide that as each sheet has to be dated with specifics say Oct 10 then friday to give me the correct route #.. sorry it is really hard to put this on paper but hopefully you can understand what I need.. "David Biddulph" wrote: You know what you're trying to match, but we don't. -- David Biddulph "kathy" wrote in message ... problem after this though it nullifies the index match from cell I13 "David Biddulph" wrote: If H4 is a real date, rather than text, then H3 can be =H4, formatted as dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if you want H3 to be text. -- David Biddulph "kathy" wrote in message ... ..thanks so much this does work.. I just need one more piece of advice.. for the date I had to split off the day of the week from the actual date.. eg. H3 has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4 monday can populate in H3? "kathy" wrote: Hi.. need help to figure out how to: two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index match???
Thanks David.. there was previous history to what I needed to accomplish
related to that cell and then this ..so will continue.. "David Biddulph" wrote: No, I don't understand what you're trying to do, but I thought that the question was how to get the day of the week, given a date in another cell. That was what I answered. If someone else has a different understanding as to what you are trying to ask, they will hopefully answer. -- David Biddulph "kathy" wrote in message ... ok we'll start over...as per previous query I needed cell I 13 on sheet 1 to reference cell E11 on sheet one which drew ifs info from a drop down list on sheet 2 A1:A57 and the day of the week in H3 on sheet 1 and return a route # from sheet 2..H1:N57 so the formula below was provided and it is working upto input dates of Thursday after which it will not provide the route number.. my query was if I have to type in the day of the week for the reference is there any way to have a date formulated cell provide that as each sheet has to be dated with specifics say Oct 10 then friday to give me the correct route #.. sorry it is really hard to put this on paper but hopefully you can understand what I need.. "David Biddulph" wrote: You know what you're trying to match, but we don't. -- David Biddulph "kathy" wrote in message ... problem after this though it nullifies the index match from cell I13 "David Biddulph" wrote: If H4 is a real date, rather than text, then H3 can be =H4, formatted as dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if you want H3 to be text. -- David Biddulph "kathy" wrote in message ... ..thanks so much this does work.. I just need one more piece of advice.. for the date I had to split off the day of the week from the actual date.. eg. H3 has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4 monday can populate in H3? "kathy" wrote: Hi.. need help to figure out how to: two worksheets in same book.. one sheet has list for drop down menu the other has an invoice.. on the invoice I need cell I 13 which is to be a route # to reference E 11 (which has name chosen from a drop down list) and then match the day of the week from the date cell H3.. and return the route number from either an exact match from list on other sheet or allow me to choose from a second drop down list eliminating the need to match the date in above scenario.. hope this makes sense and someone can help.. am new to this stuff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VlOOKUP/MATCH/INDEX | Excel Worksheet Functions | |||
vlookup / index / match? | Excel Worksheet Functions | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
vlookup, match/index ???? | Excel Worksheet Functions | |||
VLookup or Index Match ? | Excel Worksheet Functions |