| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shorten Formula
I am sure that this formula can be shorter and take out the repeats.
=IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Movie List'!B:C,1,FALSE)),IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Janets Movies'!A:B,2,FALSE)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE"),"I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE") |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shorten Formula
There are no repeats, it is against different sheets.
You could define a name of say look_val for MID(H3,FIND("-",H3)+2,1000) and use that in the formula =IF(ISNA(VLOOKUP(lookup_val,'Movie List'!B:C,1,FALSE)), IF(ISNA(VLOOKUP(lookup_val,'Janets Movies'!A:B,2,FALSE)),"","Janet has "&lookup_val&" Remove from QUE"), "I have "&lookup_val&" Remove from QUE") but actually VLLOKUP is overkill, only needs MATCH =IF(ISNA(MATCH(MID(H3,FIND("-",H3)+2,1000),'Movie List'!B:B,0)), IF(ISNA(MATCH(MID(H3,FIND("-",H3)+2,1000),'Janets Movies'!A:A,0)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE"), "I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE") or =IF(ISNA(MATCH(lookup_val,'Movie List'!B:B,0)), IF(ISNA(MATCH(lookup_val,'Janets Movies'!A:A,0)),"","Janet has "&lookup_val&" Remove from QUE"), "I have "&lookup_val&" Remove from QUE") -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Lemmesee" wrote in message ... I am sure that this formula can be shorter and take out the repeats. =IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Movie List'!B:C,1,FALSE)),IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Janets Movies'!A:B,2,FALSE)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE"),"I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shorten Formula
Perhaps put
=MID(H3,FIND("-",H3)+2,1000) in another cell, e.g. I3 and then use =IF(COUNTIF('Movie List'!B:B,I3),"I have "&I3&" Remove from QUE",IF(COUNTIF('Janets Movies'!A:A,I3),"Janet has "&I3&" Remove from QUE","")) "Bob Phillips" wrote: There are no repeats, it is against different sheets. You could define a name of say look_val for MID(H3,FIND("-",H3)+2,1000) and use that in the formula =IF(ISNA(VLOOKUP(lookup_val,'Movie List'!B:C,1,FALSE)), IF(ISNA(VLOOKUP(lookup_val,'Janets Movies'!A:B,2,FALSE)),"","Janet has "&lookup_val&" Remove from QUE"), "I have "&lookup_val&" Remove from QUE") but actually VLLOKUP is overkill, only needs MATCH =IF(ISNA(MATCH(MID(H3,FIND("-",H3)+2,1000),'Movie List'!B:B,0)), IF(ISNA(MATCH(MID(H3,FIND("-",H3)+2,1000),'Janets Movies'!A:A,0)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE"), "I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE") or =IF(ISNA(MATCH(lookup_val,'Movie List'!B:B,0)), IF(ISNA(MATCH(lookup_val,'Janets Movies'!A:A,0)),"","Janet has "&lookup_val&" Remove from QUE"), "I have "&lookup_val&" Remove from QUE") -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Lemmesee" wrote in message ... I am sure that this formula can be shorter and take out the repeats. =IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Movie List'!B:C,1,FALSE)),IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Janets Movies'!A:B,2,FALSE)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE"),"I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shorten Formula
I appreciate the suggestions, Much thanks
"daddylonglegs" wrote: Perhaps put =MID(H3,FIND("-",H3)+2,1000) in another cell, e.g. I3 and then use =IF(COUNTIF('Movie List'!B:B,I3),"I have "&I3&" Remove from QUE",IF(COUNTIF('Janets Movies'!A:A,I3),"Janet has "&I3&" Remove from QUE","")) "Bob Phillips" wrote: There are no repeats, it is against different sheets. You could define a name of say look_val for MID(H3,FIND("-",H3)+2,1000) and use that in the formula =IF(ISNA(VLOOKUP(lookup_val,'Movie List'!B:C,1,FALSE)), IF(ISNA(VLOOKUP(lookup_val,'Janets Movies'!A:B,2,FALSE)),"","Janet has "&lookup_val&" Remove from QUE"), "I have "&lookup_val&" Remove from QUE") but actually VLLOKUP is overkill, only needs MATCH =IF(ISNA(MATCH(MID(H3,FIND("-",H3)+2,1000),'Movie List'!B:B,0)), IF(ISNA(MATCH(MID(H3,FIND("-",H3)+2,1000),'Janets Movies'!A:A,0)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE"), "I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE") or =IF(ISNA(MATCH(lookup_val,'Movie List'!B:B,0)), IF(ISNA(MATCH(lookup_val,'Janets Movies'!A:A,0)),"","Janet has "&lookup_val&" Remove from QUE"), "I have "&lookup_val&" Remove from QUE") -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Lemmesee" wrote in message ... I am sure that this formula can be shorter and take out the repeats. =IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Movie List'!B:C,1,FALSE)),IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Janets Movies'!A:B,2,FALSE)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE"),"I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Shorten a Formula | Excel Worksheet Functions | |||
| Shorten an IF formula | Excel Discussion (Misc queries) | |||
| Shorten sumproduct formula | Excel Discussion (Misc queries) | |||
Linear Mode
