Copy Link Cells

S

Stephen

Hi

I want to link cells from a horizontal line to vertical column.
Problem is that when I copy down the first link it copies the wron
cell, because it should link the horizontal cell in stead of th
vertical one.
Is there a way to do this?

Stephe
 
F

Frank Kabel

Hi Stephen
Assumptions:
- sheet 1, row 1 stores your source values
- the data should be linked on sheet 2, column A

Enter the following in A1 on your second sheet
=OFFSET('sheet1'!$A$1,0,ROW()-1)
and copy this down
 
S

Stephen

Hi Frank,

The formula works great!! Thanks a lot.

I did make a small change. I used the COUNTA fromula instead of th
ROW (same principal I guess).

Stephen
 
F

Frank Kabel

Hi Stephen
COUNTA is something completly different form ROW. ROW return the
current row number of the cell it resides in, COUNTA counts all non
blank rows. So I'm just wondering it works for you with COUNTA?
 
S

Stephen

Hi Frank,

Yes, it does work well. I changed it because I had to refer to row 40
and the formula starts in row 52 from another sheet. So I couldn't get
the ROW right. So that's why (just to make it easier for myself) I
resorted to the COUNTA formula.

If you do this with COUNTA($A$1:A1) and then copy down (non blank cells
ofcourse) then it just keeps adding up.

Thanks again for your help.

Stephen

Frank said:
*Hi Stephen
COUNTA is something completly different form ROW. ROW return the
current row number of the cell it resides in, COUNTA counts all non
blank rows. So I'm just wondering it works for you with COUNTA?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top