Windows 10 Excel help

[LT72884]

Hello all. I have no programing knowledge at all. Yea, i have taken a single intro class to java but that dos not go well and that was years ago.

First, ill explain what o do have and it works.

In excel, i jave in column A1:A95 names of common materials such as aisi 1020 steel, alm 7025 t6, cold roled steel, carbon steel, etc. In columns B1:B95 are the associated densities in kg/cubic meter. Colmun C, is left for user input of volume depending on what material they want. Then column D just shows the density multiplied by volume to get mass. Example; A23 is water B23 is 1000 C23 i input 2, D23 output is 2000.

Ok, this works, but if i need a material that is 85 in the list, i have to go all the way the list, put in the value at C85 and then D85 is my mass.

What i would like is a drop down menu that says the name of the material and its density, then in another box, i type in volume and then in the third box, it displays mass.

Thanks

Sent from my SM-S920L using Tapatalk

 

[Neemobeer]

You'll need a second sheet with a column of the material and the second column with the densities. Then the column you want the drop down select all the columns you want to have the menu and click 'Data Validation' and for the Allow change it to List. In the source field select and highlight all the materials from the second sheet. That will create the drop down menu.

For the densities column you will need the VLOOKUP function. The second column will be =VLOOKUP(A1,sheet2!A1:B30,FALSE)

Break down of the VLOOKUP, the first A1 is the value you are looking for so it will be the A# where # is the row you want it to match so the same row you are on. Sheet2! would be the second sheet sheet2 may be whatever you name the second sheet. A1:B30 will emcompass all the materials and densities on the second sheet. FALSE means exact match

 

[LT72884]

Awesome. Thanks for that info. I will see if i can follow that. I have not used excel much or programmed at all.


So whats on the first sheet if second sheet has column A for material and column B for densities?

Thanks again for the help and info

Sent from my SM-S920L using Tapatalk

 

[CmdrKeene]

Would you be willing to share your excel file (or a copy of it) on drop box, one drive, or Google drive?

 

[LT72884]

Sure. I can do that. Odd question, but since im using tapatalk on my phone, i do not know the url to this forum or thread so that i can upload that.

Thanks

Sent from my SM-S920L using Tapatalk

 

[Neemobeer]

I've created a sample excel workbook.

 

[LT72884]

Ok, here is the excel file. I had a production of theater to see last night so i was gone.

Ok, tapatalk is not letting me attach any file but photos grrrr

Sent from my SM-S920L using Tapatalk

 

[LT72884]

I've created a sample excel workbook.

Ill check it out in a few minutes

Sent from my SM-S920L using Tapatalk

 

[LT72884]

ok, yeah that is what im looking for. Two questions. how did you do it and is there a way on the calculation sheet to not have all the squares in blue. just have the one row? i know beggers cant be choosers, so if not, im cool with that. I have never used excel for more than just basic basic basic stuff.

here is my sheet of common materials, but i will be adding ALOT more. probably about 200 more

EDIT:

i have been looking up vlookup stuff, and that makes sense, what i dont get is the =if(insa thing you have in there?
Also, is there a way to categorize the drop down menue? for example, i could have metals and alloys. and under metals, there could be a category for aluminum that would have all sorts of species of aluminum under it?

i know i am asking alot and i appreciate everyones help. thanks

thanks

 

[Neemobeer]

The blue is just formatting so just change the cell color. The If and ISNA are for formatting otherwise you will get an error or NA displayed in the cells that are calculated.

 

[LT72884]

Oh, i see what you are saying. I see the NA error on some of my calculations sometimes haha.

Sent from my SM-S920L using Tapatalk

 

[LT72884]

Oh, i see what you are saying. I see the NA error on some of my calculations sometimes haha.

Sent from my SM-S920L using Tapatalk

Ok, here is a pic of the very last thing i am trying to achieve. So far the excel sheet works the way i would like it. Now i would somehow like to organize my 200+ materials haha.

Thanks for all the help this far. I have learned alot

Sent from my SM-S920L using Tapatalk

 

[Neemobeer]

Organize them in the excel sheet? and how?

 

[LT72884]

Organize them in the excel sheet? and how?

Yup, thats where i am kinda stuck. Haha. This will be a handy tool once complete.

Im not sure how to put alloys under a colapsable menu, then steels under its own.

Trying to imitate that pic might be harder than i thought haha. Maybe i have to make drop downs for each one and have multiple calculators. Then that gets messy

Sent from my SM-S920L using Tapatalk

 

[Neemobeer]

Don't think that's possible with Excel. At this point you'd be better off with a managed code solution.

 

[LT72884]

Don't think that's possible with Excel. At this point you'd be better off with a managed code solution.

Lol, ok, then i shall stop here with what i have and maybe have someone code it for me. BUT, for my purposes. It works. What i use it for, is in my engineering classes, i desgn 3d parts and i need to see how much it will weigh at the end so i can estimate price. I use solidworks at school and it has it built in, but at home, i use sketchup pro and it does not have this feature. It only gives me volume haha

Thanks for the help

Sent from my SM-S920L using Tapatalk

 

[Neemobeer]

It wouldn't be terribly difficult to create you could represent the materials in one table with a category column linking them to a sub menu. Add the ability to add new materials and group them. The calculation portion would be pretty simply

 

[LT72884]

It wouldn't be terribly difficult to create you could represent the materials in one table with a category column linking them to a sub menu. Add the ability to add new materials and group them. The calculation portion would be pretty simply

Lol, you know, i can do math all day, in fact, i teach math and chem, but you try to teach me coding.... its like herding cats... me and programing just dont get along.

Logically, i see what you are saying. Would this be a java applet? What would be way awesome, in sketchup, when you select your solid object, the entity info displays the cubic volume. It would be cool that when you select your object, you could right click and then open the java applet and have it pull the volume from the part. No typing what so ever haha

Sent from my SM-S920L using Tapatalk

 

[Neemobeer]

Computer Engineering degree and math minor myself . Honestly the language doesn't matter VB .Net and C# both have built-in types to handle this type of data. Java probably does too.

 

[LT72884]

Computer Engineering degree and math minor myself . Honestly the language doesn't matter VB .Net and C# both have built-in types to handle this type of data. Java probably does too.

Oh nice. I went mechanical and aerospace route. I originally finished a bachelors in network engineering but after 10 years of that, i wanted to go in what i really wanted to haha.

So vb, could handle this? Hmm. Maybe ill have a friend of mine help me out.

Have you done linear alg yet? If so, what do you think of it?

Sent from my SM-S920L using Tapatalk