How to fill series in a pattern ?

[shyamsundar]

Column A contains a list of usernames, emails, account numbers, etc. I want to extract only the username and the respective email and put them is separate columns (C & D). So I entered the following formulas manually in C & D for the first 4 rows.

I'm trying to autofill the cell addresses in the same pattern till the end of the list. Please tell me how to do it. For example, cell C6 should be equal to A22; C7=A27; C8=A32 and so on. Dragging and dropping generates incorrect values. Thanks in advance

 

[shyamsundar]

Hi, I'm new here. The edit button on my first post just disappeared! Does anyone know why?

 

[taurean]

Welcome to MrExcel.

"Edit" option remains available for first 10 minutes or so. While in edit mode, if you cross the time limit then you get another message for getting it approved from admins.

In cell C2:
=OFFSET($A$2,5*(ROW()-2),0)
In cell D2:
=OFFSET($A$3,5*(ROW()-2),0)
And copy it down until you get blanks.

 

[shyamsundar]

In cell C2:
=OFFSET($A$2,5*(ROW()-2),0)
In cell D2:
=OFFSET($A$3,5*(ROW()-2),0)
And copy it down until you get blanks.

Awesome! It works perfect. Thank you very much taurean.

 

[Rick Rothstein]

Welcome to MrExcel.

"Edit" option remains available for first 10 minutes or so. While in edit mode, if you cross the time limit then you get another message for getting it approved from admins.

In cell C2:
=OFFSET($A$2,5*(ROW()-2),0)
In cell D2:
=OFFSET($A$3,5*(ROW()-2),0)
And copy it down until you get blanks.

The OFFSET function is a Volatile function and, as such, might be better avoided if possible. Here is a non-Volatile formula that should do the same thing...

C2: =INDEX(A:A,2+5*(ROW()-2),1)

D2: =INDEX(A:A,3+5*(ROW()-2),1)

Copy these down as needed.

 

[shyamsundar]

The OFFSET function is a Volatile function and, as such, might be better avoided if possible. Here is a non-Volatile formula that should do the same thing...

C2: =INDEX(A:A,2+5*(ROW()-2),1)

D2: =INDEX(A:A,3+5*(ROW()-2),1)

Thanks. This works too.