Automatically Insert cell Range Or drag and drop formula

0

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

 
Answer
Discuss

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: 1442) 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
Add to Discussion

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!B3

Then 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?

Discuss

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?
=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: 1442) Aug 29, '16 at 3:09 pm
Add to Discussion

Answer the Question

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