Excel Engineering Spreadsheet Applications

[NAUI]

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 “SYP #1" species & grade.
Cells B1 thru B5 are labeled “2x4", “2x6", ..., “2x12".
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 “SYP #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?

 

[TTK]

You're on the right track.

Just use data validation for the first two and vlookup for the last two.

 

[NAUI]

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?

 

[StephenA]

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?

 

[chichuck]

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

 

[NAUI]

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.

 

[HgTX]

For future reference:

forum770

Hg



Eng-Tips policies: faq731-376

 

[TTK]

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.

 

[NAUI]

Thanks for the info TTK - I am trying to develop my Tall Wall Stud & Post program for analyzing single and built-up wood members subjected to both vertical and horrizontal loading, and at various spacings. To do this, I need the option of selecting different species and grades, based on availability (and sometimes existing conditions), as well as variou section sizes. The program is essentially done, but at this point, I am having to manually input the design stresses to obtain results. This is the last piece to automate to complete the program.

My database is 58 rows x 8 columns. In two cells of the main program, I select first, species & grade (S&G), and second, section size (SS). Based on those two selection, I want to then automatically go into the data table, search down column 1 for all entries that match the selected S&G (there will be 5 of them). Next, I want to search column 2 of only those 5 rows for the selected SS (there will be one of those). Next, I want to move over one column cell address from the identified SS and return that cell value to a designated cell address back in the main program, and I'm done. There must be way to get this done.

I see this as a hierarchical process - first you search and reduce the table down to 5 possible rows (matching criteria 1), then reducing down to 1 row (matching criteria 2), then reading adjacent cells and extracting the numbers from the final row.

Perhaps there is a way to do just the part 1 search, and copy the 5 identified rows to a new field, 5 rows x 8 columns. Then do the part 2 search and copy the one selected row to a new field, 1 row x 8 columns. Finally, use a simple vlookup to pull out my data to various program cells.

 

[TTK]

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

 

[dbuzz]

Have you tried the Engineering Spreadsheets forum (forum770)?