Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in different workbooks with dynamic ranges
Hello,
I'm trying to make a formula which looks for a certain value in another workbook. My worksheet in which the formula has to be entered contains the payroll number and on the basis of this payrollnumber and the code (101,140,...) a value of a certain month needs to be returned from the second workbook. This second workbook has a layout which looks like this : A B C D E F G H name first name payrollnr jan feb mrch apr may smith john 001/02 101 10 10 20 30 25 135 16 25 45 10 36 140 ... 145 johnson kelly 001/02 101 12 32 25 21 15 135 ... 140 145 Can anyone help me as i'm struggling with index, match and vlookup to make it somehow right ? thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in different workbooks with dynamic ranges
Assuming *every employee has the same payroll number and the payroll number
is **always** in the exact same sequence*. A2:A13 = first name B2:B13 = last name C2:C13 = payroll number D2:D13 = payroll data D1:H1 = column headers of month names Lookup values: A17 = last name B17 = first name C17 = payroll number D17 = month name Try this array formula** : =INDEX(D2:H13,MATCH(1,(A2:A13=A17)*(B2:B13=B17),0) +MATCH(C17,C2:C13,0)-1,MATCH(D17,D1:H1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "steph" wrote in message ... Hello, I'm trying to make a formula which looks for a certain value in another workbook. My worksheet in which the formula has to be entered contains the payroll number and on the basis of this payrollnumber and the code (101,140,...) a value of a certain month needs to be returned from the second workbook. This second workbook has a layout which looks like this : A B C D E F G H name first name payrollnr jan feb mrch apr may smith john 001/02 101 10 10 20 30 25 135 16 25 45 10 36 140 ... 145 johnson kelly 001/02 101 12 32 25 21 15 135 ... 140 145 Can anyone help me as i'm struggling with index, match and vlookup to make it somehow right ? thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in different workbooks with dynamic ranges
Hello Biff,
The data i entered was not really complete. Meaning that every personnel member has its own unique member and does not have the same number (as mentionned in my example) . So what i want in my calculation sheet is f.i. for payrollnumber 001/02 the total of code 140 in the month of april. So i first have to define the dynamic range of the personel member and then in this range xl must look for the number of a specific code, i guess ? regards steph "T. Valko" wrote: Assuming *every employee has the same payroll number and the payroll number is **always** in the exact same sequence*. A2:A13 = first name B2:B13 = last name C2:C13 = payroll number D2:D13 = payroll data D1:H1 = column headers of month names Lookup values: A17 = last name B17 = first name C17 = payroll number D17 = month name Try this array formula** : =INDEX(D2:H13,MATCH(1,(A2:A13=A17)*(B2:B13=B17),0) +MATCH(C17,C2:C13,0)-1,MATCH(D17,D1:H1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "steph" wrote in message ... Hello, I'm trying to make a formula which looks for a certain value in another workbook. My worksheet in which the formula has to be entered contains the payroll number and on the basis of this payrollnumber and the code (101,140,...) a value of a certain month needs to be returned from the second workbook. This second workbook has a layout which looks like this : A B C D E F G H name first name payrollnr jan feb mrch apr may smith john 001/02 101 10 10 20 30 25 135 16 25 45 10 36 140 ... 145 johnson kelly 001/02 101 12 32 25 21 15 135 ... 140 145 Can anyone help me as i'm struggling with index, match and vlookup to make it somehow right ? thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in different workbooks with dynamic ranges
Hi,
sorry meant that every staff has its own unique number ! "steph" wrote: Hello Biff, The data i entered was not really complete. Meaning that every personnel member has its own unique member and does not have the same number (as mentionned in my example) . So what i want in my calculation sheet is f.i. for payrollnumber 001/02 the total of code 140 in the month of april. So i first have to define the dynamic range of the personel member and then in this range xl must look for the number of a specific code, i guess ? regards steph "T. Valko" wrote: Assuming *every employee has the same payroll number and the payroll number is **always** in the exact same sequence*. A2:A13 = first name B2:B13 = last name C2:C13 = payroll number D2:D13 = payroll data D1:H1 = column headers of month names Lookup values: A17 = last name B17 = first name C17 = payroll number D17 = month name Try this array formula** : =INDEX(D2:H13,MATCH(1,(A2:A13=A17)*(B2:B13=B17),0) +MATCH(C17,C2:C13,0)-1,MATCH(D17,D1:H1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "steph" wrote in message ... Hello, I'm trying to make a formula which looks for a certain value in another workbook. My worksheet in which the formula has to be entered contains the payroll number and on the basis of this payrollnumber and the code (101,140,...) a value of a certain month needs to be returned from the second workbook. This second workbook has a layout which looks like this : A B C D E F G H name first name payrollnr jan feb mrch apr may smith john 001/02 101 10 10 20 30 25 135 16 25 45 10 36 140 ... 145 johnson kelly 001/02 101 12 32 25 21 15 135 ... 140 145 Can anyone help me as i'm struggling with index, match and vlookup to make it somehow right ? thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in different workbooks with dynamic ranges
Ok, now I'm officially confused!
-- Biff Microsoft Excel MVP "steph" wrote in message ... Hi, sorry meant that every staff has its own unique number ! "steph" wrote: Hello Biff, The data i entered was not really complete. Meaning that every personnel member has its own unique member and does not have the same number (as mentionned in my example) . So what i want in my calculation sheet is f.i. for payrollnumber 001/02 the total of code 140 in the month of april. So i first have to define the dynamic range of the personel member and then in this range xl must look for the number of a specific code, i guess ? regards steph "T. Valko" wrote: Assuming *every employee has the same payroll number and the payroll number is **always** in the exact same sequence*. A2:A13 = first name B2:B13 = last name C2:C13 = payroll number D2:D13 = payroll data D1:H1 = column headers of month names Lookup values: A17 = last name B17 = first name C17 = payroll number D17 = month name Try this array formula** : =INDEX(D2:H13,MATCH(1,(A2:A13=A17)*(B2:B13=B17),0) +MATCH(C17,C2:C13,0)-1,MATCH(D17,D1:H1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "steph" wrote in message ... Hello, I'm trying to make a formula which looks for a certain value in another workbook. My worksheet in which the formula has to be entered contains the payroll number and on the basis of this payrollnumber and the code (101,140,...) a value of a certain month needs to be returned from the second workbook. This second workbook has a layout which looks like this : A B C D E F G H name first name payrollnr jan feb mrch apr may smith john 001/02 101 10 10 20 30 25 135 16 25 45 10 36 140 ... 145 johnson kelly 001/02 101 12 32 25 21 15 135 ... 140 145 Can anyone help me as i'm struggling with index, match and vlookup to make it somehow right ? thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in different workbooks with dynamic ranges
Sorry !
I'll try to explain it as good as possible. In 1 workbook i have a line per payroll member which is known by its unique payroll number. This payroll number is also used in the second worbook which contains all the relevant payroll data of a certain period. What i now want to do is to retrieve the amounts of a certain person in a certain period. However considering the set-up of workbook 2 as stipulated below, i need to make a dynamic range on the basis of payroll number. Is this more clear ? "T. Valko" wrote: Ok, now I'm officially confused! -- Biff Microsoft Excel MVP "steph" wrote in message ... Hi, sorry meant that every staff has its own unique number ! "steph" wrote: Hello Biff, The data i entered was not really complete. Meaning that every personnel member has its own unique member and does not have the same number (as mentionned in my example) . So what i want in my calculation sheet is f.i. for payrollnumber 001/02 the total of code 140 in the month of april. So i first have to define the dynamic range of the personel member and then in this range xl must look for the number of a specific code, i guess ? regards steph "T. Valko" wrote: Assuming *every employee has the same payroll number and the payroll number is **always** in the exact same sequence*. A2:A13 = first name B2:B13 = last name C2:C13 = payroll number D2:D13 = payroll data D1:H1 = column headers of month names Lookup values: A17 = last name B17 = first name C17 = payroll number D17 = month name Try this array formula** : =INDEX(D2:H13,MATCH(1,(A2:A13=A17)*(B2:B13=B17),0) +MATCH(C17,C2:C13,0)-1,MATCH(D17,D1:H1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "steph" wrote in message ... Hello, I'm trying to make a formula which looks for a certain value in another workbook. My worksheet in which the formula has to be entered contains the payroll number and on the basis of this payrollnumber and the code (101,140,...) a value of a certain month needs to be returned from the second workbook. This second workbook has a layout which looks like this : A B C D E F G H name first name payrollnr jan feb mrch apr may smith john 001/02 101 10 10 20 30 25 135 16 25 45 10 36 140 ... 145 johnson kelly 001/02 101 12 32 25 21 15 135 ... 140 145 Can anyone help me as i'm struggling with index, match and vlookup to make it somehow right ? thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in different workbooks with dynamic ranges
Sorry, still not following you on this. If I could see how your data is set
up out I might be able to figure something out. Can you put together a *small* sample file (of dummy data) that demonstrates what you're trying to do? If you want to do that you can send it to me at: xl can help at comcast period net Remove "can", remove the spaces and change the obvious. -- Biff Microsoft Excel MVP "steph" wrote in message ... Sorry ! I'll try to explain it as good as possible. In 1 workbook i have a line per payroll member which is known by its unique payroll number. This payroll number is also used in the second worbook which contains all the relevant payroll data of a certain period. What i now want to do is to retrieve the amounts of a certain person in a certain period. However considering the set-up of workbook 2 as stipulated below, i need to make a dynamic range on the basis of payroll number. Is this more clear ? "T. Valko" wrote: Ok, now I'm officially confused! -- Biff Microsoft Excel MVP "steph" wrote in message ... Hi, sorry meant that every staff has its own unique number ! "steph" wrote: Hello Biff, The data i entered was not really complete. Meaning that every personnel member has its own unique member and does not have the same number (as mentionned in my example) . So what i want in my calculation sheet is f.i. for payrollnumber 001/02 the total of code 140 in the month of april. So i first have to define the dynamic range of the personel member and then in this range xl must look for the number of a specific code, i guess ? regards steph "T. Valko" wrote: Assuming *every employee has the same payroll number and the payroll number is **always** in the exact same sequence*. A2:A13 = first name B2:B13 = last name C2:C13 = payroll number D2:D13 = payroll data D1:H1 = column headers of month names Lookup values: A17 = last name B17 = first name C17 = payroll number D17 = month name Try this array formula** : =INDEX(D2:H13,MATCH(1,(A2:A13=A17)*(B2:B13=B17),0) +MATCH(C17,C2:C13,0)-1,MATCH(D17,D1:H1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "steph" wrote in message ... Hello, I'm trying to make a formula which looks for a certain value in another workbook. My worksheet in which the formula has to be entered contains the payroll number and on the basis of this payrollnumber and the code (101,140,...) a value of a certain month needs to be returned from the second workbook. This second workbook has a layout which looks like this : A B C D E F G H name first name payrollnr jan feb mrch apr may smith john 001/02 101 10 10 20 30 25 135 16 25 45 10 36 140 ... 145 johnson kelly 001/02 101 12 32 25 21 15 135 ... 140 145 Can anyone help me as i'm struggling with index, match and vlookup to make it somehow right ? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges | Excel Worksheet Functions | |||
Formula To Use A Dynamic Named Ranges With VLOOKUP On Different Workbooks | Excel Worksheet Functions | |||
can VLOOKUP use dynamic named ranges? | Excel Discussion (Misc queries) | |||
dynamic, double vlookup, match, index, dget?? different workbooks | Excel Worksheet Functions | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions |