Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
please help
Lets say you have a typical Microsoft excel sheet and on the bottom you have
standard table. You have 5 rows vertically on the left of job codes, like (1-engineering) (2-coordinating) (3-support) (etc). And five columns horizontal on top of building floors, like (1st floor) (2nd floor) (3rd floor) (etc). And above that whole table you have a simple time sheet layout consisting of 4 cells running horizontal as follows Date , floor level , job code, and hours , I want to see if it is possible to input the information on the time sheet like (2) for floor level and (3) for job code and it will know what cell to reference on the table below with the hours.. ive been trying to figure this out for some time , please help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
please help
dave wrote:
Lets say you have a typical Microsoft excel sheet and on the bottom you have standard table. You have 5 rows vertically on the left of job codes, like (1-engineering) (2-coordinating) (3-support) (etc). And five columns horizontal on top of building floors, like (1st floor) (2nd floor) (3rd floor) (etc). And above that whole table you have a simple time sheet layout consisting of 4 cells running horizontal as follows Date , floor level , job code, and hours , I want to see if it is possible to input the information on the time sheet like (2) for floor level and (3) for job code and it will know what cell to reference on the table below with the hours.. ive been trying to figure this out for some time , please help INDEX/MATCH is the perfect combination for a 2-dimensional lookup. Let's say your "table below" resides in A30:F35, with job codes 1-5 in A31:F35 and floor codes 1-5 in B30:F30, and the data in B31:F35. Let's also assume the time sheet is in A1:D10, with headers Date, Floor, Job, Hours, all in row 1. You have entered values in A2, B2, and C2 and want to fetch the hours from the "table below" in D2: =INDEX($B$31:$F$35,MATCH(B2,$B$30:$F$30,0),MATCH(C 2,$A$31:$A$35,0)) Fill this down as needed. Ten minutes studying one of these articles might also help understanding of INDEX/MATCH: http://www.mrexcel.com/articles/exce...ndex-match.php http://contextures.com/xlFunctions03.html Hope this helps! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|