Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Vlookup override
I have a form that I am creating and need your assistance. Currently I have
the following formula and it is working perfectly. =IF($J$7="Small",VLOOKUP(A13,'Property List'!$F$2:$G$31,2,FALSE),IF($J$7="Medium",VLOOKUP (A13,'Property List'!$F$2:$H$31,3,FALSE),IF($J$7="Large",VLOOKUP( Sales!A13,'Property List'!$F$2:$I$31,4,FALSE)))) The only thing is that now I need to add two more variables and I am unsure how to proceed. Regardless if $JS7 is Small, Medium or Large I need the formula to override if either any of the following happens: IF(E13="SC",5) or IF(E13="SNF",3) Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Vlookup override
Modify lookup range to suit =VLOOKUP(A13,Sheet1!A1:X31,IF(E13="sc",4,IF(E13="s nf",3,IF(J7="small",2,IF(J7="medium",3,IF(J7="larg e",4))))),0) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carolina" wrote in message ... I have a form that I am creating and need your assistance. Currently I have the following formula and it is working perfectly. =IF($J$7="Small",VLOOKUP(A13,'Property List'!$F$2:$G$31,2,FALSE),IF($J$7="Medium",VLOOKUP (A13,'Property List'!$F$2:$H$31,3,FALSE),IF($J$7="Large",VLOOKUP( Sales!A13,'Property List'!$F$2:$I$31,4,FALSE)))) The only thing is that now I need to add two more variables and I am unsure how to proceed. Regardless if $JS7 is Small, Medium or Large I need the formula to override if either any of the following happens: IF(E13="SC",5) or IF(E13="SNF",3) Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Vlookup override
Don,
Thanks for your quick response but I can't get it to work. Not sure why to use A13 in the formula. Basically there is a field that is populated at the top of the form that states if the property size is SMall, Medium and Large. Then they go into a grid at the bottom were they need to put name, type of industry, rent, etc. Depending on the size they selected at top there is going to be a commision percentage populating into cell K13. The commision rates are on tab "Property List" and range F2:I3. If they happen to select the industry type on E13 (which is a drop down menu) to be "SC" or "SNF" (and not the other options) then it shouldn't matter if they are Small, Medium or Large and should automatically populate a 5 if "SC" or a 3 if "SNF" Would this help? Thanks a million for looking into this for me :) Carolina "Carolina" wrote: I have a form that I am creating and need your assistance. Currently I have the following formula and it is working perfectly. =IF($J$7="Small",VLOOKUP(A13,'Property List'!$F$2:$G$31,2,FALSE),IF($J$7="Medium",VLOOKUP (A13,'Property List'!$F$2:$H$31,3,FALSE),IF($J$7="Large",VLOOKUP( Sales!A13,'Property List'!$F$2:$I$31,4,FALSE)))) The only thing is that now I need to add two more variables and I am unsure how to proceed. Regardless if $JS7 is Small, Medium or Large I need the formula to override if either any of the following happens: IF(E13="SC",5) or IF(E13="SNF",3) Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Vlookup override
Don,
I had to leave the office and I am returning only until now (maternity leave). I was unsuccesful with this suggestionbelow so it could be a matter of me not explaining my idea correctly . Is there any way I can email you the file I am working on and we can later post the ending result for others to benefit from? Thanks. Carolina "Don Guillett" wrote: Misunderstood. Try this. You need to decide on sc and snf importance =IF(E13="sc",5,IF(E13="snf",3,VLOOKUP(A13,Sheet1!E 1:X31,IF(J7="small",2,IF(J7="medium",3,IF(J7="larg e",4))),0))) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Modify lookup range to suit =VLOOKUP(A13,Sheet1!A1:X31,IF(E13="sc",4,IF(E13="s nf",3,IF(J7="small",2,IF(J7="medium",3,IF(J7="larg e",4))))),0) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carolina" wrote in message ... I have a form that I am creating and need your assistance. Currently I have the following formula and it is working perfectly. =IF($J$7="Small",VLOOKUP(A13,'Property List'!$F$2:$G$31,2,FALSE),IF($J$7="Medium",VLOOKUP (A13,'Property List'!$F$2:$H$31,3,FALSE),IF($J$7="Large",VLOOKUP( Sales!A13,'Property List'!$F$2:$I$31,4,FALSE)))) The only thing is that now I need to add two more variables and I am unsure how to proceed. Regardless if $JS7 is Small, Medium or Large I need the formula to override if either any of the following happens: IF(E13="SC",5) or IF(E13="SNF",3) Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Override conditional formatting with code | Excel Discussion (Misc queries) | |||
Need help to freeze or override a formula | Excel Discussion (Misc queries) | |||
Override a Formula | Excel Discussion (Misc queries) | |||
Override save as Dialog box | Excel Worksheet Functions | |||
Cell Value Override | Excel Discussion (Misc queries) |