Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2006
    Location
    Dublin, Ireland, Republic of
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cascade Data (2000)

    Iam looking to write a procedure that will cascade data based on dates. ie sheet1 will maintain data for the current month, with a new row created for each day. At the end of the month this is copied to a new sheet, and the data reset in the sheet1 for collection of the next months data. ( I also need a process to ensure that the daily row update only executes once / day to avoid duplicate data ) .

    In essence this is what I want to do

    1. Chack if the process has been run already today.
    2. Create a new row
    3. Populate this row with data
    4. If it is the first day of the month
    a. Copy the entire sheet to a new sheet and name the new sheet based on the contents ( month . year)
    b. Reset the contents in the old sheet to start a new month of data collection


    I expect this is a common process done daily throughout many organisations, so before I start to code anything I was hoping that there might be code already out there that I could use. The purpose of the application is to collect daily performance stats from servers, storage and networks, for performance and capacity planning over a period of time, weeks, months, years

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade Data (2000)

    I wouldn't do it that way. I'd use a single long table - since an Excel 2000 worksheet has 65536 rows, you can store well over a century of daily records.
    You can use code to select today's row when the workbook is opened, and you can summarize the data in various ways in other worksheets.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cascade Data (2000)

    Why complicate the process with code?

    You could set a sheet with dates for the entire year, and just have them fill in the data in the appropriate row. No need to create new sheets at all. With Freeze Panes on, the header row is always visible as well as the dates. <end-down> can get you to the bottom of the data. You can also add an Autofilter (and perhaps subtotals) to give a summary of the the visible data.

    You can also use a pivot table/chart to summarize/chart various data, including grouping and even a page field for the year. WIth 65536 rows you can put over a century worth of data in this sheet.

    Steve

Posting Permissions

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