I am not sure on your "Not"
but try
=if(sheet1!B1="option1",sheet2!G4,if(sheet1!B1="op tion2",sheet2!K7,"otherwise"))
Note I used the same lowercase "sheet" you did, Make sure they match your
actual sheet names.

this equation has the cell equal sheet2!G4 if sheet1!B1="option1"
I could interpret you equation in other ways.

Also if the equation is on sheet 1 you do not have to have the sheet1! in
the equation.

"Pat" wrote:

A1 in sheet1 contains a reference to a cell in sheet2:

=sheet2!G4

what I want to include in the above formula is a reference to a cell value
in sheet1 B1:

=IF(NOT(B1="option1"),=sheet2!G4,(B1="option2"),=s heet2!K7)

The above is clearly not correct but is only a way to illustrate what I am
trying to explain. If you find it difficult to make sense of it I will try
to explain in a better way.

Pat