Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Attendance sheet

0

hi

am doing monthly attendance using excel. intially i have only small number of people so that i can manage with each excel sheet , nowadays the manpower getting increased.

My idea is, in the first sheet, would like to enter the name of the employee and it should fetch the name and provide the required details like total no of days leave and the OT hours.

whereas now am doing for each employee am having seperate sheet with the above details but to search for individual employees i am using hyperlink 

is there any way where i can make it simple and time saving to enter the required details in the first sheet like Name, In-time, outtime, Total hrs worked, standard hrs, OT  and from this would like to derive no of days present, absence and OT Hrs 

Thanks

Madhan 

Answer
Discuss

Discussion

Consider using VLOOKUP instead of hyperlinks. The lookup value could be the name, but normally one would use unique employee numbers for that purpose.
Variatus (rep: 4889) Apr 24, '17 at 5:54 am
okay thanks but i would like to fetch the required details from the sheets, where i have created a data base for each employees... i think using macro or some formulae it can be done
do you have any idea????
Madhan  
Madhan Apr 24, '17 at 6:11 am
Add to Discussion

Answers

0

You can look up values from another sheet using VLOOKUP, for example,

=VLOOKUP("O/T",'Jack the Ripper'!C3:G8,4)
Discuss
0

If you use a different sheet for each employee (bad idea btw), then you can use a vlookup formula like this:

=VLOOKUP(1,INDIRECT(A2 & "!A1:B3"),2,FALSE)

In this case cell A2 should contain the name of the employee if that is the same as the name of the worksheet that has that employees data. (Cell A2 must contain the name of the worksheet that has the data that you want to return.)

This technique is achieved using the INDIRECT() function.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login