×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • 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!
  • Students Click Here

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

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

i know i know...

i know i know...

i know i know...

(OP)
Ok, i have been trying to put together a formula for days now, and can't seem to get it working.  What I have so far in cell H53

=B53*C53*IF(Q53="MOH OK",1,1.1346)*IF(R53="$",1,1.29)
             
           (Now MOH OK and $ are drop down selections)

B53=Qty  C53 = Cost  MOH and $ are multiplied to the total of B & C 53.  I have to manually put in 1.1346 and 1.29 instead of selecting a cell in Worksheet 2, it doesn't work any other way.  Now what I added  is the 15 selection under Organisation (cell S53)and want to associate the costs that I put to them that is on Worksheet 2 into the formula Prior to *IF(Q53="MOH OK",1,......

Can anyone let me know what I am missing or doing wrong?

RE: i know i know...

Not even sure what your question really is.  A selection on another worksheet should look like Sheet2!B1.  I don't see any problem with your equation accessing a cell on a different worksheet.

For that matter, even a different workbook can be accessed: [Book3]Sheet1!$A$1

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: i know i know...

(OP)
=B52*C52*IF(B79= "2.2P - Augsburg",1,'Labor Rate - DO NOT CHANGE'!B16)*IF(Q52="MOH OK",1,1.1346)*IF(R52="$",1,1.29), Its not taking the 1.4 that is on the second sheet ( Labor Rate - DO NOT CHANGE' !B16) Into account.  Plus i have 15 other choices with corresponding figures that I do not know how the incorporate into the formula

RE: i know i know...

(OP)
Ok, I think i find one piece of the puzzle out....

=B52*C52*IF(B79="2.2P - Augsburg",'Labor Rate - DO NOT CHANGE'!B16,0)*IF(Q52="MOH OK",1,Q48)*IF(R52="$",1,R48) it is working exactly....

(B79="2.2L - Augsburg",'Labor Rate - DO NOT CHANGE'!B17,0)
(B79="2.2O - Augsburg",'Other Rate - DO NOT CHANGE'!B18,0)
and so on,,,, However, I am stuck on adding the other 14 options....so if you select one of the 15 it changes the rate.

Thanks for your reply IRstuff

RE: i know i know...

I don't totally understand your problem but when there are that many choices a VLOOKUP is usually easier to code than multiple IF statements.

Maybe try a combination of VLOOKUP and INDIRECT.

For example, setup on Sheet3 your lookup table in columns:

Column A              Column B
lookup value          location of the reference value

2.2P - Augsburg       'Labor rate - do not change'!b16

etc

then
=INDIRECT(VLOOKUP(lookupvalue, sheet3!$a$2:$b$15, 2))
or something very close to that should return the value from the desired cell.

Another combination that you might use in the MATCH - INDEX combo, so if the INDIRECT - VLOOKUP combo doesn't get you that could be another option to pursue.

RE: i know i know...

(OP)
Thanks for the suggestion and will give it a shot...

Again, many thanks for all of your help!

RE: i know i know...

(OP)
Let me ask something.... You can't use a drop down for look up value can you?

Cell H3 input formula  =INDIRECT(VLOOKUP($B$79,$S$2:$T$16, 20))

Now $B$79 is a drop down selection list....
    $S$2:$T$16 is the list + Value
    20 = the value column.

Its coming up #ref!  I just don't know what I am doing wrong...following all directions and have done this before....

RE: i know i know...

(OP)
ok, duh,,, I figured it out... I was putting the actual column number, not the column number in the array... so sorry for my ignorance...

Now onto figuring out how to put it all together....

again, many thanks for your help and your patience


RE: i know i know...

(OP)
ok, excellent help,  this is what I ended up with and workied like a charm,,, again many thanks

=B51*C51*VLOOKUP($B$79,$S$2:$T$16, 2)*IF(Q51="MOH OK",1,$T$17)*IF(R51="$",1,$T$18)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

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!


Resources