INDIRECT function - need help

Originally Posted by dansparts

@bebo

Using that formula, copied all the way down Column C, give me the value from A4 into C3, but then the value from A5 into C4, value from A6 into C5, etc.

I need value from A18 in C4, not value from A5.

Opp, sorry for my mistake. I forgot to time it to 14:

=OFFSET($A$4,MOD(ROW(1:1)-1,14)*14,)

Or simple like this:
=OFFSET($A$4,(ROW(1:1)-1)*14,)

@bebo

That worked.

I need to understand how/why this works, since I will need to be adapting this formula to several other worksheets, with different amounts of rows & columns of the pasted text.

What is the difference of the MOD in the formula or not?

I see that the (1:1) changes as it is pasted down the column to (2:2), (3:3), etc. And the -1) part?

I assume the *14 will need to be changed to whatever spacing of rows apart the data repeats on.

Why use this OFFSET function, instead of INDIRECT?

Also, is not OFFSET for a static range? The text data I will be pasting in will vary in length almost always. In other words, sometimes I paste information from 3 records, sometimes from 25 records, sometime from 150 records. Will that affect things?

Sorry for questions that might seem elementary, but while I use Excel all the time, I'm not at all well-versed in complicated formulas like this. Most of my "expertise" (cough, cough) is simple things like =SUM.