How to show reference another sheet

[nburaq]

Hi,
Today I struggle really simple problem. I have never thought this will be a problem. I have two sheets namely sheet1 and sheet2 and I would like to transfer all cell values in sheet1 to sheet2. What I mean;
In sheet1 C5 will be equal to sheet2 D64
C6 = sheet2!E64
C7 = sheet2!F64

when I pull down cross =sheet2!D$64 starting from C5 does not change to sheet2!E$64 instead copies exactly same values to C6. I don't have this issue if I don't show another sheet as reference.

I will be so glad if you could help me.

Thanks

 

[Joe4]

Here is one way.

Place this formula in cell C5 and copy down:

=OFFSET(Sheet2!$D$64,0,ROW()-5)

 

[nburaq]

Here is one way.

Place this formula in cell C5 and copy down:

=OFFSET(Sheet2!$D$64,0,ROW()-5)

Wow what a practical and genius way! Thanks a lot Joe!
So what I understand if I show a cell in an another sheet as a reference, it is not possible to auto complete by only locking columns or row number(sheet2!E$64) actually by locking only row number works strangely (sheet2!$E64-->sheet2!$E65)

 

[Joe4]

Since you are wanting to to pull the values from across a single row (only columns change) as your copy the formula down a single column (rows change), what I elected to do is to take your initial starting point, and lock it down (Sheet2!$D$64).

Then, we can use the OFFSET formula to tell it to not change the row reference, but change the column reference as we copy the formula down.
Not that the format of OFFSET is OFFSET(row offset, column offset)

In the data we are pulling from, we do not want to change the row (stay at 64), so we set that to 0 (no row offset from the original).

We want the column to increment by one for each row that we copy the formula down for.
The ROW() function simply returns the row number that the formula resides in (for example, if we put =ROW() in cell C5, it returns 5, the row number).
Since the first formula we are putting in cell C5, we do not want any column offset in that cell, so we subtract the row number (5) from that offset formula, i.e. ROW()-5
If you follow that logic down, when you copy that formula down to cell C6, the ROW()-5 portion of the formula returns 1 (6-5), offsetting our original starting point over one column (D64 becomes E64).
And so on as you copy the formula down more rows.

Make sense?

 

[nburaq]

Tghanks again works perfect!

 

[Joe4]

You are welcome.
Glad I was able to help!