Results 1 to 11 of 11
  1. #1
    tinkha
    Guest

    Extendable data sheets

    I'm trying to setup a workbook to track various elements about walking. I've created a data sheet where records can be entered giving various information parameters. This sheet is set up to have each record be a row and the columns contain the various parameters. After the user has entered a row, I need to do some calculations on the data. I set up formulas to do what I need, but I don't want to put them all the way down to row 65536 because the recalc time is horrendous at that point. I also would like to not have to update the spreadsheet just to extend its date range possibilities at some point in the future. For various reasons, I'd prefer to not use macros on this project, if I can avoid it.

    Is there some way that I could set up a formula to only add the formulas to the next empty row after the last data entry? This would mean that that formulas would always be available for the next record, but there wouldn't be the huge recalc time (until there were a lot of records). Is there an even better way of approaching this?

    I'm using Excel 2000 (SR1a).

    Thanks!

    Andy

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extendable data sheets

    I don't know of any way of doing this without using macros. The alternative seems to be to set up the sheet for something more reasonable, like maybe 1,000 rows. Then if that runs out, copy the formulas down another 1,000. Without macros, you basically have the choice of putting a formula in a cell or not putting it there.
    Legare Coleman

  3. #3
    tinkha
    Guest

    Re: Extendable data sheets

    Hmmm, ok. That's what I was afraid of. I'm trying to avoid macros mostly because of the Enable/Disable box that comes up with the macro virus protection. While I understand the reasoning behind the box, some of the people who will be using my sheet have almost no knowledge of computers and I don't want to confuse them or get them thinking there's viruses in my workbook, nor do I want to provide a mechanism for viruses to spread by just telling the users its ok to just click Enable Macros for my sheet. Maybe there are ways around that, I don't know.

    I think what I'm going to do in this case is take your suggestion to only fill down just a few rows (I'll probably do 1900 to hold about 5 years of data), hide the rows after that and just say that when the user hits the end, they need to start over at the beginning. Nothing I'm doing assumes chronological order in the column so that should work, I think.

    Thanks Legare!

    Andy

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extendable data sheets

    Since you have Excel 2000, you can digitally sign your macros. Then get the users to tell their system to trust macros with your signature. That only has to be done once, and the Enable/Disable will not pop up again. I'm not sure what versions of Excel earlier than 2000 will do with signed code though.
    Legare Coleman

  5. #5
    tinkha
    Guest

    Re: Extendable data sheets

    Unfortunately, I don't have a digital certificate to use to sign with. Last time I checked, these cost a not insignificant amount of money. Is this still the case? This is a hobby spreadsheet, not a business one, so no company support on getting a certificate, either.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extendable data sheets

    I don't have one from one of the commercial outfits either, and don't really know what they charge. There is a program that comes with Office 2000 (maybe only in the developers version) that you can use to generate your own signature. This works well for stopping the message on your own machine. I think it would also work for others if you can convince them to tell their system to accept it.
    Legare Coleman

  7. #7
    tinkha
    Guest

    Digital Certificates (Re: Extendable data sheets)

    How would I do that? I do have the Developer version, but don't see such a program in the Start Menu at least?

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Digital Certificates (Re: Extendable data sheets)

    No, it is not put into the start menu. You have to find it and execute it. Unfortunately, I am in the process of moving from Europe back to the USA, and I do not have access to a system with O2K on it and I do not remember the name of the program. Maybe someone else around here can help with that.
    Legare Coleman

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Digital Certificates (Re: Extendable data sheets)

    Hey this is a fascinating subject and I am eagerly awaiting the name of that file Legare <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
    However, may I suggest another idea. If you place your macro in an addin they will not get the warning either.
    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    tinkha
    Guest

    Digital Certificates (Re: Extendable data sheets)

    Well, I'll have to look for the program, and play around with add-ins...

    Thank you both very much!

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Digital Certificates (Re: Extendable data sheets)

    Hi,
    The program Legare mentioned is called SelfCert.exe and should be located in your Program FilesMicrosoft OfficeOffice directory. I believe it's included with the standard versions of Office 2000 as well but is not installed by default.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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