I was searching a few hours, but couldn't come up with a solution.

I have 5 values on page one Min, susp min, avg, susp max, Max. On the second page i have the order Min, avg, Max, susp min, sups max. I can reference each by hand, but my idea was i refernce the avg from page one to page two and then i refernce each of the other cells by offsets fromt the first one.

My problem is the OFFSET =OFFSET((B1), -1, 0) command does not take "=$Sheet1.B2" as value, but instead the value of B2 on sheet1 .

This would be the idea

I googled a lot refernce to a cell on another page, different excel commands, but i did not find any solution, other than typing the page reference every time.

I was searching a few hours, but couldn't come up with a solution.

I have 5 values on page one Min, susp min, avg, susp max, Max. On the second page i have the order Min, avg, Max, susp min, sups max. I can reference each by hand, but my idea was i refernce the avg from page one to page two and then i refernce each of the other cells by offsets fromt the first one.

My problem is the OFFSET =OFFSET((B1), -1, 0) command does not take "=$Sheet1.B2" as value, but instead the value of B2 on sheet1 .

This would be the idea

I googled a lot refernce to a cell on another page, different excel commands, but i did not find any solution, other than typing the page reference every time.

• excel
• excel-formula

Share Improve this question Follow edited Mar 19 at 19:16 Saeniv asked Mar 13 at 8:27 SaenivSaeniv 722 bronze badges 2

• 4 Your question is a bit unclear. Exactly what are you trying to achieve? =$'Sheet1'A3 is incorrect. – Paul Commented Mar 13 at 9:30
• 2 Why not simply use ='Sheet1'!A2 ='Sheet1'!A3 and ='Sheet1'!A4 ? Or ='Sheet1'!A2:A4 – P.b Commented Mar 13 at 12:20

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

If I understand your problem correctly, you can use the INDIRECT function. Enter the address from the other sheet in cell A1, for example Sheet1!A3 and use the following formula to offset that address: =OFFSET(INDIRECT(A1), -1, 0).

Perhaps this will help:

If you are using Excel 365: =FILTER(Sheet1!B$1:B$5,Sheet1!A$1:A$5=A1)

If you are using an older version: =INDEX(Sheet1!B$1:B$5,MATCH(A1,Sheet1!A$1:A$5,0))

Note: Be sure that the terms on both sheets are the same for example "susp max" on Sheet1 is not the same as "sups max" on Sheet2.