+ Reply to Thread
Results 1 to 5 of 5

Visitor Tracking Workbook with auto update of a summary sheet once I complete weekly log

  1. #1
    Registered User
    Join Date
    02-01-2016
    Location
    Lewes, DE
    MS-Off Ver
    Excel for Mac v15.18
    Posts
    3

    Question Visitor Tracking Workbook with auto update of a summary sheet once I complete weekly log

    Hi,

    This paragraph is basically background, and likely irrelevant, so skip to next paragraph for the problem description if you like.
    Ok, so you have time to read this too, Thanks. So, having spent ours on this site reading posts and looking at solutions, I know there is some incredible Excel, VBA, and programming knowledge being passed around that has helped countless individuals. Unfortunately, I am not yet among them as it has become apparent to me that I am a long way from understanding a lot of this information and applying it to my own projects. I've downloaded examples and tried to reverse engineer the code to work with my worksheets, but I've not yet been successful, and as much as I'd like to solve this solo, I am out of time and humbly request help. I am sure there is a simple solution, and I apologize in advance that I didn't find it in my previous searches. It is likely I didn't know the correct terminology to search for what I am attempting, so if you could point me in that direction that too would be appreciated.

    I am trying to create a workbook where the first worksheet displays various "Year to Date" totals as well as the current week's data entered into another worksheet within the same workbook. I plan on having 53 separate worksheets that represent the 52 weeks of the year and the 1 summary / totals worksheet. This may not be the best method so I am open to suggestions and changes. I think I solved how to increment the YTD totals by summing the same cells in each of the worksheets. I'm sure there is an easier way to do this, but this is the best I could come up with. I did notice that it was a solution in several forum posts so maybe it isn't that bad, just seems a little brute forced. My main sticking point is I am having a problem figuring out the best (any actually) method to move the data entered for the week to the summary page. I've attached a multi-sheet workbook which should make it easier to understand what I am attempting.

    Thanks for the help.

    Visitor Tracking.xls

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Visitor Tracking Workbook with auto update of a summary sheet once I complete weekly l

    Hi terbe and welcome to the forum,

    If all your worksheets look exactly the same and you want the first to total all the rest IN THE SAME CELL you are looking for a 3D formula.

    See an explanation at :
    http://www.pcworld.com/article/22413...xcel-2013.html
    or
    http://www.bettersolutions.com/excel...N620422111.htm

    It all comes down to a formula like the below:
    =SUM(Week1:Week2!C5)

    BUT you have named you sheets using a reserved word. I needed to rename your sheets to the above to make this work. I think a space in the tab name or the Week (1) isn't valid. See the attached for a pretty simple formula.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-01-2016
    Location
    Lewes, DE
    MS-Off Ver
    Excel for Mac v15.18
    Posts
    3

    Re: Visitor Tracking Workbook with auto update of a summary sheet once I complete weekly l

    MarvinP,

    Thank you for the help.

    I just accidentally clicked "+ Reply to Thread" instead of "Post Quick Reply" and blew away my last reply so that was a mildly painful lesson learned.

    Anyways, I didn't think about the sheet names being reserved words. I found that when I was duplicating them (up to 52 weeks), that Excel would increment the number of the sheet automatically if the sheet name already existed. So copying a group of sheets wouldn't require me to rename them each individually, I'm sure there is a formula or macro to sequentially name all your sheets when necessary, but I don't know what it is, so I was renaming them each individually. A bit of a pain until I figured out that Excel incremented the duplicated sheet automatically. I can go back to renaming them manually since it's not that big a deal and only should need to be done once.

    Thank you for the links to the 3D formula sites. Very informative and I have a better grasp on the way they function and how they apply to my situation.

    I am still working on trying to get the data from the weeks sheets to the summary sheet. If you look at the week1 sheet, you can see in the top left corner where I was playing with the idea that if a certain variable was entered, it would populate the summary (Totals) sheet with that week's data. I was also looking at the option of a button that when clicked would execute a macro to replace whatever was in the contents of the "This Weeks" data on the summary page (the stuff not populated by the 3D Formula) would be replaced by the data from the sheet where the button was clicked. I've tried the record a macro method and then place that in the button's module, but the code seemed very inefficient and seemed to have specific references to that sheet that wouldn't play nice when copied to the other 51 sheets without modification.

    An additional potential problem is that on the summary sheet, I have an area for traffic count. This are has "This Weeks" "Last Weeks" and "YTD" totals. If I solve the above paragraph's problem, I think I will be ok with "This Weeks" input, and the "YTD Traffic" will be supplied with the previous Formula you provided, but not sure how to get the "Last Weeks Traffic" data into its proper place. Possibly copy it from "This Week" before "This Week" is replaced by whatever means I use to move the data to the summary sheet.

    Thanks again, and I really appreciate any suggestions you can provide.



    Thanks again.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Visitor Tracking Workbook with auto update of a summary sheet once I complete weekly l

    Hi terbe,

    If you think you "must" use vba to solve this problem, here are the hints....

    Sheets.Count will return the number of sheets in your workbook. Type "?Sheets.Count" (without quotes) in the immediate window and press return to see the answer.

    Next - each sheet can be referenced by an index number. Sheets(2) is the second sheet from the left. Sheets(5) is the 5th sheet from the left. Here is short code to rename all sheets after the first using a For Next Loop.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-01-2016
    Location
    Lewes, DE
    MS-Off Ver
    Excel for Mac v15.18
    Posts
    3

    Re: Visitor Tracking Workbook with auto update of a summary sheet once I complete weekly l

    Thats cool, thank you, I figured there was a way, there always is in Excel.
    Still trying to figure out how to copy data. I can copy from one cell to another on the same sheet based on a condition entered somewhere else, but I can't get it to copy to another worksheet.
    Thanks again.

+ 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. auto update of weekly report into another table layout
    By kelvinliowhc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-08-2014, 10:17 AM
  2. Task time tracking with daily and weekly and monthly summary
    By issam.ha in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-04-2014, 12:02 PM
  3. [SOLVED] Macro to Auto Add Sheet Name to Summary Page In Workbook
    By Razzy in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 04-03-2014, 04:23 PM
  4. Compile data from daily worksheet files into auto gen. weekly tracking worksheet
    By stephenallen019 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-18-2012, 01:32 AM
  5. Replies: 1
    Last Post: 07-05-2011, 11:55 PM
  6. Summary sheet for 10 other worksheets to auto update
    By cvincent2008 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-11-2008, 07:21 PM
  7. Replies: 2
    Last Post: 04-10-2006, 02:59 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