View original (French)

Unable to update links if sources are not opened!

Pitiv -  
 Pitiv -
Hello,

My file contains links to several sources. When I open my file and validate the updates for the links, I get #VALUE! everywhere.
In the "Edit Links" section, each source is labeled as "unknown"... however... the links are correct...
I have to open my source files one by one to retrieve the data...
How can I access my data without having to open all the sources? (without using VBA) ?

Thank you.

8 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    it all depends on the functions used.
    Most do not work with the workbook closed, that’s normal.
    eric

    --
    By continually trying, we eventually succeed.
    So the more it fails, the more chances we have that it will work. (the Shadoks)
    In addition to thank you (yes, yes, it happens!!!), remember to mark as resolved. Thank you.
    0
  2. Pitiv
     
    Hello Eric,
    It's essentially about COUNTIFS and SUMIFS...
    0
  3. Pitiv
     
    Do these functions belong to those that do not work if the workbook is closed?
    If so, is there an alternative to these functions?

    Thank you in advance ;)
    0
  4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    matrixes on top of that, nothing more surprising than that.
    Although the messages seem a bit erroneous you have no choice.
    Either open your binders, or have all your data in the same binder.
    eric

    --
    By continuously trying, we eventually succeed.
    So the more it fails, the greater the chances that it works. (the Shadoks)
    In addition to the thank you (yes, yes, it happens!!!), remember to mark it as resolved. Thank you
    0
  6. Pitiv
     
    Hello Eric,

    Thank you for your reply.
    Yes, those are indeed the functions that do not work if the source workbooks are closed.
    As for the alternative... I don't see much, which is why I'm here on the forum ;)
    I hear your two solutions, which I had thought of, but I was actually hoping to avoid them:
    - the first forces me to open 6 workbooks to feed my main workbook
    - the second would require me to have a single workbook with 90 tabs... a bit overwhelming ;)

    Is there not a solution 3?
    Pitiv
    0
  7. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    without going through VBA, you could look into Power Query.
    Included in recent versions, or as an add-in to add to older ones (>=2010).
    But not with me, I don't know enough yet.
    eric

    --
    By trying continuously, we ultimately succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to the thank you (yes, yes, it happens!!!), remember to mark it as resolved. Thank you.
    0
  8. Pitiv
     
    :))
    I don’t want to use VBA, as it may crash for the users...
    Power Query training scheduled for 2021 ;)
    In the meantime, users will open all the workbooks ;)
    Thanks for trying!
    Pitiv
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      It's hard to know what's possible without knowing your workbooks or your formulas.
      If in each closed workbook you can create a summary sheet of the elements that interest you, you have VLOOKUP() to retrieve them, which works on closed workbooks.
      eric
      0
  9. Pitiv
     
    Don't bother! I think I'm looking for a solution that functions cannot offer, unfortunately.
    Every usable solution brings other drawbacks... I'm going to bet on Power Query!
    0