Cell Location Series

[Henry1807]

Hey All,

Anyone know how to have cell locations increase (or decrease) automatically, in increments other than 1, in a series when you drag across?

For example: G10, G14, G18, G22, etc.

This works fine when you are increasing or decreasing in increments of 1 (G10, G11, G12, etc), but I can't quite get it to increase and decrease in other increments. It should be really easy, but I'm missing that lightbulb in the mind moment.

Thanks.

Henry

 

[PaddyD]

what are the increments?

paddy

 

[zacemmel]

Use the indirect function

 

[zacemmel]

=OFFSET($A$1,(COLUMN()-1)*2,0)

Or offset. Using that will increment it by 2's. This is assuming you are starting in column A.

 

[Henry1807]

PaddyD,

I'm trying to create a table about 20 columns across, referencing data next to the table, with each cell in the table referencing data 9 rows below the cell above it.

So the cells would be:

G10, H10, I10, J10, ...
G19, H19, I19, J19, ...
G28, H28, I28, J28, ...

Etc, for a few hundred rows.

Thanks.

Henry

 

[PaddyD]

in general, you should be able to do this with a version of offset() (as zacemmel posted).

to provide a pecise answer, we'll need to know:

1) the range of cells where you're source data is

2) the range of cells where you want the results.

paddy

 

[Henry1807]

zacemmel,

Thanks much for your help. Your idea worked great!

Henry