Dynamically referencing a sheet

[arniebun]

So I want to reference a sheet in another work book, with only the sheet name in a cell. So I have this formula that works =INDIRECT("[BOP.xlsx]" & $E$12 &"!M13") what I want to do is copy this formula down and as I do the value of M13 will change to M14 and so on. When I am dragging the cell down nothing is happening. I have even simplified the formula to reference the same workbook but different sheet and still the same. If I take the $ away from E12 that will change but M13 won’t.

Could anyone help me please, I have tried searching online but can’t find anything.

 

[Fluff]

Trust centre settings have nothing to do with formulae.

 

[arniebun]

Trust centre settings have nothing to do with fo

Trust centre settings have nothing to do with formulae.

do you have any ideas how i can fix this issue i am having with the formula?

 

[Fluff]

Without any knowledge of you workbooks, sheet names & whats in E12, very difficult.

 

[arniebun]

=INDEX(INDIRECT("[BOP.xlsx]" & $C$16 &"!O:O"), ROWS($1:83), 1) With c16 being the name of the worksheet in BOP.xlsx to reference from all in the same folder. bop is open but getting #ref. I basically had to do a restart, i saved my work restarted my computer reopened everything as it was and got #ref, it happened before and i had to re write the whole sheet.

 

[Fluff]

Try

=INDEX(INDIRECT("'[BOP.xlsx]" & $C$16 &"'!O:O"), ROWS($1:83), 1)