Excel 2007 Linking & Formula Update

[csanders3]

I have an attendance sheet that I need to copy for 400 employees. I have linked it to database that I imported and filtered. I am only extracting name, id, fte and hire date. On my timesheet tab I am copying and pasting a new sheet for each employee, but when I copy a new sheet, the formula updates by linking to the new position based on copied/pasted line, instead I want a formula to pick up next employee and each one after. See attached (i.e., look in formula bar on the record I'ved copied, and see how it reads ='Employee List'!$F51, instead I need it to pick up ='Employee List'!F3, then on the next copied timesheet, ='Employee List'!F4. But instead it grabs whatever line I start on. What is a way to do this? Also is there an easier way than copying 400 employee timesheets (every 50 lines), than what I'm doing?

 

[Keebellah]

Hi, welcome to the forum.
When you copy and paste andthere are formulas that play a rol then they copy and use the relative address,
In this case you copu F2 down 49 line so F2 = F2 + 49 -> F51

There is no way to do it otherwise unless you write a macro that copies each sheet and the the next row in your employee sheet.

Another way is to use one sheet and make surte the formula is all set to $F$2 and $G$2 etc.

After you copy the sheet x times then in each sheet you increment the row so you will still need to change a number of cells to correct the formula but then it will work.

A Macro could take care of this but that will require programming.

BTW why is your file still in xls format and not xlsx (if you're using 2007?)

 

[csanders3]

Thank you for your prompt response. I will try to write a macro that will copy each sheet as suggested. Even though I am working in 2007 I save my files in xls because some of the people that will need to access this report still have Excel 2003, and no compability on their computers so I always save in this to ensure opening.

 

[Keebellah]

You must keep this in mind if you use macros that sort data because the default sorting in 2007 and 2010 is not compatible.
If you need some help just say so.

 

[csanders3]

Of course I desparately need help, even with writing the macro, as I have been researching a resolution for this for about a week or so. But I'm learning everyday as I encounter new challenges. I generally go online & find youtube and Lynda.com examples, but I'm stuck so far which is why I entered the forum.

 

[Garf13LD]

Just make sure it is always 49 rows apart.

 

[Garf13LD]

With macro filling.

 

[Keebellah]

Hi, the macro does a good job, but if I read the first posting "copying and pasting a new sheet for each employee" the macro, with a small change can create a sepate sheet for each Employee and the sheet's name could be the employee's name.
If you have more than 256 employees then excel 2003 is limited to 254 employees allowing for the employee list and a blank employee sheet that van be used as template.

 

[Garf13LD]

From his example, he is listing all in one sheet.
I doubt he wants to manage 400 tabs.

 

[Keebellah]

I have a littlle tool to manage that