External Formula

[dek]

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?

[p45cal]

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?

[dek]

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

[dek]

Apologies. Workbook attached.

[p45cal]

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?

[dek]

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.

[p45cal]

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]

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.

[dek]

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?

[Aussiebear]

An Aussie or a visitor in Australia?

[dek]

Aussie - was working in London

[dek]

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).

[p45cal]

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.

[dek]

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

[dek]

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?

[p45cal]

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]

in sheet a.stucture, range C8:C14, can the same workbook name appear more than once?

[dek]

answer = no