Complex Table. Need specific value out for multiple spec 1

[SnapperHG1]

Hope that I'v not mislead too many people with the subject!

I have relatively large table, 14 columns, and need figure from the body of the table based upon the Load Duration (Long Term, medium Term or Short and Very Short term), the member thickness and the bolt diameter. To further complicate, is load parallel or perpendicular to grain.

I know NO VBA, but tfrom the overview I have of it think this might be the best method. Can anyone advise / tell me the code I require and how to implement?

I have copied and pasted the table direct from Excel, so hopefully it will copy and paste out again?

Thanks in advance.


Load Duration Min Member Thickness Direction of loading
Parallel to Grain Perpendicular to Grain
Bolt Diameter Bolt Diameter
M8 M10 M12 M16 M20 M24 M8 M10 M12 M16 M20 M24
Long-Term 16 0.48 0.59 0.69 0.88 1.05 1.2 0.43 0.52 0.6 0.73 0.84 0.93
22 0.66 0.81 0.95 1.21 1.44 1.65 0.6 0.72 0.82 1.01 1.16 1.27
35 1.06 1.29 1.52 1.93 2.3 2.62 0.95 1.14 1.31 1.61 1.84 2.03
44 1.29 1.62 1.91 2.43 2.89 3.29 1.2 1.43 1.65 2.02 2.32 2.55
47 1.33 1.74 2.04 2.59 3.09 3.52 1.23 1.53 1.76 2.16 2.47 2.72
60 1.52 2.07 2.6 3.31 3.94 4.49 1.4 1.89 2.25 2.75 3.16 3.47
72 1.55 2.28 2.93 3.97 4.73 5.39 1.47 2.06 2.64 3.3 3.79 4.17
97 1.55 2.4 3.42 5.05 6.37 7.26 1.47 2.26 3.07 4.43 5.11 5.62
147 1.55 2.4 3.42 5.94 8.4 10.63 1.47 2.26 3.18 5.42 7.09 8.51
Medium-Term 16 0.61 0.75 0.88 1.12 1.33 1.52 0.55 0.66 0.76 0.93 1.07 1.18
22 0.84 1.03 1.21 1.54 1.83 2.09 0.76 0.91 1.05 1.28 1.47 1.62
35 1.34 1.64 1.92 2.45 2.92 3.32 1.21 1.45 1.66 2.04 2.34 2.57
44 1.52 2.06 2.42 3.08 3.66 4.18 1.41 1.82 2.09 2.56 2.94 3.23
47 1.58 2.18 2.58 3.29 3.91 4.46 1.46 1.94 2.23 2.74 3.14 3.45
60 1.72 2.45 3.17 4.2 5 5.7 1.63 2.23 2.85 3.49 4.01 4.41
72 1.72 2.66 3.47 5.04 6 6.84 1.63 2.47 3.11 4.19 4.81 5.29
97 1.72 2.66 3.78 5.99 8.08 9.21 1.63 2.49 3.52 5.21 6.48 7.13
147 1.72 2.66 3.78 6.57 10.02 12.55 1.63 2.49 3.52 5.99 8.45 10.38
Short- and very short-term 16 0.7 0.85 1 1.28 1.52 1.73 0.63 0.75 0.87 1.06 1.22 1.34
22 0.96 1.17 1.38 1.75 2.09 2.38 0.86 1.04 1.19 1.46 1.67 1.84
35 1.51 1.87 2.19 2.79 3.32 3.79 1.38 1.65 1.9 2.32 2.66 2.93
44 1.69 2.33 2.76 3.51 4.18 4.76 1.55 2.07 2.38 2.92 3.35 3.68
47 1.75 2.39 2.95 3.75 4.46 5.09 1.61 2.19 2.55 3.12 3.58 3.94
60 1.84 2.72 3.49 4.79 5.7 6.49 1.74 2.46 3.14 3.98 4.57 5.02
72 1.84 2.84 3.85 5.73 6.84 7.79 1.74 2.66 3.43 4.78 5.48 6.03
97 1.84 2.84 4.04 6.63 8.92 10.5 1.74 2.66 3.76 5.75 7.38 8.12
147 1.84 2.84 4.04 7.01 10.7 13.88 1.74 2.66 3.76 6.4 9.38 11.42

 

[xlbo]

Can you give an example of a set of criteria and which value you would want to be returned ?

Have a feeling this can be done via the SUMPRODUCT formula

 

[SnapperHG1]

Apologies. Good point!

I need to select the apropriate figure from the body of the table dependant on whether the case is parallel to the grain, Long, Med or Short term, Member Thickness and Bolt dia. hence the complexity. I had considered If statements, but couldn't get my head around how I would 'filter' on these criteria and dump the answer in a specific cell.

Thanks again

 

[xlbo]

......and the criteria plus result ???

I think this is going to be do-able with 2 IFs plus a couple of VLOOKUPs

I need a set of criteria and the result to be sure though....this is so I can understand the logic of what each bit of the criteria means....

 

[GlennUK]

Change your titles to be ParaM8, ParaM10 .... PerpM24 across the top of the table, and LT22, LT35, LT44 ... MT22 ...MT147, ST147 down the left side.

Then your formula would be similar to:
=INDEX(DataArea,MATCH("MT147",LeftTitles,0),MATCH("PerpM24",TopTitles,0))

replacing DataArea with the reference of the figures, and so on.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SnapperHG1]

xlbo,

I have another sheet where I enter the criteria, such as Load Duration, member thickness, etc, and the result is the corresponding value in the table, i.e.

A Long Term Load, in 47mm thick material, using an M12 bolt, with loading parrallel to grain would result in 2.04 in the table. (the pasted result above seems to have indented all the rows with the term in. They are in a separate column in Excel)

Hope that this explains a little more clearly. Apologies if not.

Glenn UK,

I think I understand, but if you would be kind enough to confirm the following/ correct me?

a)DataArea is the whole table I pasted.
b)MT47, replaces the M8, M10, etc.
c)LeftTitles is the name for the 16, 22, 35 etc cells on the left hand side.
d)I repeat the MATCH scetion for every M reference in teh table.

Thanks again

Steve

 

[GlennUK]

Hi Steve,

a) No, DataArea will be the reference to the values only, i.e. starting at the cell with 0.66

b) No, ParaM8 replaces the first M8. Para is placed at the front of M8 M10 for the first 6 titles ( as that half of the table is for "Parallel to grain" ), and Perp is placed at the front of the last 6 titles across the top ( as that half of the table is for "Perpendicular to grain" ).

c)LeftTitles is the reference the range containing the titles on the left side of the table ... which I said should be altered to LT22, LT35, LT44 ... MT22 ...MT147, ST147 - meaning LT22 replaces 22 for the Long Term and so on ( I hope you get the idea ).

d) The formula is to be used pretty much as is, with no extra MATCH entries! Use your criteria to generate the search items ... a Long Term Load, 47mm thick material, using an M12 bolt, with loading parrallel to grain would have search items of LT47 and ParaM12.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

DataArea starts with cell with value 0.48 not 0.66 ( I misread the table ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SnapperHG1]

Glenn,

I think I understand. I'm going to print this off and have a go Tuesday am. It's now Friday pm and I've just spent the last three days doing nothing but Excel spreadsheets like this one and I'm going up the wall with them!!

Many, many thanks. The more I read your explanation the more I think I understand. I think I need to put some hidden cells in to create a reference LT47 for Long term 47mm thick and ParaM12 based upon user selection, but I think that's fairly easy using the VLOOKUP function and a mod of the table. Might even try this pm....

Would it be easier, do you think, if I split the table into six tables? Parrallel and Perp, Long, Med and Short from the user selection point of view. That way I could set up 6 'DataArea' references.

Alternatively, thinking of the actual real design, it might be better to limit the spreadsheet to perp or parallel. From what I can see parallel is the worst / limiting case. Hmmm