huangyhg

excel engineering spreadsheet applications
i am working with an excel design spreadsheet and want to select criteria from a table, based on two separate inputs. for example, part of my table is constructed as follows.
cells a1 thru a5 are all labeled 鈥淪yp #1" species & grade.
cells b1 thru b5 are labeled 鈥?x4", 鈥?x6", ..., 鈥?x12".
cells c1 thru c5 contain the appropriate fb design stress.
cells d1 thru d5 contain the appropriate fv design stress.
continuing on, cells a6 thru a10 are all labeled 鈥淪yp #2" species & grade, cells b6 thru b10 are labeled with 2x sizes, and so on.
my objective is to be able to select a specific lumber species & grade, via a validation list, say in cell f1. next, i wish to select a 2x size section with another validation list, say in cell f2. at this point, i would like to have the appropriate selected fb and fv values displayed in two additional cells, available for further calculations.

i can probably do this with nested if functions, but that would soon become very cumbersome. can anyone give me some guidance on how to do this easier?
you're on the right track.
just use data validation for the first two and vlookup for the last two.
but i'm still confused. let's say i enter a species and grade in one cell, and a lumber section in another cell. then, based on those two entries, how do i enter the table and find the row that matches both values, and return the appropriate value from the fb column?
from your description, you only have one species and grade for each size of timber for each fb fv.
either there is a formula connecting some of the data sets or you need to repeat some of the data
or am i missing something?
if all the species/grade categories each have exactly five different lumber sizes attached then try this:
at the bottom of your list, include a five item table that includes all five entries for your selected species/grade. do this with a grid of vlookup formulaes in a table that is 5l by 4c in size.
then, based on the selected lumber sizes, use a vlookup in that table to choose the proper fb and fb values.
you have an input cell for species grade, an input cell for lumber size, and two output cells for fb and fv.
no data validation is required. this will however, result in some standard spreadsheet errors if you mistype anything in either input cell. if that is not acceptable, maybe you can do some data validation in the input cells and still use the results for the table lookups to surpress them.
regards,
chichuck
my species & grade designations include df-l #1, df-l #2, df-l #3, syp #1, syp #2, syp #3, spf #1, spf #2, spf #3, hem-fir #1, hem-fir #2, hem-fir #3, ... and, for each of those, i have 5 section designations of 2x4, 2x6, 2x8, 2x10 & 2x12. i am trying to find a way to first enter the table and find all rows matching selected species & grade (from column 1), then secondly, select from those rows based on selected section size (column 2). then, third, read over to that selected row's fb column and select its fb value.
for future reference:
ok, my first post may have over simplified the effort as it assumed only one wood species.
i would recommend that you organize the data in columsn as you have suggested and sorted first by species.
then define a named range for all of the data associated with each wood species.
next use data validation (in order to use scroll box data entry) for the first two variables.
the last step would require nested if statements and vlookup commands to find the appropriate data.
this will get messy with the 12+ wood species that you would like to use.
this problem would be much easier to solve using the vba programming option within excel.
as a side question, do you typically perform designs using all of the above noted wood species and grades?
reducing the data to the top two or three most commonly specified species/grades would greatly simplify the task.
thanks for the info ttk - i am trying to develop my tall wall stud & post program for analyzing single and built-up wood
ok, i think i found a fairly easy solution without getting into vba programming but it would mean reorganizing your data.
if you eliminated the second column of data (section size) and instead made these sizes column headings you will have a true array with the fb values being the raw data in the array. i have tried to show the format below but it may get messed up after submiting the post.
you could use data validation to select the first two inputs as you previously planned. next use an intermediate step to find the correct row number and column number in the array using the match function.
lastly use the column number and row number from the previous step along with the index function to find the appropriate value of fb.
try it and let us know if it works.
2x4 2x6 2x8 2x10 2x12
dfl #1 400 600 800 1000 1200
dfl #2
dfl #3
syp #1
syp #2
syp #3
spf #1
spf #3
spf #3 350 550 750 950 1150
have you tried the engineering spreadsheets forum (

__________________
借用达朗贝尔的名言：前进吧，你会得到信心!
[url="http://www.dimcax.com"]几何尺寸与公差标准[/url]