Date and time

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.
 
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.
 

Attachments

  • TAWTL empty data.jpg
    TAWTL empty data.jpg
    66.7 KB · Views: 12
  • TAWTL error window.jpg
    TAWTL error window.jpg
    70.5 KB · Views: 9
  • TAWTL error.jpg
    TAWTL error.jpg
    61.5 KB · Views: 11
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
 
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
 
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:
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).
 

Attachments

  • WaterTankLevels_Rev2.xlsm
    120.9 KB · Views: 9
Opps..... didn't test that too well, ignore it and I'll post a revision shortly
 
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:
@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.
 
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.
 
idiot proof...... no such thing, can only try. :)

If I could just get my head around tables, I think alot of issues would go away.
I have a project of my own where tables will come in handy so I don't mind learning as we go.

Keep in mind I'm just an old retired guy that plays around with this stuff for entertainment, I'm no programmer, just trying to help out, pass on some of the things I've learned and learn something new in the process.

Hope to have something for you in the near future.
 
I understand completely. I'm even less a programmer than you. I can usually alter script and make it do what I need or piece together sniplets of script I find online. I'm no good at starting something from scratch. I wouldnt know when its best to just use a formula or throw in a script. I can read and understand scripts though. I know what and how your scripts in here work but i could have never written them myself. I too am learning as we go and I'm very grateful for you time and help.

I will wait for you next rev.

Oh and something you asked me earlier that i forgot to answer. I live in utah, where i'm at right now, so im in mountain standard time. I work and will be using this spreadsheet in north dakota. The time zone there is Central Standard time.
 
OK, I'll try this again.

Think I've addressed your concerns so far.

Have hidden your original sheet and the Generic tank site sheet.
Don't delete these sheets. If you need to rebuilt the sites they will be needed.

Now using tables. Formulas will automatically fill in when needed.
DO NOT delete entire rows above row 10 because this is where the formulas live.
Deleting entire rows 10 and below is fine. The upper rows should be locked but I think protecting the sheets now will get in the way of your testing.

I'm gone for the rest of the day but let me know how it goes.

Good luck (fingers crossed)
NoS
 

Attachments

  • WaterTankLevels_Rev3.xlsm
    108.3 KB · Views: 12
THIS POST WAS PUT IN JUST AFTER YOU POSTED LAST. IT DOES NOT PERTAIN TO THE FILE IN THAT POST SO THIS CAN PROBABLE BE IGNORED. I'LL PLAY AROUND WITH THE NEW FILE AND REPOST MY RESULTS.


Hey NoS,

I was just playing around with the last version again. I was messing with sheets that I cleared all the input data to see how its gonna be when i get back to ND and start fresh with data. I ran in to a couple problems. I know you are looking into new things for this so if this doesnt matter just ignore it.

After clearing out the data I figured the best way to start the sheet would be to just put in the tank levels with the form but leave the amount taken out. That data went in the sheet fine. The problem was when I did the second input. I used the same levels as the first time because thats what they would be but this time I took 160 bbls out. You can see the errors in the pics below.

The error where the bbls taken is, it says its not in number format, i even went in and set that column to be numbers. The other errors, div/0 is because there are no values in the cells the formulas are using.

Mike
 

Attachments

  • Error screen.jpg
    Error screen.jpg
    33.5 KB · Views: 9
  • Errors in workbok.jpg
    Errors in workbok.jpg
    96.8 KB · Views: 17
Last edited:
This is a continuation of the previous post.

The errors do pertain to the file you just uploaded also. The type mismatch occurred when I went to the summary page. You can see in the attached pic. That only happened because of the #div/0 error on the other sheet.

Seems to be another if/then problem in the same code as before, but a different line?

Well good luck with what you are doing today. I'll play around with the code while wait for your reply. Maybe ill stumble into the fix. lol.

Mike
 

Attachments

  • Summary error.jpg
    Summary error.jpg
    98.7 KB · Views: 7
  • code error.jpg
    code error.jpg
    45.9 KB · Views: 6
How often will you be entering tank levels and not taking any pulls?

If you notice on the first data line there are no formulas in those columns that have errors, that's because with no previous pull you really have nothing to work with. By entering 0 bbls taken you effectively moved everything down a row, hence the errors.
Make the pull 160 and move the date back a day, think then it will make sense.

please check for personal message on this site, thanks.
 
Back
Top