Good day everyone.
I'm newer into the excel formulas and I've run up against a scenario I just cant seem to beat.
Ive got an excel file containing two separate workbooks; 'Fastener Calculator' and 'Fasteners'.
'Fastener Calculator' contains a series of look-ups and formulas to auto populate fields with information found on 'Fasteners'
The cell that is really screwing me up is supposed to read a cell value on 'Fastener Calculator' and use that information to look up a value on 'Fasteners' using, what I assume should be, a combination of the offset and match functions.
I thought I had the thing working, but then I realized that it was only matching the first like value in a long list of numbers. To put it more clearly, I am trying to auto generate the Minor Diameter of a screw based on the threads per inch, however; as a result of the fineness or coarseness of the thread you can have the same threads per inch over a variety of different screws. So instead of displaying the accurate minor diameter its locating the first instance of the required threads per inch and providing that value.
Ive tried to attack this a few different ways and they have all been unsuccessful. I think the solution involves isolating a specific range when doing the match function.
For those interested in attacking this problem I'll lay out the thought process...
The user selects the appropriate fastener via a drop down box, then the threads per inch via the adjacent drop down box. Between 1 and 3 options are available based on the fastener selected via =OFFSET(fASTENERS!A7, MATCH(C12,fASTENERS!A7:A108, 0)-1,2,3,1), this is where the real issues lies because of the options. This means that in the "fasteners' workbook contains one merged cell (originally three vertical cells) containing 'Major Diameter' (a constant regardless of threads per inch), and three vertical adjacent cells with threads per inch (TPI), and adjacent each TPI is the highly sought after 'Minor Diameter'.
This is the formula that provides an erroneous result, but a result none the less...
=OFFSET(fASTENERS!C7, MATCH(H12,OFFSET(fASTENERS!A7, MATCH(C12,fASTENERS!A7:A108, 0)-1,2,3,1), 0),1,1,1) <-- as you can see I attempted to narrow the range of for the match function, but for some reason this only pulls the very first minor diameter on the list, not even associated with the TPI, the first minor diameter in general.
This one has me beat, so I'm hoping we have an excel genius out there who can help me out.
Here is the link to my file in drop box.
https://www.dropbox.com/s/uyxfo8k74jvic3k/Screw Chart.xlsx
Oh, by the way, on the 'Fastener Calculator' tab the "Fastener Quantity" is not yet linked to anything, it can be disregarded, and the gibberish to the right is just me messing around with various formulas.
I'm newer into the excel formulas and I've run up against a scenario I just cant seem to beat.
Ive got an excel file containing two separate workbooks; 'Fastener Calculator' and 'Fasteners'.
'Fastener Calculator' contains a series of look-ups and formulas to auto populate fields with information found on 'Fasteners'
The cell that is really screwing me up is supposed to read a cell value on 'Fastener Calculator' and use that information to look up a value on 'Fasteners' using, what I assume should be, a combination of the offset and match functions.
I thought I had the thing working, but then I realized that it was only matching the first like value in a long list of numbers. To put it more clearly, I am trying to auto generate the Minor Diameter of a screw based on the threads per inch, however; as a result of the fineness or coarseness of the thread you can have the same threads per inch over a variety of different screws. So instead of displaying the accurate minor diameter its locating the first instance of the required threads per inch and providing that value.
Ive tried to attack this a few different ways and they have all been unsuccessful. I think the solution involves isolating a specific range when doing the match function.
For those interested in attacking this problem I'll lay out the thought process...
The user selects the appropriate fastener via a drop down box, then the threads per inch via the adjacent drop down box. Between 1 and 3 options are available based on the fastener selected via =OFFSET(fASTENERS!A7, MATCH(C12,fASTENERS!A7:A108, 0)-1,2,3,1), this is where the real issues lies because of the options. This means that in the "fasteners' workbook contains one merged cell (originally three vertical cells) containing 'Major Diameter' (a constant regardless of threads per inch), and three vertical adjacent cells with threads per inch (TPI), and adjacent each TPI is the highly sought after 'Minor Diameter'.
This is the formula that provides an erroneous result, but a result none the less...
=OFFSET(fASTENERS!C7, MATCH(H12,OFFSET(fASTENERS!A7, MATCH(C12,fASTENERS!A7:A108, 0)-1,2,3,1), 0),1,1,1) <-- as you can see I attempted to narrow the range of for the match function, but for some reason this only pulls the very first minor diameter on the list, not even associated with the TPI, the first minor diameter in general.
This one has me beat, so I'm hoping we have an excel genius out there who can help me out.
Here is the link to my file in drop box.
https://www.dropbox.com/s/uyxfo8k74jvic3k/Screw Chart.xlsx
Oh, by the way, on the 'Fastener Calculator' tab the "Fastener Quantity" is not yet linked to anything, it can be disregarded, and the gibberish to the right is just me messing around with various formulas.
