I want to set up a formula where i input a word into a cell and that determines a value in another cell for a calculation. is that possible? could be a differnent value depending on the preset words.
Printable View
I want to set up a formula where i input a word into a cell and that determines a value in another cell for a calculation. is that possible? could be a differnent value depending on the preset words.
Need a bit more information of what you mean but I think you could use a Vlookup to look up a word in a list and return the value related to that word.
Sounds like you need to use a simple Vlookup
It's a calc to work out the weight and volume of packs when given a square metre of a roof.
Different profiles have different weights and package quantitys so will take up different space when flat packed to go on a truck.
I could make the calc repeated over various ones but I want to make it so the user doesn't need to search for the correct profile and instead can just type the profile in and it will work it's magic.
Drop me a excel file with what you want it to do and the different data and I will sort it for ya ;-)
id like to learn how to do it myself to be honest but i can send it to you and if you explain what you have done that would be great.
basicly the table in the pdf is the information for each profile, the numbers on the right is the sheets per pack.
the excel file has the information i put in now on the left and the calculations i need on the right.
i would like to just put in the profile name ie 65/305 and the thickness in with maximum sheet length and it auto fill in the mass per m2 and liner mass.
does that make sense?
https://drive.google.com/file/d/0Bz2...it?usp=sharing
https://drive.google.com/file/d/0Bz2...it?usp=sharing
Still as clear as mud but I have done what I thik you want.
http://www.kimba.me.uk/excel/kalzip.xlsx
I have created a subset of your information in a table G4:K10
Created drop down list for the data entry for Product and Width using Data Validation
Used SUMPRODUCT to populate the Meter Square, Mass Meter Square, Liner Mass and then your calculations still calculate the total weight, total pack weight and total packs.
I didn't see where the sheet lengths in meter and sheets per pack were on your PDF so I will leave that to you as you want to learn ;-)
ok that just what i wanted. made a couple of alterations and added more profiles. put in a sumproduct for the pack sizes and generally just completed for the rest of the details.
also locked the formula cells so that they cannot be altered.
thanks very much
what do you think?
https://drive.google.com/file/d/0Bz2E35WtzrFXenFCYk9MN0toTjA/edit?usp=sharing
wow what a great guess from me but I also hope you learnt a little too ;-)
When I lock a sheet I lock it all down except the editable fields which I change the background to green so people know they can edit these.
click the 4 cells (holding ctrl) right click and format cell>Protection, untick locked. they when you protect just tick the select unprotected cells.
yeah i learnt about drop down boxes and that sumproduct. hopefully next time i need to use them ill remember how to. if not i have that sheet to refer to.
Thanks
Theres a whole lot more you can do with Excel than just dropdowns and Sumproduct ;-)
VBA now that's where you want to be at ;-)
VBA?
VBA is far too slow. .NET integration is where it's at!Quote:
Originally Posted by akimba
"Visual Basic for Applications" MS Office's programmable language (Alt+F11) but I was just saying that as the famous phrase is "90% of users use 10% of the Application and 10% use 90% the Application"
Yeah can argue .Net would be faster but for simple programming start with the tools given to you for free ;-)
.NET is free!
ok rather than the word "free" how about the preinstalled tools that come with Excel?