...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