+ Reply to Thread
Results 1 to 9 of 9

Autopopulate some information from different tabs in spreadsheet

  1. #1
    Registered User
    Join Date
    02-14-2017
    Location
    London, England
    MS-Off Ver
    2011
    Posts
    4

    Autopopulate some information from different tabs in spreadsheet

    Hi,
    Hoping someone on here is able to help - I am creating a project plan with all the tasks according to theme in a spreadsheet along column 1 and the week/day across the top. This all works fine but it would be good for each theme lead to be able to see just their workstream in a separate tab. Is there an easy way to autopopulate some of the information from tab 1 into tab2,tab 3 etc?

    It would also be good if this autopopulated to show only the next 8 weeks rather than the whole project life – is that something that is also possible to do?

    Not sure if I’ve explained that well enough – see attached!

    Thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: Autopopulate some information from different tabs in spreadsheet

    This is probably pretty easy with VBA, just need to understand exactly what you are looking for.... Can you re-post your sheet with the result you're hoping to achieve in tab 2 and 3. Also, formulas may be easier. Do you have to have VBA?
    You either quit or become really good at it. There are no other choices.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Autopopulate some information from different tabs in spreadsheet

    I think this will do what you want.

    I had to modify the sheet a bit. First of all I got rid of the merged cells. Merged cells don’t work well with VB code. Use them sparingly. I replaced a lot of the merged cells with Center Across the Selection. It looks the same, but the individual cells are still there.

    I used real dates for the dates. It’s the cleanest way to find the 8 week range you are looking for. You can reformat these cells to something like “ddd m/d” and they will look like Mon 3/6 but still carry the date internally. The first date cell is fixed and the rest of the date cells are calculated from it.

    I also inserted a new Column A. It has a formula to copy down the project number. This column can be hidden.

    Then I added the Control Panel sheet. This does some error checking and computations.

    Cell B1 has the current day. Cell B4 is the previous Monday. Cell B5 is 8 weeks from the previous Monday. You can overwrite these values by entering a date in Column C. If the date entered in Column C is not a Monday within the range of the data, then Columns E and/or F will be shaded red. The program will fail.

    Then enter the project number you want to track. If the project number doesn’t exist, then cell D7 will be FALSE and highlighted in red. Again the program will fail if you don’t correct this issue.

    Click on the Get Project button and the selected project for the specified period is copied to the Project sheet.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    Registered User
    Join Date
    02-14-2017
    Location
    London, England
    MS-Off Ver
    2011
    Posts
    4

    Re: Autopopulate some information from different tabs in spreadsheet

    Thank you so much!! This is so useful, I'm a total Excel novice so this is amazing to me!

    Just a couple of questions:

    WhenI update the various tasks under each project it doesn't pull through onto the separate sheet? I have tried saving and reopening but that doesn't seem to make a difference. Is there something else I should be doing?

    Is there any way to have each project stream be its own tab so that someone could look easily at the next 8 weeks for each project separately or can it only be done one at a time via the macro?

    Thanks!

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Autopopulate some information from different tabs in spreadsheet

    What do you mean by "When I update the various tasks?" and "It doesn't pull through?" Explain to me what you mean by updating. Are you changing cell values / formatting or are you adding and deleting rows (adding rows might be a problem).

    What did you expect to see and what did you not see?

    As for creating a page per project, that could be done by putting the code in a loop to loop through project names. I'll get to that after resolving the issue above.

  6. #6
    Registered User
    Join Date
    02-14-2017
    Location
    London, England
    MS-Off Ver
    2011
    Posts
    4

    Re: Autopopulate some information from different tabs in spreadsheet

    Sorry - when I update the tasks under each one of the projects on the mastersheet i.e Under Project 1 if I was to put 'stakeholder management' in cell C17, and then go back to control panel and press 'get project' to run the macro and that inputted text doesn't show up.

    I want to be able to input tasks under each project and for them to come up when the macro is fun. Does that make sense?

    Thank you!

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Autopopulate some information from different tabs in spreadsheet

    Thanks. The description pointed me to the exact spot I had to fix.

    This version has the create sheet function "tacked on." You can now select project by name. If you select a project with Get Project, that project gets displayed on the Project sheet. If you click on the Get All, a sheet is created for each project.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-14-2017
    Location
    London, England
    MS-Off Ver
    2011
    Posts
    4

    Re: Autopopulate some information from different tabs in spreadsheet

    That's great, thank you so much! Just a couple more things as I have now inputted all my tasks etc into the spreadsheet:

    - it seems to come up with False and turn the cell red when I press 'get all' and the only one which seems to update with the updated tasks underneath the project heading is the '0. General' project code.

    Is there anything I can do to rectify this or something I'm doing wrong?

    Thnaks!!!

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Autopopulate some information from different tabs in spreadsheet

    I can't duplicate the issue, but I suspect it may have to do with how things are laid out on the Master sheet. You have to maintain column A manually. It's a formula, but you have to make sure you copy it down the appropriate amount of rows. Also the program is fragile in that it assumes the first 10 rows are as they are on the original.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating TABS from a list and have these tabs have information populate automatically
    By clpickett3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2016, 03:35 AM
  2. Monthly spreadsheet autopopulate master sheet?
    By sahsonk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2015, 01:52 PM
  3. [SOLVED] Create multiple tabs in a spreadsheet from information in columns
    By unpuertomex in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-13-2015, 12:15 PM
  4. Pull information from a table to autopopulate another sheet
    By timnabholz in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 08-06-2014, 09:11 PM
  5. Replies: 1
    Last Post: 02-10-2014, 11:30 PM
  6. How Do I split a file into several Tabs, acording to information on other tabs?
    By Edinson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2013, 05:17 AM
  7. Autopopulate a spreadsheet based on specific info from another spreadsheet
    By Boitumelo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-07-2011, 05:48 AM

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