1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007 Linking & Formula Update

Discussion in 'Business Applications' started by csanders3, Jan 18, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. csanders3

    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?
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    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?)
     
  3. csanders3

    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.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    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.
     
  5. csanders3

    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.
     
  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Just make sure it is always 49 rows apart.
     

    Attached Files:

  7. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    With macro filling.
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    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.
     
  9. Garf13LD

    Garf13LD

    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:
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I have a littlle tool to manage that :)
     
  11. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085832

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice