reference every fourth cell

[mgirvin]

In cell A100 I would like to create a formula that I could copy downward to the range A100:A104 that would return the cell contents from A1, A4, A7 and A10. I have created a few large formulas that involve OFFSET, INDIRECT, ADDRESS and references to other cells that count intervals. But they are inelegant. Can anyone think of an elegant solution?

Examples of inelegance:

=INDIRECT(ADDRESS(ROW()-(ROW()-B111),COLUMN())), where B111 has a number to subtract and this one takes cell content from every fifth cell

or

=OFFSET(E11,-(COUNTBLANK($E$1:$E$10)+COUNTA($E$1:$E$10)+1-(COUNTBLANK($F$11:F11)+COUNTA($F$11:F11))),), this one takes every other cell.

[Aladin Akyurek]

In A100 enter & copy down:

=INDEX($A$1:$A$10,3*(ROWS($A$100:A100)-1)+1)

[mgirvin]

Wow, Aladin!

That is great. Thank you for the genie-like elegance!

[RalphA]

Here is my solution for data in A1, A4, A7 and A10 to appear in cells A101, A102, A103 and A104.

In cell A101, enter =INDIRECT("A"&1+3*MOD(ROW(),101)) and copy down.

Sorry, Aladin. I started when there were no responses, and didn't check to see if one had been posted in the meantime. Anyway, I think my solution is valid...

[mgirvin]

Wow, RalphA!

Now I have two great solutions!

As this is my first post, I am amazed at the speed, efficiency and elegance of the responses.

Thanks so much! I am learning from both solutions.

[Aladin Akyurek]

Here is my solution for data in A1, A4, A7 and A10 to appear in cells A101, A102, A103 and A104.

In cell A101, enter =INDIRECT("A"&1+3*MOD(ROW(),101)) and copy down.

Sorry, Aladin. I started when there were no responses, and didn't check to see if one had been posted in the meantime. Anyway, I think my solution is valid...

It has some drawbacks though:

1) INDIRECT is volatile.
2) INDIRECT locks the operation to column A. That is, you have to edit the formula if the user inserts a column before the current column A.
3) The formula is not robust: Inserting rows before the row of the first formula cell would yield incorrect results.
4) Constants like 1 and 101 make the formula too specific.

[RalphA]

Aladin:

Hey, I'm no genius! All I did was to create a formula that would do what was required. I didn't say it was a universal solution, or non-volatile, or what ever. Give me a break!

But, if "A" is

too specific

, one could change the "A" to 'char(64+column())'

[Aladin Akyurek]

Aladin:

Hey, I'm no genius! All I did was to create a formula that would do what was required. I didn't say it was a universal solution, or non-volatile, or what ever. Give me a break!

It is intended as a constructive comment...

But, if "A" is

too specific

, one could change the "A" to 'char(64+column())'[/quote]

Constants like 1 and 101, make the formula too specific, not just "A". Did you test the CHAR(64+COLUMN()) expression?

[RalphA]

Aladin:

Thanks for the "constructive comment". I had wrongly interpreted it as a critique.

As for having tested the CHAR(64+COLUMN()), yes I certainly did. I copied the formula to A99, B99, C99 and D99, and it shows up as A, B, C and D, in the respective cells. Didn't you test it?

[Aladin Akyurek]

Aladin:

Thanks for the "constructive comment". I had wrongly interpreted it as a critique.

OK.

As for having tested the CHAR(64+COLUMN()), yes I certainly did. I copied the formula to A99, B99, C99 and D99, and it shows up as A, B, C and D, in the respective cells. Didn't you test it?

I did: In AG99, I get the lower case a.