+ Reply to Thread
Results 1 to 5 of 5

Need to copy a formula that automatically changes selected sheet in formula

  1. #1
    Registered User
    Join Date
    02-06-2012
    Location
    Youngsville, LA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Exclamation Need to copy a formula that automatically changes selected sheet in formula

    I have a workbook that I have over 50 worksheets in. One for each employee that lists their hours during each payperiod and calculates their total hours worked. I would like to compile this into a summary worksheet that has each employee in a list and have only their total hours listed next to their name. Each employee sheet is formatted exactly the same with their name in cell A1 and the sum of their total hours worked in cell C40. To create a formula that automattically transfers this information into the summary sheet, I would have to put "='Doe, John'!A1" in the employee column (A) and "='Doe, John'!C40" in the Total Hours Worked column (B). Is there anyway that I could simply copy the formula from the first cell in the column to the cells beneth and have excel automatically change the sheet name to the next sheet in the workbook. It would take forever to edit each cell. If I have to do that, I might as well just go in and type all the information myself from each worksheet. PLEASE HELP!!!!

  2. #2
    Forum Contributor
    Join Date
    12-28-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    280

    Re: Need to copy a formula that automatically changes selected sheet in formula

    Hi Jennifer

    It would make sense if you copied all the employee names into col A of your summary tab and then use a a formula to point at the required sheet based on the name in col A/ For example, assuming you have in A2:

    Doe, John

    Then in B2 you can return their total hours worked with:

    =INDIRECT("'" & A2 & "'!C40")

  3. #3
    Registered User
    Join Date
    02-06-2012
    Location
    Youngsville, LA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need to copy a formula that automatically changes selected sheet in formula

    Each employee's name is on a separate sheet. Is there a way I can get Excel to poplulate column A with the employee's names without me having to copy and paste 50+ times or type them all in?

  4. #4
    Forum Contributor
    Join Date
    12-28-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    280

    Re: Need to copy a formula that automatically changes selected sheet in formula

    Yup - with the Summary sheet open (& visible) in Excel, open up the Visual Basic Editor (VBE) with Alt+F11. Open up the Immediate Window at the bottom of the VBE with Ctrl+g. Type in the following all on one line and press Enter at the end:

    Please Login or Register  to view this content.
    This will enter all your sheet names into column A from the first empty cell in Col A downwards. You can then remove any you don't need (such as the Summary sheet name).

  5. #5
    Registered User
    Join Date
    02-06-2012
    Location
    Youngsville, LA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need to copy a formula that automatically changes selected sheet in formula

    Thank you! That's a big help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1