Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lOOK UP VALUES
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 -- srinivas |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lOOK UP VALUES
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 On Oct 8, 7:58*am, Srinivas wrote: 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 -- srinivas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Advanced Filter for Values in Column M greater than Values in Colu | Excel Discussion (Misc queries) | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) |