Hi Guys
I need your assistance, i`m trying to create an employee attendance register which automatically calculates the employees wage as i complete the attendance register daily
i have already designed the attendance register (sheet 1) and wage calculation table (sheet 2) in a excel document.
when i add a new employee to the attendance register the wage calculation table must be automatically inserted on sheet 2 for that employee, is this possible
Alternatively can someone help me design a formula like this =IF(MOD(ROW()-4,8)=0, INDIRECT("Sheet1!A" & 8+((ROW()-4)/8)-2), "")which i found here: http://www.mrexcel.com/forum/excel-questions/410244-dragging-down-formulas-skip-8-rows-reference-another-sheet.html
my excel file is attached
your assistance will be highly apprciated
Thanking You
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.)
Automatically Insert cell Range Or drag and drop formula
0
Discussion
Are you open to changing the format of the Wage sheet? Changing the structure a bit would go a long way to solving your issues.
don (rep: 1989) Aug 18, '16 at 12:34 am
I thank you for your reply, I am willing to change the structure as long as I dont lose any of the necessary information
nadeem1 Aug 18, '16 at 2:16 pm
Answers
0
If you want the employee info to copy over you can put a formula like this into cell A2 on the Wage sheet:
=Attendance!B3Then copy it down. This would remove your setup for row 13 but would make it easy to always have the list of employees.
Or, did you need a separate table on the Wage sheet for EACH employee?
Discussion
i thank you for your reply, i require a seperate table on the wage sheet for each employee, even if there is a drag and drop formula which i can put in the wage table (cell with employees name) so that when i drag the table down it automaticcally inserts the next employee`s name into the cell. i have seen something similar here: http://www.mrexcel.com/forum/excel-questions/410244-dragging-down-formulas-skip-8-rows-reference-another-sheet.html
nadeem1 Aug 28, '16 at 2:10 am
Ok, so, things like at that link ARE possible. But doing this on a large scale is a bitch and a half and, even if you get it working right now, it will be so confusing that you will forget how to change anything at a future date and the spreadsheet will become useless.
For instance, is this kind of formula (taken from the link you provided) easy for you to understand?
You need to come up with another method for managing this data. I would try to get everything put onto one line per employee since it looks like you "step-down" the data currently.
For instance, is this kind of formula (taken from the link you provided) easy for you to understand?
=IF(MOD(ROW()-8,8)=0,INDIRECT("Sheet1!b"&8+((ROW()-8)/8)-2),"")You need to come up with another method for managing this data. I would try to get everything put onto one line per employee since it looks like you "step-down" the data currently.
don (rep: 1989) Aug 29, '16 at 3:09 pm