Absolute referencing in different excel sheet

[niam07]

Hi ya,

I have an excel sheet with three sheets.

First sheet has certain information as follows.

S.No	Name	Job
1	A	X
2	B	X1
3	C	X2
4	D	X3

<tbody>
</tbody>

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

S.No	Name	Job
1	A	X

<tbody>
</tbody>

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())