+ Reply to Thread
Results 1 to 22 of 22

auto-populate data from sheet 1 to specific places in multiple other worksheets

  1. #1
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    auto-populate data from sheet 1 to specific places in multiple other worksheets

    I would like to be able to enter weekly earnings in the first worksheet and then have that data auto-populate into the rest of the worksheets.

    For example:

    If Robert Allenby earns 100,000 in week one, I would like to enter that value into the "Player Earnings" worksheet in cell B6. I would like that data to then appear in cell C20, C33, and C46 on "The Masters" worksheet and in other cells if another participant adds Robert Allenby to his team. I would like the week 2 amount (C6 from the "Player Earnings" sheet) to populate into all the correct places on the "RBC Heritage" worksheet and so forth for 19 weeks.

    I would appreciate if anyone could offer adcive on how to accomplish this task. Each participant will have 10 golfers on their team, and many will be duplicates. I would like to enter the earnings for each golfer only once per week if possible.

    Thanks!
    Attached Files Attached Files
    Last edited by desmondo37; 02-20-2012 at 06:21 PM.

  2. #2
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Several ways to do this...but without seeing your workbook I can't say which way is best or easiest. Either direct links to cells or vlookups would probably get what you need. Upload an example for more detailed help. Thanks!
    -Greg If this is helpful, pls click Star icon in lower left corner

  3. #3
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    I'm sorry. I didn't expect a reply so soon! I originally uploaded the wrong file, but just corrected the mistake. If you could take a look at it, that would be great!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Can you attach a sample workbook? It is not clear what the significance is of cells C20, C33 and C46 on the Masters worksheet.

    Pete

  5. #5
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Here is the workbook I am working with on. Thanks for the quick responses!
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    I read your last post with the updates after I had already worked on the original. Maybe you can still use this. I added a new tab to get accumulative earnings. You would add players only on The Masters tab and the golfers they select below their names on The Masters tab. The other tabs are linked. I added some vlookup/match formulas as well. Let me know what you think. Thanks!
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Just realized you're in Excel 2000. This is in an older format. I have some cells referencing the sheet names so you'll have to save this file before those cells will display properly. Good luck!
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    gjlindn-

    This looks great! I appreciate your time and work on this! The functionality of this is almost exactly what I was hoping for.

    Is there any way to get the weekly earnings to display on each week's sheet? For example, when I enter the earnings from week 1 on the Player Earnings worksheet, I would like that value to also show up on "The Masters" worksheet in column C Similarly, I would like the Week 2 earnings entered on the Player Earnings sheet to show up on the RBC Heritage sheet. If this is possible, I would love to learn how. Thanks again!

  9. #9
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    On your Masters tab (and each of the other tabs) in Cell C20 you could put
    Please Login or Register  to view this content.
    It's essentially the same formula as in column E but uses the Player Earnings tab instead of the Player Accum Earnings tab. Let me know if that doesn't make sense. Thanks!
    Last edited by gjlindn; 02-20-2012 at 03:08 PM. Reason: typo

  10. #10
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    I changed the formula to look on the "player earnings" sheet instead of the "players accum earnings" sheet. This seems to be working... check it out!
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    That's awesome, congrats! BTW...I see you uploaded an xlsx file (2007 & 2010), you may want to update your Office Version listed on the site (says Excel 2000) Also, if you wouldn't mind marking this thread as solved, that would be perfect. Thanks!

  12. #12
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    I originally built the workbook in excel 2000 on the computer in my office, but there is a computer with 2007 installed on it in another room. I used it to open your original file, and find it to be much better. Thanks again for your help!!

  13. #13
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    I was poking around trying to figure this out and just changed the worksheet name to "player earnings" from "players accum earnings." Then I saw your new post with the new formula. Should I use the new formula or use the column E formula that I altered?

    Also, how do I mark the thread "solved?" Do I just edit my original post and change the subject to include the word "solved?"

    Sorry, I am a new user.

    Thanks again!!

  14. #14
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  15. #15
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    That would depend on what you're looking for. If you want to display both the accumulative totals and totals per match you'd want to add the new formula and keep the old as well. I can take a look at your uploaded example again to confirm later tonight if you like, though. Thanks!

  16. #16
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    I'm sorry to ask one more follow-up. I'll need to add many more names to the "Player Earnings" sheet. I won't know which ones, or how many until after all the entries are submitted. Do I need to enter the data (the new golfer's name, earnings amount for each week) in a specific order?

    For example, I have added the name "Jones, Mike" to "Player Earnings." It shows up automatically on "Player Accum Earnings." (obviously) When I enter "Jones, Mike" to A35 of the "The Masters" sheet, it automatically displays $60 in cell C35 though I have not entered any earnings for Jones on the "player earnings" sheet. That amount ($60) corresponds with the value for "Halpine, Tim" in week 1 (cell B21 on "player earnings").

    I'm not sure if I entered the data in the wrong order for the "Jones, Mike" golfer, because I have also added "Smith, Will" as another test and that appears to be functioning correctly. I did add the $ to the formula so I could copy and paste it down row C in "the masters."

    I have also attempted to lock the cells with formulas so that I do not alter them by mistake. Perhaps this action created the problem?

    Thank you for any guidance you can provide!
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Sorry...my bad. I missed a parameter in the VLOOKUP.
    Please Login or Register  to view this content.
    Attached is an updated version with protection. Password is left blank so you can easily unprotect if needed. I also added validation to the section where you'd add the golfers to each player to help eliminate errors. The range used for the validation is a name I called Golfers and includes rows 6-60 of the Player Earnings tab. Thanks and sorry for the confusion on that VLOOKUP!
    Attached Files Attached Files
    Last edited by gjlindn; 02-20-2012 at 11:48 PM.

  18. #18
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Thanks a lot! You've been extremely generous and helpful!

  19. #19
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Edited so Accumulative scores will accumulate as soon as any value is entered for the week.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Amazing! Thank you!

  21. #21
    Registered User
    Join Date
    02-18-2012
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2000
    Posts
    24

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Greg-

    I have been using this workbook for several weeks now and it has been working great! It is saving me so much time every week! I have come to week 7, the Byron Nelson, and I cannot get the weekly earnings to populate into the "Byron Nelson" sheet from "Player Earnings." Also, I cannot get the cumulative earnings to show up on the "Byron Nelson" sheet from the "Player Accum Earnings" sheet. I am not sure if I have messed up the formula in some way by mistake. I would appreciate it if you would glance at it when you have a chance.

    Thanks!
    Attached Files Attached Files

  22. #22
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: auto-populate data from sheet 1 to specific places in multiple other worksheets

    Hi Darren...nice to hear from you This is a really easy fix. On both the Player Earnings and Player Accum Earnings tabs change "Week7" to "Week 7". Thanks!

+ 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