# Look up values - in range

November 2, 2010 at 04:39:34
Specs: Windows XP
 Dear allI request you to kindly help me…because it helps me a lot in saving time….i give small description of the problem…I have an excel sheet where one column is having names of the employees and each employee is allotted different duties for thirty days (A2:A30 – names of the employees, B2-AD2 – dates of the month, B2:AD30 – allocation of duties to employees)Now I want the following: If u punch the date in a cell, it has to give you duties of the allocation of the employees on that day in other sheet….in a different sheet….I hope my explanation gives u understanding of the problem.Pl help AND THANK URegdsamar

See More: Look up values - in range

#1
November 2, 2010 at 05:52:18
 re: "(A2:A30 – names of the employees, B2-AD2 – dates of the month, B2:AD30 – allocation of duties to employees)"I'm confused.How can B2 - AD2 contain dates and B2:AD2 (which is part or B2:AD30) also contain "allocation of duties to employees"?

Report •

#2
November 3, 2010 at 00:47:27
 sirxtremely sorry for goof up....its actually B3:AD30 not as mentioned earlier.pl help sirregdsamar

Report •

#3
November 3, 2010 at 09:01:02
 If your dates are in Row 2, then I have to assume that your names actually begin in Row 3.I also assume that you have a matching list of names in Column A of Sheet2 and that you want the duties for the entered date to appear next to each name (Column B)You don't say where you want to enter the date in Sheet2, so I'm going to assume A1.In Sheet2!B3 enter this formula:=OFFSET(Sheet1!A3,0,MATCH(\$A\$1,Sheet1!\$B\$2:\$AD\$2))Drag it down to the bottom of your list of names.This will use the MATCH function to return the column number within Sheet1!B2:AD2 where the date entered in Sheet2!A1 is found.It will then use that value as the column offset form Column A and return the value in that cell.For example, if 10/23/2010 is found in D2, that is the 3rd column of the B2:AD2 range and it will return the values in Column D.

Report •

Related Solutions

#4
November 3, 2010 at 10:26:30
 SirThank u very much....it worked....its really wonderful...thank u very much sir...it made my work easier....once again my happy thanksregdsamar

Report •