Forum Discussion

Anonymous's avatar
Anonymous
Sep 20, 2019

I did it, guess so. The External Referencing.

Hey guys,   Thanks for your replies. And eventually I made it work, in a very amateur & dumb manner.   So our problem is that we manage our accounts every day, here we have today's book and the ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 21, 2019

Deleted 

Yes, INDIRECT doesn't work with closed files. The workaround could be, if without VBA, formula like

 

=
IF(<current sheet>="Sheet1", [Book2.xlsx]Sheet1!A1,
IF(<current sheet>="Sheet2", [Book2.xlsx]Sheet2!A1,
IF(<current sheet>="Sheet3", [Book2.xlsx]Sheet3!A1,
IF(<current sheet>="Sheet4", [Book2.xlsx]Sheet4!A1,
IF(<current sheet>="Sheet5", [Book2.xlsx]Sheet5!A1
)))))

 

where <current sheet> you may pick-up from CELL() formula. Even better is to keep helper cells for each sheet with current sheet name and use reference on it. Not very elegant but it could work if you don't have a hundreds of worksheets and don't add/remove sheets, at least frequently.

  • Anonymous's avatar
    Anonymous
    Sep 21, 2019

    SergeiBaklan 

    Hi Sergei thanks for your reply,

     

    Your suggestion is awesome, and yeah the truth is that we do change our tags very.... often, that's why I want to embed a formula to help tag names update themselves automatically.

     

    To your knowledge, is that possible to apply this formula (MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))

    in yours, so we don't have to update worksheet names manually.

     

    Regards.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 21, 2019

      Deleted 

      Hi,

      I guess this formula returns current sheet name. Yes, sure, you may use it instead of <current sheet>. But again, it's much better not to complicate the formula but use helper cell. Take it in the place where end-user can't see, in addition you may hide column/row with it, add your formula into this cell and in formula with links use reference on this helper cell.

      • Anonymous's avatar
        Anonymous
        Sep 22, 2019

        SergeiBaklan

        Hi Sergei,

         

        I've tried your formula it works well.

        But yeah it is a bit bulky, haha, sorry to say that, I mean, because we have too many tags here & the number is still growing so it would be a pain to use that.

        IF(<current sheet>="Sheet5", [Book2.xlsx]Sheet5!A1
)

         

        I believe if I can replace that "Sheet5" following [Book2.xlsx] with cell(), i.e. [book2]Cell() in which cell() to pick up the tag name, then it will help me get what I want in book2 under same tag, and there's no need to change the formula manually.

        But in fact it is an external link, so I'm not sure if we make it like that.

        So far I've tried to put cell() and [book2] together over and over and over - a big fail.

         

        What do you think? Is that because that I use connectors in a wrong way, like misplaced & ' " so excel can't read it, if so can you please give me some ideas. Or we just cannot embed cell() in an external link?

         

        Thanks for your time. 

         

         

Resources