Non-Sequential Cell References

[Guest]

I hope I can explain this clearly.

Assume in COLUMN-A I have data from ROW-1 through ROW-500

In COLUMN-B, I want B1 to refer to A1; B2 to refer to A6, B3 to refer to A11
(every 5th one).

Is there an easy way to create COLUMN-B without me having to enter in 100
cells manually?

My initial thought was to create the first 3 or 4 and then select and drag
down, but that doesn't seem to work.

Thanks in advance!

 

[T. Valko]

Try this:

=OFFSET(A$1,(ROWS($1:1)-1)*5,)

Copy down as needed.

Biff

 

[Guest]

Dude, you ROCK!!!

I thought it would have to use OFFSET, but the couple things I tried weren't
working.

Thanks for the quick reply!!!

 

[Guest]

Hi,

I do like below:

in the cell C1 enter 1 and in the cell C2 enter 6 and active cells C1 & C2
copy drag down to where ever you need
in the cell D1 enter this formula: ="=A"&C1 and copy drag down this formula
to where ever you need
the cells you copied should be activated right click on the active cells and
choos copy and then right click on the cell B1 choos past especial > Value
and click ok
go to menu: Data> text to column and follw the instruction

hope it will work for you

Thanks

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Dude, you ROCK!!!

I thought it would have to use OFFSET, but the couple things I tried
weren't
working.

Thanks for the quick reply!!!