Repeat a formula with interval
 

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?

Repeat a formula with interval
 

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.

--
Biff
Microsoft Excel MVP


"BarbnSLC" wrote in message
...
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?

Repeat a formula with interval
 

On Jan 1, 9:30 am, BarbnSLC
wrote:
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

Repeat a formula with interval
 

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

"Ken Johnson" wrote:

On Jan 1, 9:30 am, BarbnSLC
wrote:
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

Repeat a formula with interval
 

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

Do you have calculation set to automatic?

ToolsOptionsCalculation tabAutomatic

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.

--
Biff
Microsoft Excel MVP


"BarbnSLC" wrote in message
...
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

"Ken Johnson" wrote:

On Jan 1, 9:30 am, BarbnSLC
wrote:
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

Repeat a formula with interval
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BarbnSLC" wrote:
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