Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 36

Thread: Date and time

  1. #21


    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm excited to see it. Can't wait till I can. Just haven't been able to get away from work yet to get a laptop and my phone app won't open your file. I should be getting into town in the next couple days though. I will defiantly let you know how much I like it. Hopefully I can understand enough to get it completely without it being done for me.

  2. #22
    NoS. omg that is awesome. Way more than I could ever thank you enough for. Sorry its taken me so long to get back on this. I still don't have my laptop, I will actually be getting it today. I did however get a chance to take a break from work and get home. First thing I did was get on here so I could check out your file. Well, ok, I talked to my kids first lol. I love everything that you did. There are a couple things though, if you don't mind helping me with.

    The biggest problem I found so far is a type mismatch. I was clearing out the data that you had put in so i could start with new data. When I clear a sheet and go to the summary page to Refresh Data I get the error. From what i see the error is because the script is looking for the last line with data and there is none. My guess would be an if/then statement in there to boot it to the next cell if it finds no data? I don't know exactly where that would go. I took screen shots and uploaded them here. You can see the links below

    The next 2 things are more of 'can it be done' questions and if so how? First it the Show Entry Form button on the summary page. Is it possible to put in a ctrl key link to that form so no matter what sheet I'm on i can just do 'ctrl-f' (for example) and have the Entry Form pop up?

    The other thing I'd like to see is the number of rows of data it can store. Is it possible to have it be auto unlimited? By that I mean have it continually add the data rows without me having to copy/paste in the formulas in more rows like you did.

    Other than those few things, you have done a totally awesome job on this project.

    I have added new water towers to my work load. With these tanks I only deal with one per oil pad and its just opposite of how these tanks work. The new tanks I'm putting water INTO them and their flow rate is emptying them, not filling like here. I'm gonna play with this workbook to see if I can get it to reverse the results so I can have a spreadsheet for these tanks too. Its all the same concept except i need to know when to go put water into them, not pull it out. Maybe, if you don't mind, if I run into problems you could lend my your awesome expertise to solve any problems I might run into.

    Thanks you soooooo much again.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	TAWTL empty data.jpg 
Views:	4 
Size:	66.7 KB 
ID:	2850   Click image for larger version. 

Name:	TAWTL error window.jpg 
Views:	5 
Size:	70.5 KB 
ID:	2851   Click image for larger version. 

Name:	TAWTL error.jpg 
Views:	6 
Size:	61.5 KB 
ID:	2852  

  3. #23
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hello tester, glad to hear from you, and no that's not awesome, it's just a userform.

    One of the quirks with this thing that I never addressed is that it doesn't really behave 'til the second set of data is entered. I will fix that and get rid of that type mismatch. In the mean time just put in some phoney data on the first line for date, befores and bbls.

    You say you cleared out the data so you could start with new data.... you do realize only the stuff on the entered data side is all that should be deleted, right? All those cells on the right hand side under calculated items are filled with formulas that will leave the cells blank if they need to be blank. Don't touch or delete anything there.

    The show the Entry form request....
    Alt + F8 will bring up the macros that you can run. Select the Show Tank Site Form then select the options button and you can assign it anything you want. I tend to use CTRL + m , apparently that is an unused combination in Excel and won't screw anything else up.

    Number of rows of data it can store..... lets see, Excel 2010 has 1,048,576 rows. At entries of 1 row per day that's good for just under 2900 years.... is that what you call unlimited? :-)

    Seriously though, you shouldn't, and won't, need to copy-paste anything. Actually you shouldn't be pasting anything now, at worst you should only be grabbing the drag handle of a cell with a formula and dragging down. I only dragged the formulas down about 50 lines so I'd have some space to play with.

    I know there is something about formatting as a table and Excel will automatically copy formulas down to a new row if it needs to, but I've never used that so will look into it.


    Anyway you play with this thing, let me know of any thing else that needs tuned up and I'll get back to ya.

    NoS

  4. #24
    NoS,

    Yes I did learn that i could only delete the data the show tank site form puts in the hard way. The first time I deleted all the info, but only on one sheet.

    I got the ctrl+m thing done. I didn't know about that option.

    On the number of rows, its funny that you told me how many rows there are. My daughter is 15 and learning excel in school. I was explaining this spreadsheet to her the best i could and she just randomly threw that tid bit of info out there. I did not know. But about the rows. I'm a little confused. I thought the reason you dragged the formulas down 50 rows it because they had to be put in each row before you put the data in that row to be able to get the output. So what you are saying is that the formulas get put in when the data gets put in?

    Im gonna wait till you get a chance to look at the error. Then im gonna clear all the data out and put in new fake data. Im gonna put the data in slowly. I'll do it in no less than 4 hrs per new load. Its still too fast but the numbers it returns will be more realistic. I'll be able to find any other probs there might be.

    Mike

  5. #25
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    fix the type mismatch, make Sub GetCurrentLevels() look like this, added only 2 lines
    Code:
    Sub GetCurrentLevels()
        Dim rng As Range
        Dim cel As Range
        Dim Lastrow As Long
        Dim nextFull As Date
        Dim curLevel As String
        Dim curFillRate As String
        
    Set rng = Sheets("Summary").Range("A4:A18")
    
    For Each cel In rng
        With Sheets(cel.Value)
            Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
            If Lastrow < 9 Then GoTo N
            curFillRate = .Range("O" & Lastrow).Value
            nextFull = .Range("R" & Lastrow).Value
            curLevel = .Range("S" & Lastrow).Value
        End With
        
        cel.Offset(0, 3) = nextFull
        cel.Offset(0, 4) = curLevel
        cel.Offset(0, 5) = curFillRate
    N:
    Next cel
    End Sub
    As for when the formulas go in....
    Currently I need them in ahead of time because the numbers in the current level column never quit increasing. In order for what you see to make sense I have to hide the ones that don't make sense, meaning you only see the one on the row of the last data but even that needs to reference the next row which needs to already have the formulas. Did that make any sense?

    PS: what time zone are you in?
    Last edited by NoS; 2014-11-20 at 05:17 AM.

  6. #26
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Mike, use this for your experimentation. Don't worry about the #NA, looks after itself after the second data entry. The tables automatically expand themselves when needed and take the formulas with them, no copy-paste required. I'm looking at locking the rows with formulas to prevent accidental deletion but for experimenting that would be a pain so for now don't delete anything above row 10 (entered data excepted).
    Attached Files Attached Files

  7. #27
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Opps..... didn't test that too well, ignore it and I'll post a revision shortly

  8. #28
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NoS View Post
    As for a static date and time, seems the only way without VBA would be to enter this in the cell
    Ctrl + ; then a Space then Ctrl + Shift + ;
    Heres a little trick I learnt somewhere

    1. For your workbook, enable iterative calculations according to your version of excel.
    2. In A1 enter this formula in A1 and copy down.
    =IF(B1<>"",IF(A1="",NOW(),A1),"")
    3. Now... If you enter any data in B1 and down, Excel places a timestamp in A1 and down. A1 remains blank if there is nothing in B1.
    4. To reuse, you need to clear contents in B, otherwise the original timestamp remains.
    Last edited by Hercules1946; 2014-11-20 at 10:54 PM.

  9. #29
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    @Hercules1946

    Thanks, I'll tuck that away for future reference.

    That particular issue was overcome when the OP decided to use Excel on a laptop rather than Polaris on a phone.

  10. #30
    Ok, ignored.

    2 issues found this time. First, when I do remove the data content and refresh on the summary page I don't get the error, but it does leave the old values. Can you make the cells go blank if there is no data on the sheet.

    Second issue is to make it more idiot proof, I will be using it and need all the help i can get. I was thinking, if possible, to have the "write this data to the worksheet" button grayed out till all fields in the form are filled out. Also because testing it just now when I clicked on that button without selecting a oil pad name from the drop down i got subscript out of range error. In that same test when I dont change the tank levels (feet at least) it doesnt put the date and time in. When I write that to the worksheet it has value errors. If you then go to summary sheet and refresh you get the same error that you just fixed when the last line was blank.

Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Posting Permissions

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