INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Member Login

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Eng-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...It's extraordinarily refreshing to see truly expert advice without having to wade through hipper than thou attitude..."

More...

Geography

Where in the world do Eng-Tips members come from?

Click Here To Find Out!

Eng-Tips Shirts!

Get your
Eng-Tips Forums
SWAG here!

Home > Forums > Engineering Computer Programs > General Engineering Programs > -Engineering spreadsheets Forum

Offset and match functions

Read More Threads Like This One

AELLC (Structural)

23 Jan 12 13:11

I am trying to devise a method to input a reaction load and several other variables, and the output would be a type of support hanger.

This seems to be getting a bit too cumbersome because of all the variables, and I was looking for a more efficient (but non-VBA) formula.

In the attached Excel file, cell D16 is a copy of the actual formula (still not complete).

Cell D9 is a simplified formula with 2 "IF" statements to illustrate what I am trying to accomplish. I am looking to reduce it to only one "IF" statement, but am stymied.

i.e., depending on whether cell D5=1 or cell D5=2, need to go to match array I8:I13 or J8:J13, respectively.

electricpete (Electrical)

23 Jan 12 13:49

thread770-281108: switch or case statement for excel spreadsheet

Switch function was described in above thread. I have found it sometimes helps simplify nested if logic. I haven't studied your particular formula enough to figure out if it will help. But it's a good tool to have in your belt imo.

=====================================
(2B)+(2B)' ?

AELLC (Structural)

23 Jan 12 14:05

LOL, this has been frustrating me for 3 days, but posting it here seems to have jarred the brain cells.

I didn't realize until now that in the MATCH function, array can be a formula i.e. IF(D5=1,I8:I13,J8:J13

since D5 is data-validated and the only possible values are 1 and 2.

IDS (Civil/Environmental)

27 Jan 12 0:23

Pete - thanks for the reminder of the Switch function, I'd forgotten about it (both the VBA version, and your UDF).

I've just posted an example on my blog:

http://newtonexcelbach.wordpress.com/2012/01/27/the-switch-function-vba-and-udf/

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!

Back To Forum

Back To -Engineering spreadsheets