Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Tidying up formula
Hi,
I'm trying to minimise the size of a file by trimming down the formulae. Would anybody have any suggestions on the following =IF($E12="","",IF(Reintype($E12)="Assumed",VLOOKUP ($B$6&G12,[Data.xls]DECAP_ALL2!A:W,17+($I12-1),FALSE),IF(Reintype($E12)="Ceded",VLOOKUP($B$6&$ G12,[Data.xls]DECAP_ALL2!A:W,17+($I12-1),FALSE),""))) (note: "Reintype" is a user define function whcih tests to see if the content of Exx is within certain ranges of numbers |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Tidying up formula
One suggestion
=IF($E12="","",IF(OR(Reintype($E12)="Assumed",Rein type($E12)="Ceded"),VLOOKU P($B$6&$G12,[Data.xls]DECAP_ALL2!A:W,17+($I12-1),FALSE),"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul" wrote in message ... Hi, I'm trying to minimise the size of a file by trimming down the formulae. Would anybody have any suggestions on the following =IF($E12="","",IF(Reintype($E12)="Assumed",VLOOKUP ($B$6&G12,[Data.xls]DECAP_ ALL2!A:W,17+($I12-1),FALSE),IF(Reintype($E12)="Ceded",VLOOKUP($B$6&$ G12,[Dat a.xls]DECAP_ALL2!A:W,17+($I12-1),FALSE),""))) (note: "Reintype" is a user define function whcih tests to see if the content of Exx is within certain ranges of numbers |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Tidying up formula
"Bob Phillips" wrote: One suggestion =IF($E12="","",IF(OR(Reintype($E12)="Assumed",Rein type($E12)="Ceded"),VLOOKU P($B$6&$G12,[Data.xls]DECAP_ALL2!A:W,17+($I12-1),FALSE),"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul" wrote in message ... Hi, I'm trying to minimise the size of a file by trimming down the formulae. Would anybody have any suggestions on the following =IF($E12="","",IF(Reintype($E12)="Assumed",VLOOKUP ($B$6&G12,[Data.xls]DECAP_ ALL2!A:W,17+($I12-1),FALSE),IF(Reintype($E12)="Ceded",VLOOKUP($B$6&$ G12,[Dat a.xls]DECAP_ALL2!A:W,17+($I12-1),FALSE),""))) (note: "Reintype" is a user define function whcih tests to see if the content of Exx is within certain ranges of numbers Thanks for the reply Bob. I made a mistake in the formula though so I'll try that again. The difference is depending on whether the result is Assumed or Ceded there is a different lookup reference i.e. if assumed return index 15, if Ceded return index 17+($I12-1) The formula should have read: =IF($E12="","",IF(Reintype($E12)="Assumed",VLOOKUP ($B$6&G12,[Data.xls]DECAP_ALL2!A:W,15,FALSE),IF(Reintype($E12)="Ceded" ,VLOOKUP($B$6&$G12,[Data.xls]DECAP_ALL2!A:W,17+($I12-1),FALSE),""))) Thanks Again |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Tidying up formula
Paul,
if the only options fo Reintype(E12) are blank, "Assumed" and "Ceded" then the following should do. =IF($E12="","", VLOOKUP($B$6&G12,[Data.xls]DECAP*_ALL2!A:W, IF(Reintype($E12)="Assumed", 15, 17+($I12-1)), FALSE)) HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |