INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Trying to create a stock list program

Trying to create a stock list program

(OP)
I like a challenge, so I dont mind figuring this out on my own. I am explaining what I am trying to accomplish, in case someone already has this or there is an easy way. Then I will explain the one formula I cant figure out.

Main Objective:
Using the simple chart I attached, I want to make a program where someone has to enter three numbers for the length, height, and width of a piece of metal and the program will look those numbers up in this chart and return what the correct stock size is. The reason I want this is because you cannot purchase steel in any stock size.

The rules are as follows....
If possible, we order in Inch x inch x metric (1" x 2" x 50mm) = 1020
If we only have one dimension that is inches, then we order in Inch x Metric X Metric, (1" x 50mm x 50mm) = STL PLT
It is always inches first then metric listed and within one system it is always the smallest number listed first.

So if T5 = 10, U5 = 19, & V5 = 35, the stock returned would be (3/8" x 3/4" x 35MM)


As I said, I am willing to work on figuring all this out, but if you want to save me the headaches because you like the challenge, feel free to do more.

WHAT I REALLY NEED.... T5 = 10, U5 = 19, & V5 = 35

In U7 I need a formula that will look for the number in T5 "10" in the range B1:R1, then inside that column (in this case it would be column D) it will look for the number in U5 "19" . If it finds the exact number, it will return that number. If it doesnt find the exact number it will return a blank cell or an X or something. If the number in T5 does not exist in the first range, it will also return as a blank or an X or something standard.

As you can see by the chart, some cells are blank. This is because these two stocks cannot be bought together. For instance, they do not offer 3/4" x 1 3/8" stock.

This is the only formula I really cant figure out so far. I cant figure out if it is a combo Hlookup & Vlookup or uses index or what.




RE: Trying to create a stock list program

Hi,

I like using Named Ranges for both understandability and maintainability. Here are the ranges I named in your sheet:

Name         Range                Description
rInH         =Sheet1!$B$2:$R$2    Inches Horizontal
rInV         =Sheet1!$A$1:$A$39   Inches Vertical
rLookUpALL   =Sheet1!$B$1:$R$39   MM Lookup Range
rLookUpH     =Sheet1!$B$1:$R$1    Horizontal MM Lookup
rLookUpV     =Sheet1!$B$1:$B$39   Vertical MM Lookup
 

The formula in...

T7: =INDEX(rInH,1,MATCH(T5,rLookUpH,0))
U7: =INDEX(rInV,MATCH(INDEX(rLookUpALL,MATCH(U5,rLookUpV,0),MATCH(T5,rLookUpH,0)),rLookUpV,0),1)


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Trying to create a stock list program

(OP)
Hey Skip

Thanks for the help. You threw something at me I dont remember ever seeing. lol You called them Named Ranges. I have been messing with the excel file you fixed trying to figure out how that works. I finally googled it. lol I cannot believe I have been using excel for so many years, always messing with formulas and never learned that you can name ranges. lol Should come in handy in the future.

RE: Trying to create a stock list program

Quote:

lol I cannot believe I have been using excel for so many years, always messing with formulas and never learned that you can name ranges

Wow. Just goes to show that modern help systems are not that helpful I suppose.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Trying to create a stock list program

If you have column headings or row headings, you can SELECT all headings and data and use ...

Formulas > Defined Names > Create from selection...Create names from values in the:

...and Check the location containing the names for the Named Range(s). I use this method a lot. I also use Structured Tables (introduced in 2007 version) as well.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Trying to create a stock list program

(OP)

Quote (IDS)

Wow. Just goes to show that modern help systems are not that helpful I suppose.

The problem with Help systems is that one needs to have some idea something even exists or is possible before they can look it up in Help. lol Personally, those are my favorite things to learn. Things that are commonly known and some how have escaped me. They serve as great reminders that no matter how much we think we know, we can always look stupid. lol

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close