INDIRECT + Insert row on another sheet

Question

I have the following formula:

=INDIRECT(A1&"!R92C3",0)

with sheet name in A1. That is (if A1 = Sheet1) the formula returns the contents of Sheet1!C92.

Now the person I am trying to help (with Excel 2003) need to insert a row above row 92. When doing so he wants the formula to return the contents of Sheet1!C93.

I tried the formula: =INDIRECT(A1&"!R"&ROW(Sheet1!92:92)&"C3",0), that is with the sheet name hard coded in the ROW-part of the formula. It works but of course I would like to avoid the hard coding of the sheet name.

Then I tried =INDIRECT(A1&"!R"&INDIRECT(A1&"!"&ROW(92:92),)&"C3",0) but I only get #REF!

I know that as from Excel 2007+ I could use Table, but is there another way when using Excel 2003?

Hans Knudsen

Answer 1

Jeeped

Thank you - yes that woud be a way to go.

Another way which I just discovered myself is as follows:

In Sheet1!A1 I have the text Sheet5

In Sheet1!A2 I have =ROW(Sheet5!C92)

In Sheet1!A3 I have the formula: =INDIRECT("'"&$A$1&"'!R"&A2&"C3",0)

Now I can insert rows above row 92 and the above formula will adjust accordingly

With Excel 2007+ it would of course be much easier by making the data range a Table.

Answer 2

... if your formulas are entered on for example Sheet1 and A1 holds for example Sheet5 meaning that i want to get the value from Sheet5!C92 and I on Sheet5 inserts a row above row 92 then none of your formulas work....  

I see what you are describing and I do not see any way to change the remote row reference. I would suggest going to a named range reference where the C92 cell is named the same on each of the worksheets with the worksheet name as a prefix. For example:

Sheet2×MyCell

Sheet3×MyCell

Sheet4×MyCell

Sheet5×MyCell

... etc.

Your formula then becomes,

=INDIRECT(A1&"×MyCell")

Answer 3

Jeeped

Thank you, but obviously I have not explained myself very well.

All your formulas work fine if they are on the same sheet as the sheet specified in A1.

But if your formulas are entered on for example Sheet1 and A1 holds for example Sheet5 meaning that i want to get the value from Sheet5!C92 and I on Sheet5 inserts a row above row 92 then none of your formulas work.

Hans Knudsen

Answer 4

Hans Knudsen,

Try

=INDIRECT(A1&"!R"&ROW(Sheet2!92:92)&"C3",0)

___________

Regards, Tom

Answer 5

.. Then I tried =INDIRECT(A1&"!R"&INDIRECT(A1&"!"&ROW(92:92),)&"C3",0) but I only get #REF!

Just change the INDIRECT(ROW()) function pair to an equivalent ROWS() function like this,

=INDIRECT(A1&"!R"&ROWS($1:92)&"C3",0)

... or,

=INDIRECT(A1&"!C"&ROWS($1:92))

Edit: I beliv e your original method would have worked with,

=INDIRECT(A1&"!R"&ROW(92:92)&"C3",0)