Re: Advanced Design Tables

And don't forget that the values in a lookup table can also be dynamic. So you can have the table values change to ISO, DIN, ANSI, etc, by having one selection cell that drives formulas and values in the lookup table.

The row / column to return can also be a lookup so for example use an Hlookup in the Vlookup to select what column to return for the given input. Then throw in some IF and Choose functions for good measure.

And if you feel really challenged, play with the Indirect function which uses a cell containing an address to lookup a value in a different cell.

Be creative. Excel has a lots to offer.

Good Luck,

Tony Noll

You know, some people would call us nuts, or nerds (my kids) but this is getting exciting. I see it as becoming a challenge to see who comes up with the most elegant, simple, useful, feature-filled, (did I say simple?), engineering related, loaded design table. No, I don't have the time to just sit around and work on this, but just think of the possibilities. I think that since this is Matt's project, he should pony up the prize. How about......weekend for 2 in Paris. :-)

WT

Paris? Bah! I'd rather have a trip for one to drive the last of Union Pacific's Big Boy locomotives. Droool......

I have another idea cooking, but I havent had time to implement it yet. I also discovered last night a thing called an "intersection" operator. It returns the value from the cell at the intersection of a row and a column in a table. I hope to have it look up a size and length for a SHCS, and set the appropriate config.

Another thing I noticed, if your model can update the DT, data you don't want changed can get mangled. That is, unless the data is the result of a formula. Entering data with a simple formula seems to lock the data against being updated by the model.

Paris was just the first "exotic" idea that popped into my mind - it needed something like that for the effect. I'm surprised Matt didn't jump in yet.

Anyway, I haven't tried it, but can you just lock a cell if you don't want it changed? But then that brings up the question of why have it updateable at all. But to answer that, if you don't, it won't even let you look at a property because it thinks you are going to change it. Frustrating at times.

WT

Hi Tony-

So true - learn excel and things open up. I like this little snippet, since IFs were mentioned:

REFERENCE TO A1 =>

IF(A1

I know about cell locking, but I'm not sure why I didn't try it. Maybe because I was taking the opportunity to write a formula to copy and paste into other rows.

OK, now I'm stumped. When trying to look up a screw length, the lookup tables sometimes round down, which results in a screw that's too short. Is it possible to lookup values that are only larger than the input value? I could use the nested IFs, but I don't think I can nest them deep enough (more than 7).

Wayne,

Yeah, a prize sounds cool, but I was thinking more like a coupla beers for everyone that contributes at the next SW World we're all at instead of a trip to Paris. ...although, maybe I could expense the trip through the user group...

I'm just sitting back, copying all the good stuff to a text file, downloading examples, making sample DTs, and starting to outline the presentation. I already have enough stuff for 3 meeting presentations. This is excellent information. We ought to do this more often!

matt

"Wayne Tiffany" wrote in news:c4h3ug$2j0e0s$ snipped-for-privacy@ID-201804.news.uni-berlin.de:

Dale,

The way the lookup works (with false or blank as the last argument) is that it scans down the target row / column until it reaches the first value that is larger than the search value. It then backs up to the previous row / column and returns the offset value. So you may need to construct your lookup table values s.t. the longer value is always returned.

If the last argument is true, the lookup will only return a value if there is an exact match, else it returns the error #N/A I believe. You can use one of the information functions such as ISNA() to check this.

Hope this helps,

Tony Noll

"Tony" wrote in news: snipped-for-privacy@comcast.com:

Thanks. I'm going to have to think a little more before I can implement this. Because of gaps in the available lengths, I may need a table for each nominal size. I'll have to experiment more.

New topic every week?

Dale Dunn wrote in news:Xns94C1DCC278F1Cdaledunnatjamestoolc@65.24.7.150:

How about every month when I have to write a new user group presentation? Sure is a lot easier when you guys do my homework for me!

matt

That would work for me. I was just being greedy when I asked for a useful thread every week. I'd complain about doing you work for you, but I learned so much this last time, i'd better keep my mouth shut.

Plus, when Matt gets a nice presentation worked out, you can talk your users group into inviting him in so you can see it!

Jerry Steiger Tripod Data Systems "take the garbage out, dear"

Matt,

Sorry it took so long to get back to you. In my opinion one of the biggest time savers is figuring out how to copy/paste names or create names inside of SW that are spelled correctly.

If you copy the text in the Component Property dialog box of an assembly to get the complete spelling and then paste the text into the formula bar of excel, the result will be correct. However, you cannot paste directly into the cell. A preselection list in Excel helps too.

Planning user group meetings takes alot of time. You a great to do this month after month. The next one in New England is on April 13 - as always, you and any other sw user are invited. The event keeps getting bigger. I am fortunate that multiple resellers help me.

Regards, Marie