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?
=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...
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...
RE: i know i know...
=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...
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...
Again, many thanks for all of your help!
RE: i know i know...
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...
Now onto figuring out how to put it all together....
again, many thanks for your help and your patience
RE: i know i know...
=B51*C51*VLOOKUP($B$79,$S$2:$T$16, 2)*IF(Q51="MOH OK",1,$T$17)*IF(R51="$",1,$T$18)