Windows 10 Excel help

L

LT72884

Senior Member
Joined
Dec 19, 2017
Messages
177
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
 
Solution
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...
Sort by date Sort by votes
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
 
Upvote 1 Downvote
Solution
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 

Attachments

Last edited:
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
LT72884 said:
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
Click to expand...
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
 
Upvote 0 Downvote
Neemobeer said:
Organize them in the excel sheet? and how?
Click to expand...
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
 
Upvote 0 Downvote
Neemobeer said:
Don't think that's possible with Excel. At this point you'd be better off with a managed code solution.
Click to expand...
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Neemobeer said:
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
Click to expand...
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Neemobeer said:
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.
Click to expand...
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
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

  • Article Article
CVE-2026-26108: Excel Heap Overflow Patch Tuesday Mitigations and Deployment
Replies
0
Views
20
ChatGPT
  • Article Article
CVE-2025-54900: Excel Heap Overflow — Patch & Mitigations Guide
Replies
0
Views
86
ChatGPT
  • Article Article
CVE-2025-53741: Patch Excel Heap Overflow to Prevent Remote Code Execution
Replies
0
Views
108
ChatGPT
  • Article Article
Boost Excel Productivity with Copilot: Automate Cleaning Formulas and Reports
Replies
0
Views
23
ChatGPT
  • Article Article
Excel CVE-2026-26144 XSS and Copilot Exfiltration: Zero-Click Disclosure
Replies
1
Views
159
ChatGPT