Offset formula linked to another workbook will not recalculate automatically

[ozbeachbum]

Hi all,
A follow on from my last post although I got the formula;

=OFFSET('[02 REG CHR securities.xlsx]Securities Info'!$BX$75,(ROW('[02 REG CHR securities.xlsx]Securities Info'!BX1)-1)*6,0)

to work, it does not recalculate unless the linked workbook '[02 REG CHR securities.xlsx] is open.
I have done some research and apparently this is the case when using OFFSET, therefore I tried INDEX as suggested in the article;

=INDEX('[02 REG CHR securities.xlsx]Securities Info'!$BX$75,(ROW('[02 REG CHR securities.xlsx]Securities Info'!BX1)-1)*6,0)

I though this solved the issue as it worked, but only in the first cell, when I copied it down, I get an invalid reference or error.
It seams obvious that it is how it calculates the rows after the first one, I tried some adjustments but to no avail.
Any thoughts would be greatly appreciated.
Dave.

 

[Legacy 143009]

I think INDEX needs a range to pick rows from. Try define a range like:

=INDEX('[02 REG CHR securities.xlsx]Securities Info'!$BX$75:$BX$135,(ROWS($A$1:$A1)-1)*6,0)

 

[Legacy 143009]

Also, right side needs +1 because INDEX function won't work with 0 index.

=INDEX('[02 REG CHR securities.xlsx]Securities Info'!$BX$75:$BX$135,((ROWS($A$1:$A1)-1)*6)+1)

 

[Fluff]

Duplicate to: Bring info in every 6th from another workbook

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.