Sent.
Let me know how you're getting on.

On 21 July, 13:32, Derrick wrote:
ya you might as well send it. the email address is
i would prefer it if this email does not receive spam... that goes for
everyone reading this :P
thanks for your help!



"AB" wrote:
Hey Derrick,

We're in different timezones, therefore the lag.
So, i've managed to come up with a formula (as opposed to vba/macro)
solution. It's not yet as sophisticated as you wanted but it actually
does work and could be a good basis to buld on for further
enhancements.
The solution uses a couple of Array formulas and named ranges (one of
which is a 'relative' named range (as opposed to 'absolute' named
range) - one needs to be cautios with those but they do work and i've
been using them successfully for a while).

It could be quite tricky for me to explain here the whole thing and
then for you to try to replicate that at yours (i mean it could take
up to an hour or more), so if i could just forward you the workbook,
it might save us both some time. As i said, the file contains only
formulas and no macro - so no threat to your system. Let me know if it
would work for you and the email address i should send the file to.
If sending the file doesn't work for you, post back and i will try to
explain how the file works in here.

On 20 July, 18:18, Derrick wrote:
ahh ok. *i understand.
I will use different rows.
ie - for 4 diff calculations, i will use 4 rows.
that way, when everything is done, i can tally up all the steel needed.
so: *'no' to overwriting
possibly, i'll be using up to 30 rows.. it will depend on the amound of
calcs needed.

so im afraid the simpler answer is not possible..

"AB" wrote:
yeap, it does help.
My bad - my question regarding the count of entries wasn't clear
enough - what tried to find out is:
How many rows you'd anticipate in the Calc sheet?
Meaning - would you populate the 8 columns in row 2 and then select
the dropdonw (still in row 2) depending on what the dropdown let's you
select.
Then, when you get your next task/steel (i don't know how to call it -
that's whe you have your next need to calculate/select the appropriate
bars), would you enter the data in the same 8 columns in row 3, select
the dropdown.
Then in row 4..
etc.
OR
you'd always utilize just row number 2 in there - i.e., whenever you
need to get the appropariate available bar types you'd just overwrite
the data in the row 2?
The latter would be simplier and maybe even i could figure out how to
achieve that just with formulas (no macros/vba).

Is my question any clearer now?

On 20 July, 17:16, Derrick wrote:
ya, sorry i noticed the columns later. *
4 columns in Steel sheet ( actually there are more, but transferring of data
can be duplicated - (1)Number, (2)Name, (3)Description, (4+) Iy, Ix, Sy,
Sx,etc. - so i just put Iy, under the assumption that getting info will be
easy - using Lookup(), once the item number is selected.)
8 relevant columns in the Calc sheet
- max allowable/actual deflection (2)
- max allowable/actual stress1 *(2)
- max allowable/actual stress2 (2)
* * *- these three are similar - in that if one goes over, i need to get
steel to fix the problem, but the steel should be large enough to solve all
three problems - or using a max() function i think.
*- dropdown list (where item number and deflection are displayed in dropdown
list, but only the item number is displayed when one is checked. *if not
possible, then everything can be shown (description and number)) (1)
- and a desription column, where if above is possible, the descripion will
be displayed in a seperate column. (1)

as for the number of entries in the dropdown list, i would like to have all
possible steel selections shown. - that way we can select one depending on
what we need for the job

does this help?

"AB" wrote:
so, i've got 4 columns in Steel sheet:

Item
Name
Description
Iy

The Calc sheet structure got somewhat grabbed in your post. Could you
clarify what columns do you have in ther? I must be missing some
fields, am i?:
Max Deflection allowable
min Iy
item number
item description
dropdown

Also - how many entries you'd expect to have on the Calc Sheet? Always
just one and the dropdown would be 'effective' for that one or you'd
keep appending new entries all the time to the list?

On 20 July, 13:15, Derrick wrote:
hey AB. Now that the weekend is over,

Steel table/data:
* *a * * * * b * * * * * * * c * * * * * * * * d
item * * *name * * *description * * *Iy
*1... * * * bar * * * * * 2x3 * * * * * * *4.500
100 * * *channel * * *c3x4.1 * * * * 1.70
101 * * *channel * * *c3x5.4 * * * * 2.1
etc.

calc sheet:
* *a * * * * * * * *b * * * * * * * c * * * * * * * * *d * * * * * * * * * *
* * * * * * * * * *e * ...
1 * Max deflection * * * * * min Iy * * * * * * item * * * * * * * * * * * *
* * * * item
2 allowable * *actual * * * *required * * * *number * * * * * * * * * * * * *
description
3 * 0.5 * * * * * * .62 * * * * *2.1 * * * * *(dropdown here) * * * * * * * *
*(when checked,
* * * * * * * * * * * * * * * * * * * * * * * * * (001 - Bar 3X2 * * * * * *
* * * * *return info here)
* * * * * * * * * * * * * * * * * * * * * * * *..101 - Channel C3x5.4) * * *
* (channel- C3X5.4)

note that the cell blocks aren't the exact cell blocks, but only used for
reference.
also, i have 3 criteria i have to meet. *Max deflection, Stress 1, Stress 2.
if any of these are over, i ahve to check for a suitable steel type. so....
max() something.
if there is no steel available.. can there be a "not possible" option show up?

"AB" wrote:
It surely can be done.
What's the exact structure of your 'Steel' table/data?
What data exactly would be takend from your input table/data and passed over
to your 'Steel' data for checking?

"Derrick" wrote in message
...
so here's the situation:
1. i have to check deflection of a horizontal beam as a result of glass
weight on it.
if deflection is less than the acceptable limit, then everything is
peachy.
if not, then i have problems.
- one option, and the most popular over here, is to reinforce it by
inserting a steel member (tube or bar or channel etc).
the problem with doing this is that the deflection caused by the steel's
weight is often larger than the strength it can provide with its 'Iy'
property. (the higher the Iy, the smaller the amount of deflection.)
so. *now i have a table of steel, and its properties. - Ix, Iy, weight,
etc.
on another sheet.
2. if there is a problem with the deflection, i i would like to do an
automatic check through the list of steel to see which members have high
enough Iy value to counter: the original deflection + the deflection
caused
by the steel's weight.

so. *can I:
1. use a loop in a macro to go through my list of steel
2. (using a formula already set up) return a 'yes' or 'no' depending on
whether or not the above criteria are met.
3. click on a 'recommended steel type' cell block, and have a list of
steel
members which work drop down (from the returned 'yes' or 'no' loop)
ie: *A * * * * B
1 * *# * *description
2 * 101 * *2"x4" bar
return: 101 - " 2"x4" bar " *in another cell
4. have the selected steel member display its information on the sheet so
i
can see its properties and then calculate results which solve the
deflection.

i know. it's alot to ask. *any help would be spectacularly appreciated.
maybe a cake will be baked for you. * Who knows...- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -