Other Excel Help

I want to knock up a simple quantity calculator, that I can stick on the local shared drive for other people to access.

This is the kind of layout I'm basing it on:



Although, I need to include an output field.

The problem is, I'm clueless as to how to get it to work. i.e. applying code to the appropriate fields in the form. Also, is it possible to restrict the view to the only that form when the spreadsheet is opened?

I'm not looking for someone to do it all for me (that would be bare-faced cheek), I just don't know where to start.

Can one of you guys help me out?

Best regards

Dave

 

whats your underlying formula?

Are all the cells you specify mandatory or are you trying to create a calculation that will fill in any blank based on what is not entered?

 

Just the three measurements multiplied by relative density to produce a tonnage figure.

e.g. 4m x 3m x 150mm = 1.8 cu. metres

1.8cu. x 1.5 tonnes per cu metre = 2.7 tonnes

I will eventually want it to fill in blanks for backwards calculations but, as I said, I want to understand how to do it so I can add to and develop it as I need to.

I'm just at a loss where to start.

 

Formule is just a straightforward math calculation from what you describe so guessing that is not the stumbling block, but
=SUM((lengthref*widthref*depthref)*density)

As to reverse calculations - look up excel help under 'goal seek' - that should help you a bit with blank filling activities. If you have multiple potential blanks then look into cell validations of a conditional statement to advise users if more than one cell is empty.

You could do this with a straight sheet and cell protection locking the users out of cells you don't want fiddling with. - pass protect the cell protect/sheet and you're good to go. No need to complicate the scenario with forms.

If it's a reference tool that is likely to be used a lot you could actually sign up for appinventor, knock together a simple app and provide the apk... might be easier to reference for the user (if they have an android)

sorry if I've misunderstood or this seems brief - running out the door to get to a meeting.

 

Thanks for the reply.

You are correct - the calculations are straightforward, and not a problem. I just wanted to produce it as a form because I'll eventually add a drop-down box listing the various materials, that will auto-populate the relative density field when selected.

Cheers

 

GMC, why do you need the =SUM() part of your formula? There's no actual SUM taking place.

I'd agree with you about the form though - it could be done much more easily using a basic spreadsheet with locked & hidden cells.

EDIT: Spreadie, you can use drop downs in Excel too - if you're using 2007 or 2010, look at Data Validation on the Data tab and choose List as the "Allow" option for the validation criteria. Point the list source to your possible materials list, then use a VLOOKUP to the same list to get the relative density figure for your calculation.

 

Backward calcs I'll leave to you but this is a basic calculator (you could bypass the volume calcuator step and build it into final density calculation I think. I set it up this way to prove the volume calculation.




Private Sub CalculateVolume_Click()
Volumetxtbox.Value = Length.Value * ItemWidth.Value * (Depth.Value / 1000)
End Sub

Private Sub CalculateRelativeDensity_Click()
RelativeDensity.Caption = Volumetxtbox.Value * Materials.Value
End Sub

Materials.Value is Column 1 (not displayed on the calculator) of 2 selected where column 1 is bound which holds the density value from the named range in the spreadsheet.

If it's the kind of thing you're looking for pm me your email address and I'll send the file over to you so you can see the property values and take it from there.

 

Thanks Carrie, you're a star. If you like, I'll let you borrow my Karcher when you wash Tel's car.

PM inbound

 

You're welcome. But no thanks to the Karcher, he can wash his own car

 

Why do I imagine Tel driving this?

 

Lol, no idea at all

Spreadie, YGEM. If you've got any questions just let me know.

 

Wheres the flames?

 

I told you you should have used waterproof decals instead of letting them all wash off in the rain

 

Good grief, that is lush.

 

Just realised I forgot your other question - open form automatically.

In MVB double click on Thisworkbook (in the project window). It'll open up a code window for the workbook. Enter:

Private Sub Workbook_Open()
UserForm1.Show
End Sub

And the form will automatically open when the file opens

 

Thanks Carrie.

 

Rep to you Carrie, I learned a couple of things I didn't know there.

Also, this thread is extremely hammer heavy

 

So did I shirty. I hadn't used MVB at all, I just thought I'd have a go BUT before you (and Spreadie) panic I do know Excel extremely well and I did test it before posting