Same colum different row?
 

How do i copy the same colum but one row down's values accross the workbook
repeatedly instead of keep changing the row number?
e.g. i want $E1, then $E2 etc accross the page.

Any help would be hugely appreciated =)

Same colum different row?
 

If I understand you correctly...
ASSUME:
The data you want to reference is in the range E2:E6.
The formula you want to create will be located in G2.
The range of formulas you want to copy to is G2:K2 where...
G2 will reference E2
H2 will reference E3
I2 will reference E4
J2 will reference E5
K2 will reference E6

In G2, enter...
=INDIRECT("E"&COLUMNS($G2:G2)+1)

Copying across to K2 will create the following formula in K2...
=INDIRECT("E"&COLUMNS($G2:K2)+1)

Now to explain the formula:
The INDIRECT worksheet function returns the reference specified by a text
string. References are immediately evaluated to display their contents. Use
INDIRECT when you want to change the reference to a cell within a formula
without changing the formula itself.

Next we build the reference for 'Indirect'.
The 'E' is the column you want to look at and is hard-coded into the formula.

The COLUMNS worksheet function returns the number of columns in an array or
reference.
So COLUMNS($G2:K2) returns 5 because there are 5 columns in the reference of
G/H/I/J/K.

I added the +1 because, in this example, we are starting in row 2.
COLUMNS($G2:G2) returns 1 but we want E2 so 1+1 = row 2
COLUMNS($G2:K2) returns 5 but we want E6 so 5+1 = row 6

Put the whole thing together and ...
=INDIRECT("E"&COLUMNS($G2:G2)+1) returns the value in E2
=INDIRECT("E"&COLUMNS($G2:K2)+1) returns the value in E6


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Bonbon" wrote:

How do i copy the same colum but one row down's values accross the workbook
repeatedly instead of keep changing the row number?
e.g. i want $E1, then $E2 etc accross the page.

Any help would be hugely appreciated =)

Same colum different row?
 

Gary L Brown wrote...
....
G2 will reference E2
H2 will reference E3
[etc.]

In G2, enter...
=INDIRECT("E"&COLUMNS($G2:G2)+1)
....

A nonvolatile alternative would be

G2:
=INDEX($E$2:$E$6,COLUMNS($G2:G2))

G2 filled right into H2:K2.

Same colum different row?
 

Bonbon,
I think Harlan's solution is better than mine.
Sincerely,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Bonbon" wrote:

How do i copy the same colum but one row down's values accross the workbook
repeatedly instead of keep changing the row number?
e.g. i want $E1, then $E2 etc accross the page.

Any help would be hugely appreciated =)