Hello,

I am trying to find a solution to a a problem with a formula i created.

I have the following formula/s
=IF('Project Summary'!A1<-1000,"2","1")

=SWITCH('Project Summary'!A1,"Yes", "1", "No","2","Not on Tracker","1")

Which work fine, however when i filter the Project Summary (Project Name) column, the formula displays different results (as it is looking at the generic cell) How can i make the formula look for a specific cell only? In Column A (range name rngProjNm) i have the Project Names (Project 1, Project 2, Project 3, Project 4, etc etc) and I want the formula to specifically reference a specific cell (I have the Project Names on A summary sheet so it will reference that) even if i filter the source data.

Any thoughts? I think i can use an XLOOKUP to reference it, but not sure how to inbed that in the formula