lOOK UP VALUES

[Srinivas]

i HAVE SHEET LIKE THIS FILE NAME AS ADVANCE FILE


NAME JAN FEB MAR APR JUN JUL AUG ...............

JOHN 100 200 300 400 200 200 200
KATE 200 200 200 200 200 200 200
LAURAL 200 200 300 300 300 300 300

IN PAY ROLL FILE I WANT TO DEDUCT THE ADVANCES

MY SYSTEM SHOULD TAKE AUTOMATICALLY THE VALUES IF I TYPE JAN IT SHOULD TAKE
JAN VALUES AGAINST JOHN, KATE AND LAURAL IF FEB, FEB VALUES AND MAR, MARCH
VALUES.......... ETC

CAN ANY ONE HELP ME IN THIS ........PLEASE


SRINIVAS

 

[Pete_UK]

If Advance is the name of the sheet in the same file, then using
another sheet you can use B1 to enter JAN and A2 to enter the name,
and then use this formula to get the amount:

=INDEX(Advance!$A$1:$M$20,(MATCH($A2,Advance$A$1:$A$20,0),MATCH(B
$2,Advance!$A$1:$M$1,0))

If the advances are in a separate file then you will need to include
the full path and filename. It is easier to do this in a new sheet in
the advances file like above, and then move the sheet (CTRL-drag) out
of that file into another one, and the formulae will automatically
adjust.

By the way, please do not SHOUT in future postings - it is considered
rude.

Hope this helps.

Pete