Excel 2007 Linking & Formula Update

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

csanders3

Thread Starter
Joined
Jan 18, 2013
Messages
3
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?
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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

Thread Starter
Joined
Jan 18, 2013
Messages
3
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

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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

Thread Starter
Joined
Jan 18, 2013
Messages
3
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 
Joined
Apr 17, 2012
Messages
455
From his example, he is listing all in one sheet.
I doubt he wants to manage 400 tabs.
:cool:
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top