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.

 

[kevin9999]

Try removing the "! " from the "!M13" to become just M13 and see if that works.

 

[mikerickson]

=INDEX(INDIRECT("[BOP.xlsx]" & $E$12 &"!M:M"), ROWS($1:13), 1)

 

[arniebun]

=INDEX(INDIRECT("[BOP.xlsx]" & $E$12 &"!M:M"), ROWS($1:13), 1)

Legend, thank you so much

 

[arniebun]

Try removing the "! " from the "!M13" to become just M13 and see if that works.

thanks unfortunately i couldn't get this to work but thanks for helping

 

[arniebun]

=INDEX(INDIRECT("[BOP.xlsx]" & $E$12 &"!M:M"), ROWS($1:13), 1)

I was woundering how i can reffrence the sheet without having it open?

 

[Fluff]

You cant (AFAIK) the other workbook needs to be open for indirect to work.

 

[arniebun]

You cant (AFAIK) the other workbook needs to be open for indirect to work.

you can do it but i would need to use something else

You cant (AFAIK) the other workbook needs to be open for indirect to work.

 

[Fluff]

You can't do it with Indirect.

 

[arniebun]

i am using =INDEX(INDIRECT("[BOP.xlsx]" & $E$12 &"!M:M"), ROWS($1:273), 1) I have BOP.xlsx open, when i open my workbook with this reference in i get #ref on all the cells. I haven't changed the name or done anything, it has just happened when i opened the worksheet. when i deleted the formula and rewrite it, i still get #ref i have tried changing setting in the trust centre from the forum but doesn't seem to work I have included some images of my trust centre settings, please help.