Repeat a formula with interval

  • Thread starter Thread starter BarbnSLC
  • Start date Start date
B

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?
 
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.
 
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?
Click to expand...

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

Ken Johnson
 
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
 
...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.
Click to expand...

If you're trying to link to another file then INDIRECT won't work *unless*
the linked file is open.
 
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.
Click to expand...
Both INDEX and INDIRECT options should auto-update
Check that the calc mode is set to automatic
(Click Tools > Options > Calculation tab > Automatic > OK)

---
 
Back
Top