Bring info in every 6th from another workbook

[ozbeachbum]

Hi All,
I am endeavouring to bring in the value from the cell in every 6th row in another workbook, I have tried the formula below without any success.

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

Any assistance would be greatly appreciated.
Cheers,
Dave.

 

[hrayani]

Try this

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

If, I am not wrong here, the problem I see is you are referring to cell BX1 which gives 1
then you are subtracting 1 from the answer i.e. 1-1=0
then you are multiplying it by 6
so anything multiplied by 0 will give you error

 

[Legacy 143009]

Can you

so anything multiplied by 0 will give you error

No, anything divided by 0 will give an error. 0 times is anything =0 always.

@ozbeachbum can you please writedown the address of cell you have the formula?

 

[hrayani]

Can you

No, anything divided by 0 will give an error. 0 times is anything =0 always.

@ozbeachbum can you please writedown the address of cell you have the formula?

Thanks, I stand corrected
it will give 0 & no error. thanks again

but, still it won't work I reckon coz the result will always be zero
1-1*6=0

Or is there anything, I am not getting

 

[Legacy 143009]

Thanks, I stand corrected
it will give 0 & no error. thanks again

but, still it won't work I reckon coz the result will always be zero
1-1*6=0

Or is there anything, I am not getting

You can still write OFFSET(cell_referance, 0 0). It will return the same cell reference since there are 0 offsets in rows and columns.

 

[hrayani]

You can still write OFFSET(cell_referance, 0 0). It will return the same cell reference since there are 0 offsets in rows and columns.

Yes, exactly
but it won't offset anything

 

[Legacy 143009]

Yes, exactly
but it won't offset anything

Maybe he wants the first value from BX75. Then +6, then +6...

 

[hrayani]

Maybe he wants the first value from BX75. Then +6, then +6...

hmmm... let's see

 

[ozbeachbum]

Hi,
Thanks for the feed back.
I don't know why but my original formula;
=OFFSET('[02 REG CHR securities.xlsx]Securities Info'!$BX$75,(ROW('[02 REG CHR securities.xlsx]Securities Info'!BX1)-1)*6,0)
works now, not sure what was going on.

 

[Legacy 143009]

Maybe you need to keep 02 REG CHR securities.xlsx file open. Or automatic calculation was turned of. I don't have enough knowadge about this but OFFSET is a volatile function. Which means it recalculates each time you open a file. MAy be the formula stop working if auto-calculation was turned off or file was not opened.