i know i know... 1

[Harley78]

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?

 

[IRstuff]

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

 

[Harley78]

=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

 

[Harley78]

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

 

[bltseattle]

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.

 

[Harley78]

Thanks for the suggestion and will give it a shot...

Again, many thanks for all of your help!

 

[Harley78]

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....

 

[Harley78]

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

 

[Harley78]

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)