|
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") |
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") |
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") |
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") |
| All times are GMT +1. The time now is 03:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com