referencing a cell to a range in different worksheet

Question

How can I reference a cell to a range in excel?

For eg: I want to reference cell A1 of Sheet1 to Range A1:A10 of Sheet3.

• Only one cell in the range A1:A10 will have value in it or no cell will have values.
• But no two cells in the range will have value.

So if Cell A6 of Range A1:A10 in Sheet3 has this value then I want to display that value in Cell A1 of Sheet1.

How to best acoomplish this?

Thanks in advance.

Answer 1

If only one cell will ever have a value, you can just use =sum(Sheet3!A1:A10). It will return a value of 0 if nothing is filled in.

Answer 2

=IFERROR(LOOKUP(2,1/(Sheet3!A1:A10<>""),Sheet3!A1:A10),"")