Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Items
I could use some help with following problem. I am having problems getting
correct formula and normally there are people much smarter then I in the discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1) A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 3 Mr. Someone 327 anywhere Drver numbers are blank On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers numbers already filled in) I have this set up as a list. A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 37 3 Mr. Someone 327 anywhere 39 What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it. I appreciate any help I can get with this problem. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Items
On Sheet1, enter the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, in C2 and copy down: =INDEX(Sheet3!$C$2:$C$3,MATCH(1,(Sheet3!$A$2:$A$3= A2)*(Sheet3!$B$2:$B$3=B 2),0)) or =IF(ISNUMBER(MATCH(1,(Sheet3!$A$2:$A$3=A2)*(Sheet3 !$B$2:$B$3=B2),0)),INDE X(Sheet3!$C$2:$C$3,MATCH(1,(Sheet3!$A$2:$A$3=A2)*( Sheet3!$B$2:$B$3=B2),0) ),"") Change the sheet references and ranges accordingly. Hope this helps! In article , "Smish" wrote: I could use some help with following problem. I am having problems getting correct formula and normally there are people much smarter then I in the discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1) A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 3 Mr. Someone 327 anywhere Drver numbers are blank On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers numbers already filled in) I have this set up as a list. A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 37 3 Mr. Someone 327 anywhere 39 What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it. I appreciate any help I can get with this problem. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Items
Maybe its the way it shows up in the post but the A B C are actually over the
Name, Origin, and Driver columns. This formula doesn't work for what I am trying to do though. Every day I get a manifest with approx 300 deliveries to be made. I already know from previous weeks who is going to do the deliveries and have that information in sheet 3. What I am trying to do is use is use a formula if possible in sheet 1 column c to basically see if on sheet 5 a & b match the a & b on sheet 1 it will pull the driver number to sheet 1. "Domenic" wrote: On Sheet1, enter the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, in C2 and copy down: =INDEX(Sheet3!$C$2:$C$3,MATCH(1,(Sheet3!$A$2:$A$3= A2)*(Sheet3!$B$2:$B$3=B 2),0)) or =IF(ISNUMBER(MATCH(1,(Sheet3!$A$2:$A$3=A2)*(Sheet3 !$B$2:$B$3=B2),0)),INDE X(Sheet3!$C$2:$C$3,MATCH(1,(Sheet3!$A$2:$A$3=A2)*( Sheet3!$B$2:$B$3=B2),0) ),"") Change the sheet references and ranges accordingly. Hope this helps! In article , "Smish" wrote: I could use some help with following problem. I am having problems getting correct formula and normally there are people much smarter then I in the discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1) A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 3 Mr. Someone 327 anywhere Drver numbers are blank On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers numbers already filled in) I have this set up as a list. A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 37 3 Mr. Someone 327 anywhere 39 What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it. I appreciate any help I can get with this problem. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Items
What I am trying to do is use is use a formula if
possible in sheet 1 column c to basically see if on sheet 5 a & b match the a & b on sheet 1 it will pull the driver number to sheet 1. I take it you meant sheet 3, not sheet 5, right? Here's what I assumed... For your sheet 1... The sheet name is Sheet1 A1:C1 contains Name, Origin, and Driver # A2:B3 contains your data For your sheet 3... The sheet name is Sheet3 A1:C1 contains Name, Origin, and Driver # A2:C3 contains your data Then, on Sheet1, the following formula is entered in C2 and copied down: =INDEX(Sheet3!$C$2:$C$3,MATCH(1,(Sheet3!$A$2:$A$3= Sheet1!A2)*(Sheet3!$B$2 :$B$3=Sheet1!B2),0)) Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. That means, instead of pressing just ENTER, press ENTER while both the CONTROL and SHIFT keys are pressed down. Excel will automatically place braces {} around the formula indicating that you've entered the formula correctly. Does this help? In article , "Smish" wrote: Maybe its the way it shows up in the post but the A B C are actually over the Name, Origin, and Driver columns. This formula doesn't work for what I am trying to do though. Every day I get a manifest with approx 300 deliveries to be made. I already know from previous weeks who is going to do the deliveries and have that information in sheet 3. What I am trying to do is use is use a formula if possible in sheet 1 column c to basically see if on sheet 5 a & b match the a & b on sheet 1 it will pull the driver number to sheet 1. "Domenic" wrote: On Sheet1, enter the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, in C2 and copy down: =INDEX(Sheet3!$C$2:$C$3,MATCH(1,(Sheet3!$A$2:$A$3= A2)*(Sheet3!$B$2:$B$3=B 2),0)) or =IF(ISNUMBER(MATCH(1,(Sheet3!$A$2:$A$3=A2)*(Sheet3 !$B$2:$B$3=B2),0)),INDE X(Sheet3!$C$2:$C$3,MATCH(1,(Sheet3!$A$2:$A$3=A2)*( Sheet3!$B$2:$B$3=B2),0) ),"") Change the sheet references and ranges accordingly. Hope this helps! In article , "Smish" wrote: I could use some help with following problem. I am having problems getting correct formula and normally there are people much smarter then I in the discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1) A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 3 Mr. Someone 327 anywhere Drver numbers are blank On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers numbers already filled in) I have this set up as a list. A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 37 3 Mr. Someone 327 anywhere 39 What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it. I appreciate any help I can get with this problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Make typing "jump" to matching item(s) in drop-down list? | Excel Discussion (Misc queries) | |||
Selecting matching items in a column | Excel Discussion (Misc queries) | |||
multiple items in database | Excel Worksheet Functions | |||
Count items between specific hours on a matching date | Excel Worksheet Functions |