+ Reply to Thread
Results 1 to 8 of 8

Linking to future sheets

  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    Seoul, South Korea
    MS-Off Ver
    Excel 2007
    Posts
    20

    Linking to future sheets

    To start, I'd like to say this forum is awesome and has saved me more than a few times. Thank you to all the experts!

    Now, I have a bit of a problem. I have a series of report files documenting daily activity. Usually, these files are saved by month. In otherwords, a daily activity report for August. Within each file is a tab for each day. Each tab also has one cell that lists exactly that day. These files are saved on a server.

    Based on these files, we must collect data and create a summary for the week. It must include total number of people from each team for the day and the weather for the day. This will be collected in a database such that for any 7 day period that we select, a summary table can display all the information we want to know. The database has been created, however, I would like to automatically link the database with the daily activity report files. Source data location will be fixed, so no need to find that, however source tab and source file will change. Since this is an ongoing report, these links must be dynamic, searching for the correct file and tab in accordance with the date required. The above is an approximation of the end product. Is what I am looking for possible? Please help!
    Below is an example of the resultant table.
    8/2 8/3 8/4 8/5 8/6 8/7
    Weather Sunny Cloudy Snowy Snowy Rainy Sunny
    Team A 25 24 17 15 19 22
    Team B 6 5 9 8 3 3

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Linking to future sheets

    Hi DaBrot791

    I have something that may give you some ideas. It is very much work-in-progress as I have run out of time on this.

    I have an issue, which may just be use of language. "Usually, these files are saved by month". To use this system, it must be "Always" !

    Your data must be in a file named n-yyyy (as per 8-2014). Your summary file may be called anything you like.

    I hope that this will give you some ideas how this may be done.

    Note that if you have not used macros before, you may have to adjust your macro settings. To do this go to File > options > Trust Center > Trust Center Settings > Macro Settings.

    Please note that after Friday, I will be unable to reply to any emails for 2 weeks.

    Let me know how you get on.

    Regards
    Alastair
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-04-2012
    Location
    Seoul, South Korea
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Linking to future sheets

    Thank you Alastair,
    Truly a life saver. I have downloaded the files and will take a look at them. I also realize that the files always must be saved consistently.

    Cheers

  4. #4
    Registered User
    Join Date
    09-04-2012
    Location
    Seoul, South Korea
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Linking to future sheets

    I have reviewed the files and have a few questions. The report files will be rather dynamic, in that there may be additional teams added in the future. I have prepared for this by allowing additional cells for the teams. However, analyzing the macro, it seems rather static. If additional teams are added, it seems the macro must be edited as well. I won't always be here to do that, and others are...excel challenged. Also, I tried to enter data for future dates, or even revise data, and was not able to get updated results. Perhaps it is because the root folders to search in the macro were incorrect? Please enlighten me!

    P.S. I greatly appreciate the effort put into the macro. I am still trying to understand how it works.
    Cheers,
    Attached Files Attached Files
    Last edited by DaBrot791; 08-28-2014 at 08:54 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Linking to future sheets

    Hi DaBrot791

    I have re-written the macro so that it works with your files.

    Note that the macro looks at the weather cell (Row 3) to decide whether data needs to be brought in.

    Note that on 2014.08 Daily-Report sheet 2014.08.20 there is an error on B219 in that there is a space after the "d". This will not match with the "d" on the DB sheet!

    When using this, please remember that it works OK for me - so your challenge is to see if you can get 20 and 21.08.2014 to get into the DB

    Have fun playing with this and I will answer any queries in 2 weeks.

    Regards
    Alastair
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Linking to future sheets

    Hi DaBrot791

    How did you get on with the macro?


    Regards
    Alastair

  7. #7
    Registered User
    Join Date
    09-04-2012
    Location
    Seoul, South Korea
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Linking to future sheets

    Firstly, welcome back, hope you had a good vacation. I spent one week with no internet followed by a few days with no electricity. This, coupled with my very limited knowledge of macros and coding severely hindered any understanding of the code. In the end, I was forced to give up, due to the potential for future changes and additions in the base file. I appreciate the assistance and will still continue to study it however for future use. If any explanation can be offered, it'd be greatly appreciated. I will write specifics of what I didn't understand later.

    Cheers,

  8. #8
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Linking to future sheets

    Hi Dabrot791

    I had such a good vacation that I have completely forgotten what I was doing!

    I think I have recalled everything so I have annotated the macro - I hope it explains things.

    Feel free to ask any questions.

    Regards
    Alastair
    Attached Files Attached Files

+ 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. Replies: 4
    Last Post: 01-15-2014, 10:26 AM
  2. Hide Future Rows Containing Future Dates.
    By Raj_121 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-27-2013, 07:14 AM
  3. Saving sheets for future reference
    By Vic Schoeman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2011, 06:45 PM
  4. Linking two sheets
    By The_jackal in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 05-28-2008, 09:34 AM
  5. Linking Sheets
    By rickschepp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2007, 04:15 PM

Tags for this Thread

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