Consulting

Results 1 to 18 of 18

Thread: External Formula

  1. #1
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location

    External Formula

    Overview

    I have setup a formula to bring values in from an external workbook using the INDIRECT excel function.

    Formula: INDIRECT("'["&C1&"]"&C2&"'!"&C3)
    C1= Workbook Name
    C2= Sheet Name
    C3= Cell Reference

    The above formula is applied over 1,000 cells and linking in over 10 workbooks

    Issue
    • There is no issue with the formula; happy with the result
    • The issue is that for the values to be visibile in the target workbook(not "#ref"), the target workbook must be open.
    Is there anyway to store the values in the target workbook without having over 10 source workbooks open?

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    re:"for the values to be visibile in the target workbook(not "#ref"), the target workbook must be open"
    I hope this is a typo for "the source workbook must be open" or some such, otherwise the solution would be too easy!

    Anyway, there are at least 2 solutions that I can think of, but which one's better depends on how the data you want to see is distributed, both in the source and destination worbooks:

    If both the source and destination ranges are a few blocks of cells then a few querytables that can be refreshed at will is probably easier to maintain. Named ranges and whole sheets, some tables/lists, even autofiltered ranges in a closed workbook can be queried by another excel workbook.

    On the other hand, since direct references (not involving the INDIRECT function) in cells can return values from closed workbooks, we get a macro to write these formulae into the cells, using values from cells C2, C2, C3 etc. These formulae, can be dotted about the destination worksheet, as can the cells they refer to in the source workbook.

    So which is it?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location
    Hi,

    Thank you for the reply.

    You are correct, I should have written "source" instead of "target"

    I attach my workbook which demonstrates the structure of the issue and my current soultion which is the formula outlined in the original post.

    I like your idea of generating a direct formula using the VBA approach.

    As you will see, I am looking up at 7 source workbooks, obtaining the values and populating them in each of the "c.Ext"***"" sheets.

    In generating the VBA solution, each target worksheet needs to be considered.

    Apologies for the delayed reply.

    Kind Regards,
    dek

  4. #4
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location

    Workbook attached

    Apologies. Workbook attached.

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    C30 in sheet c.Ext(Labour) contains:
    =#REF!C21
    and B30 contains:
    =#REF!B21

    Much the same for most of the sheets through columns B and C.
    Are these broken references to cells within this workbook (you've deleted a sheet or two) or are they external to this workbook?

    ps. where are you in the world?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location
    Hi,

    I have corrected the issue you highlighted in the previous post regarding the #ref errors.

    Please find attached the revised workbook with the corrections.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    1. Are the files you're trying to see are in the same folder?

    To get data from closed workbooks it will need to know the path as well as the filename. Currently you only have filename info in the workbook. How we do this depends on whether the files you're trying to see are in the same folder or not.
    Hopefully they're all in the same folder and we can put the folder's path in a cell in the a.structure sheet. Otherwise, it's not too hard to look it up in a table too. There is a third way; put the path and filename together in the existing table, but there'll be some messing about parsing it to put square brackets in the right place.

    2. Which way do you want to go on this?

    FYI the sort of thing Excel expects to see in a cell when getting data from closed workbooks can be seen by having a simple formula like:
    ='[Open Workbook.xls]Sheet1'!$F$15
    when the workbook you're interrogating is open. Then when you close that workbook, the formula changes to something like:
    ='C:\Documents and Settings\dek\My Documents\[Closed Workbook.xls]Sheet1'!$F$15

    (I'd be interested to know whether the path starts with a drive letter, or two backslashes)

    ps. where are you in the world?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    The attached is to establish that the concept works at your end.

    There's a short macro called blah which writes formulae to cells E22:P23 on the c.Ext(Rev) sheet (shaded).
    Currently the formulae have not been changed.
    Open the attached and the source file and before you run anything, make sure you can see values in the shaded range (perhaps copy and paste-values as they stand to another sheet or workbook for comparison later).
    Now run blah.
    (If it comes up with the File Open dialogue box there's something wrong with filenames.)
    Revisit the shaded area. Is it giving the same results?

    If so, you could close the source workbook now and the formulae should change to include path info and they should still show values.
    Final test: save, close and reopen the attached (all with the source workbook firmly closed) - can you still see values in the shaded range?

    The code in blah needs to be streamlind and made more robust and be able to cater for multiple sheets and different sized ranges as well as handle paths so that the source workbook never needs to be opened.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location
    Hi Again,

    I am in Australia.

    In regards to your 1st post, all source workbooks will be in the same folder. I will review the file provided in a few hours as the machine I am currently on does not have office installed.

    Will be in touch soon.

    Where are you located?

  10. #10
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    An Aussie or a visitor in Australia?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location
    Aussie - was working in London

  12. #12
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location
    p45cal,

    Many thanks for the solution. It works a treat.

    Is it possible to include an error handler so that if a workbook does not exist for a BU, that it populates a "0" value instead of the trying to link to an external workbook.

    As you are aware, if the workbook does not exist then an open dialog box is initiated.

    The structure of each sheet will remain the same (across columns and rows).

  13. #13
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    re: "Many thanks for the solution"
    It wasn't meant to be a solution, just a check. I'd expected you were going to tell me which cell you wanted to put the path in in the spreadsheet then I'd add code to put the path info into each formula.

    Sure, an error handler can be put in to check for the existence of workbooks, but it too would need the path. We could check at the beginning for the presence of files and put something in each offending cell like 'no file'. Furthermore, we could check for the existence of the sheet in valid files and again put text like 'no sheet', which would signify that the file existed but not the sheet. The SUM function doesn't mind adding cells, some of which are text.
    I see a possible problem with putting zeroes in, you might think that the amount really was a zero that had been fetched from a workbook.

    I have a few more questions: are the numbers on row 18 of most of the sheets actually used anywhere?, because I would use them in the creation of the formulae rather than derive it from the actual column number in the sheet.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #14
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location
    It wasn't meant to be a solution, just a check. I'd expected you were going to tell me which cell you wanted to put the path in in the spreadsheet then I'd add code to put the path info into each formula.
    Any cell is fine for the path. However, the source workbooks will be in the same folder as the target.

    Sure, an error handler can be put in to check for the existence of workbooks, but it too would need the path. We could check at the beginning for the presence of files and put something in each offending cell like 'no file'.
    A text value will not suffice as these sheet are used as the base for further calculations in other sheets (which have since been developed)

    Furthermore, we could check for the existence of the sheet in valid files and again put text like 'no sheet', which would signify that the file existed but not the sheet. The SUM function doesn't mind adding cells, some of which are text.
    Same answer as provided above for "no file"

    I see a possible problem with putting zeroes in, you might think that the amount really was a zero that had been fetched from a workbook.
    Zero is required rather than text value as the sheet values are used in other sheets to conduct financial calcs otherwise it will generate an output error

    I have a few more questions: are the numbers on row 18 of most of the sheets actually used anywhere?, because I would use them in the creation of the formulae rather than derive it from the actual column number in the sheet.
    Yes, these values are used in other sheets where a vlookup is being conducted

  15. #15
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location
    Hi p45cal,

    I was wondering whether any further assistance will be provided as per your last thread, or if this has transitioned to my hands to complete?

  16. #16
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    It's been a bank holiday w/end in the UK. I'll look again this week.

    If the workbook with the formulae (destination) is going to be in the same folder as the source files then the formulae can include the path easily, with the advantage that the destination won't have to stay put, however, if the source file path starts with a local drive letter assigned for that machine, it may not be the same drive letter on other machines.. unless the path starts with '\\'?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  17. #17
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    in sheet a.stucture, range C8:C14, can the same workbook name appear more than once?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  18. #18
    VBAX Regular
    Joined
    Apr 2009
    Posts
    51
    Location
    answer = no

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •