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
|