G
Guest
I've created a spreadsheet to perform engineering calcs. My problem is that
I need to enter my data table by first selecting 1 criteria (to narrow the
choices to several rows), then selecting a second criteria (to further refine
choice to one of the previously selected rows), then read across to a
specific cell and select its value.
For my data table:
1. Column 2 contains the various wood species names.
2. Column 2 contains the various member sizes. So, for a given species,
there may be 5 sizes available, i.e. 5 rows with the same species name in
column 2, and 5 different sizes in column 3.
3. Column 4 contains a third bit of data that I am seeking.
4. Column 1 then contains the formula TRIM(CONCATENATE(data col 2,data col3))
5. Finally, the data table is sorted in ascending order around column 1.
For the procedure:
1. I use data validation in cell 1 to select wood species (SPF #1, SPF #2,
....) from a range.
2. I use data validation in cell 2 to select member size (2x4, 2x6, ...)
from a range.
3. I use a hidden cell 3 to concatenate the selected values from cells 1 & 2.
4. I then use VLOOKUP on cell 3 to find its value in column 1 of my data
range, then read over two columns for the desired result.
The procedure works for all combinations where the value in hidden cell 3
matches an entry in column 1 of the data table. But, if the concatenated
value of hidden cell 3 does not match any value in column 1, it selects an
arbitrary row and extracts the incorrect data value, instead of showing an
error or some indication that it can not be found.
I need to enter my data table by first selecting 1 criteria (to narrow the
choices to several rows), then selecting a second criteria (to further refine
choice to one of the previously selected rows), then read across to a
specific cell and select its value.
For my data table:
1. Column 2 contains the various wood species names.
2. Column 2 contains the various member sizes. So, for a given species,
there may be 5 sizes available, i.e. 5 rows with the same species name in
column 2, and 5 different sizes in column 3.
3. Column 4 contains a third bit of data that I am seeking.
4. Column 1 then contains the formula TRIM(CONCATENATE(data col 2,data col3))
5. Finally, the data table is sorted in ascending order around column 1.
For the procedure:
1. I use data validation in cell 1 to select wood species (SPF #1, SPF #2,
....) from a range.
2. I use data validation in cell 2 to select member size (2x4, 2x6, ...)
from a range.
3. I use a hidden cell 3 to concatenate the selected values from cells 1 & 2.
4. I then use VLOOKUP on cell 3 to find its value in column 1 of my data
range, then read over two columns for the desired result.
The procedure works for all combinations where the value in hidden cell 3
matches an entry in column 1 of the data table. But, if the concatenated
value of hidden cell 3 does not match any value in column 1, it selects an
arbitrary row and extracts the incorrect data value, instead of showing an
error or some indication that it can not be found.
10,3,0)