Change cell referance based on value

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"),=sheet2!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

I am not sure on your "Not"
but try
=if(sheet1!B1="option1",sheet2!G4,if(sheet1!B1="option2",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"),=sheet2!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
>
>
>

Thankyou BJ


"bj" <bj@discussions.microsoft.com> wrote in message
news:13F9915E-1C0B-4A1B-9BDA-1A7C5BE2BE7A@microsoft.com...
>I am not sure on your "Not"
> but try
> =if(sheet1!B1="option1",sheet2!G4,if(sheet1!B1="option2",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"),=sheet2!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
>>
>>
>>