Inventory Sheet updated continuously by a UserForm

DISCUSSION

I have a spreadsheet that I have been creating for work. It involves a UserForm (FRM_TubingTransfer) and writing the data entered onto sheets (either the ‘MASTER SHEET’ or ‘COMBOBOX DATA’) in the same workbook.

PROBLEM

I have (with your help and much trial and error) gotten the workbook to a reasonably working model. The only major thing left is the ‘AVAILABLE TUBING’ sheet. This sheet is the most complicated, I think, because it isn’t just strictly writing the data.

First, any time there is a NEW PROPERTY / SUPERVISOR entered in the form onto the COMBOBOX DATA screen, the code needs to check the AVAILABLE TUBING sheet and check if that data has already been entered there (This is because there may be some instances where the PROPERTY NAME, and PROPERTY NUMBER are the same but have a different PROPERTY AFE. Aka, there may be two entries with ‘WELL 1’ that have different AFE numbers but they are the same location and need to be treated as such.). Also, if a pipe yard (denoted by “-YARD-“ in the Property number and AFE number column) is entered, do not enter it on the AVAILABLE TUBING SHEET.

Second, when a TUBINGTRANSFER is processed, the program needs to do several things;

For the FROM LOCATION (if a well and not a pipe yard)
Add Buried Joints to the buried cell
Add (E) to the damaged cell
Subtract (A), (B), and (C) from the total on location cell
For the TO LOCATION (if a well and not a pipe yard)
Add (A), (B), and (C) from the total on location cell

ANTICIPATED SOLUTION

Some code to update the Property Location, Buried Joints, Damaged Joints, and Tubing on Location on the ‘AVAILABLE TUBING’ sheet.

LINK TO PREVIOUS THREAD:

http://www.excelforum.com/excel-prog...ml#post2105097

Greg,

The code is looking more and more like something we could use every time, I thank you for taking so much time to help me create this program! I told the boss that we need to put you on the payroll!

Anyways, the spreadsheet format is ideal, a couple things though. I would really love it if it didn't have any formulas in the cells and instead handled everything in a macro somewhere. I know that this works in the native excel you have set up as well but I won't be the person using this once it goes live and I don't really want a formula to show up missing at somepoint. This is going to be a continuous project for our office and if something gets messed at one stage, it will cause a ton of grief for everybody for each continuing entry, yaknow? I've heard rumors that a V.P. of another district got fired because he failed to keep track of his tubing as well as he should - which means me being a peon would go in a heartbeat! Because of this I would like to make this project as picture perfect as possible - something that other districts can pick up and use because it blows anything they have out of the water.

A couple additional notes - when a new property is entered, the macro needs to search for and see if it has already been entered on the available tubing screen. This is because there may be an instance where the same well name and number are used with different AFE's. This is still the same location and should be treated as such on the available tubing sheet. Furthermore, I have included pipe 'yards' and pipe 'suppliers' to use the sheet. If something is denoted with "YARD" in the property number and AFE number then it should be treated as any other location. If it is denoted with "SUPPLIER" then it should not have an inventory associated with it (just the property info).

Also, the damaged tubing information you requested - when a class 'E' box is filled out on the form, it needs to be added to the FROM LOCATION in the damaged tubing cell on the inventory sheet. When a class 'E' box is filled out on the TO LOCATION then it needs to be subtracted from the FROM LOCATION on the tubing transfer sheet.

As for the calendar - when I tried to use it, I was on my personal computer at home logged on to a program called CITRIX to access my information on the network drive at work and I think that there was something going on with CITRIX that was messed up. I will try it again next time I'm in the office (I'm mostly in the field so that may be a while).

Any more information please let me know.

Well, the class 'E' joints are a hassle for sure. They can't be transfered to other wells because accounting can't justify charging a cost to a well for pipe that we know is faulty. Also, my group can't justify paying a truck to pick up say one joint of tubing on a location every time we find a bad one.

The compromise is to leave the tubing on location and once a month have a driver go to several locations to pick up this bad pipe.

You are correct in thinking that 'E' tubing can only be charged to the FROM LOCATION. However, when we reconcile the pipe monthly, there needs to be another way to transfer from the location to the scrap yard. I'm thinking entering a negative number or a check box would be the easiest. Possibly a separate tab?

Thoughts, ideas?

Sounds good, so would the code have to do some sort of 'if going to scrap then this else act normal' deal?

Greg, I have been very busy lately and haven't had much time to look into your workbook but I will soon, thank you for your continued help!

It's been a while, let's see if anybody remembers the problem...

I'm still working on it and it would be nice for a couple things to happen when the drop-down boxes are selected that have the corresponding drop down boxes associated with them.

First, would it be possible to have some sort of concatenation happen on the 'Property Name' drop down box? What I mean is, while the 'COMBOBOX DATA' sheet will still just list the well name as 'WELL ABC', could it read 'WELL ABC - AFE 123456' on the form? This is because there may be several AFE numbers associated with the same well and it would be necessary to differentiate between them.

EDIT - Also, It would be cool if the SUPPLIER locations came up first, then the YARD locations, then the wells.

Also, it would be nice if the property name box was alphabetical when the drop down arrow was engaged, and the property number and AFE number be numerical. So I'm saying, is it possible to sort each of these categories each time the drop down arrow is engaged?

Any help will be as always, appreciated.

I know it has been quite awhile since this thread has been active; however, I am building a similar spreadsheet for keeping track of pipe in a storage yard. I found this thread while doing some research. Does anyone know if this spreadsheet ever got completed to the way the original creator intended? I was hoping I could have a look at the end result to assist in the format of my similar version.

Thanks,

James

Its been 4 yrs since the question was posted and it is not likely that the original poster will come back.

Why dont you post a new thread for your question? You may get faster replies.