Hi everyone
I am trying to modify the formula below but if u can think of a better approach, your input will be appreciated
<code>=IF(A2=0,"",IF(ISERROR(FIND("extra",LOWER(J2))),IF(ISERROR(OFFSET(PROJECTS[[#Headers],[PROJECT NAME]],MATCH(MID(H2,FIND("project",LOWER(H2)),FIND(" ",H2,FIND("project",LOWER(H2)))-FIND("project",LOWER(H2))),PROJECTS!B$2:B$2000,0),0)),"Regular",OFFSET(PROJECTS[[#Headers],[PROJECT NAME]],MATCH(MID(H2,FIND("project",LOWER(H2)),FIND(" ",H2,FIND("project",LOWER(H2)))-FIND("project",LOWER(H2))),PROJECTS!B$2:B$2000,0),0)),"Extra"))
to remove the nested find for the word "Project"
</code>
Would it be possible to have this formula work without looking for a project name containing "project" all projects are differently named and some even have commas in them can this formula be adapted to pickup any strings written in column B of "projects" sheet ?
and searching through Column H's Various strings
and see if any of the projects are found and return the name of that project
The projects are written on a sheet called "Projects"
Column A has an Sorting order number to help sort put the projects in order not to get false positives for incomplete matches.
Column B has the project IDs
Column C has the project names
and the formula will go in Column R of a sheet called "LIVE ALL"
also
if A=0 should just return "" (blank)
if Column J of "LIVE ALL" has the word "EXTRA" written in it it should just return "Extra"
and if no projects found in H it should return "Regular"
I am trying to modify the formula below but if u can think of a better approach, your input will be appreciated
<code>=IF(A2=0,"",IF(ISERROR(FIND("extra",LOWER(J2))),IF(ISERROR(OFFSET(PROJECTS[[#Headers],[PROJECT NAME]],MATCH(MID(H2,FIND("project",LOWER(H2)),FIND(" ",H2,FIND("project",LOWER(H2)))-FIND("project",LOWER(H2))),PROJECTS!B$2:B$2000,0),0)),"Regular",OFFSET(PROJECTS[[#Headers],[PROJECT NAME]],MATCH(MID(H2,FIND("project",LOWER(H2)),FIND(" ",H2,FIND("project",LOWER(H2)))-FIND("project",LOWER(H2))),PROJECTS!B$2:B$2000,0),0)),"Extra"))
to remove the nested find for the word "Project"
</code>
Would it be possible to have this formula work without looking for a project name containing "project" all projects are differently named and some even have commas in them can this formula be adapted to pickup any strings written in column B of "projects" sheet ?
and searching through Column H's Various strings
and see if any of the projects are found and return the name of that project
The projects are written on a sheet called "Projects"
Column A has an Sorting order number to help sort put the projects in order not to get false positives for incomplete matches.
Column B has the project IDs
Column C has the project names
and the formula will go in Column R of a sheet called "LIVE ALL"
also
if A=0 should just return "" (blank)
if Column J of "LIVE ALL" has the word "EXTRA" written in it it should just return "Extra"
and if no projects found in H it should return "Regular"