Absolute referencing in different excel sheet

[niam07]

Hi ya,

I have an excel sheet with three sheets.

First sheet has certain information as follows.

[TABLE="width: 500"]
<tbody>[TR]
[TD]S.No[/TD]
[TD]Name[/TD]
[TD]Job[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]X1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]X2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]X3[/TD]
[/TR]
</tbody>[/TABLE]

In second sheet, I sometimes cut and paste entire rows from first sheet. So as an example, my second sheet could be.

[TABLE="width: 500"]
<tbody>[TR]
[TD]S.No[/TD]
[TD]Name[/TD]
[TD]Job[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

My third sheet, has formulas so that it copies exact same information from sheet 1.

so, A1 in sheet 3 will be = 'Sheet1'!A1 and so on.

But, my issue is that once I cut some information from sheet 1 and paste it sheet 2,

Now,

in my sheet 3, my A2 = becomes 'Sheet2'!A2

What I want in Sheet 3 is always to look for absolute values in sheet 1,

A1 = 'Sheet1'!A1
A2 = 'Sheet2'!A2

and so on. Irrespective of whether I insert new rows, or cut existing rows in sheet 3, I want sheet 3 values to be copy cat of sheet 1 values. Always.

I tried INDEX and Indirect but could not get to work. Will be grateful for any help.

Regards,
Naim

 

[StephenCrump]

A1: =INDEX(Sheet1!A:A,ROW())