Repeat a formula with interval

[BarbnSLC]

I found a similar post but I have had no success with the formula that was
given to the poster. Please help.

I need Excel to repeat a formula with an interval of 4. My goal is to link
two spreadsheets within the same workbook and get the following:

='DATA LINK'!B1
='DATA LINK'!B5
='DATA LINK'!B9
='DATA LINK'!B13
='DATA LINK'!B17

For 9999 rows.

If I use the chain function, Excel doesn't fill in the appropriate interval.

How do I do this?

 

[T. Valko]

One way:

Assume you enter the formula in cell A1.

=INDEX('Data Link'!B:B,(ROWS(A$1:A1)-1)*4+1)

Copy down as needed.

 

[Ken Johnson]

I found a similar post but I have had no success with the formula that was
given to the poster. Please help.

I need Excel to repeat a formula with an interval of 4. My goal is to link
two spreadsheets within the same workbook and get the following:

='DATA LINK'!B1
='DATA LINK'!B5
='DATA LINK'!B9
='DATA LINK'!B13
='DATA LINK'!B17

For 9999 rows.

If I use the chain function, Excel doesn't fill in the appropriate interval.

How do I do this?

=INDIRECT("'DATA LINK'!B" & 4*(ROW(B1)-1)+1)

Ken Johnson

 

[BarbnSLC]

Ken: Thanks. However, I keep getting #REF! with the formula you provided
and it doesn't ask for the data linked file. If you have any helpful info, I
would appreciate it. The formula using the INDEX command seems to work
(posted in a previous reply), but it doesn't update automatically with new
data.

What is the difference between the INDEX and the INDIRECT command? Will the
INDIRECT command update automatically?

Thanks
BarbnSLC

 

[T. Valko]

...INDEX...doesn't update automatically with new data.

Do you have calculation set to automatic?

Tools>Options>Calculation tab>Automatic

Both formulas do the same thing and both do work correctly.

The difference between the two versions is that INDIRECT is a volatile
function meaning it recalculates *every time* a calculation is triggered by
some event. This constant recalculation is *usually* not desireable. The
INDEX version will only recalculate when a dependent reference changes. This
method is more desireable!

If you're getting a #REF! error with the INDIRECT version then the resulting
string is not a valid reference. Or,

it doesn't ask for the data linked file.

If you're trying to link to another file then INDIRECT won't work *unless*
the linked file is open.

 

[Max]

Ken's suggestion works fine for me.
Did you try copying it direct from his post,
then paste into the formula bar, and copy down?

As for:

.. it doesn't update automatically with new data.

Both INDEX and INDIRECT options should auto-update
Check that the calc mode is set to automatic
(Click Tools > Options > Calculation tab > Automatic > OK)