How can I do a lookup to a specific cell on another tab

[KevinM]

I am trying to create a formula to say go to cell "A3" in sheet 2 and
return what ever the value is in A3. Example if in sheet 2, cell A3
had the value of 100, i would like to create a lookup on sheet one
where i can type in A3 and it would return with the value of 100

 

[zvkmpw]

I am trying to create a formula to say go to cell "A3" in sheet 2 and

return what ever the value is in A3.  Example if in sheet 2, cell A3
had the value of 100, i would like to create a lookup on sheet one
where i can type in A3 and it would return with the value of 100

One way is to put
A3
(or another cell address) in Sheet1!A2 and put this in Sheet1!A1:
=IF(INDIRECT("Sheet2!"&A2)="","",INDIRECT("Sheet2!"&A2))

The purpose of the IF(...) is so you don't get zeros for cells in
Sheet2 that are empty.