Link to external source workbook for data validation lookup values returns zeros

[scottbass]

Hi,

Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.

I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).

I've investigated the techniques in this link: http://office.microsoft.com/en-au/ex...010102338.aspx.

Questions:

1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?

Regards,
Scott

[Pete_UK]

To answer Q2, and partly Q1, though I haven't read the link:

=IF(source_cell="","",source_cell)

Obviously, source_cell should include the full path to the file, the filename, sheet name and cell reference.

Hope this helps.

Pete

[scottbass]

Hi Pete,

I'm creating the formula by:

* Open the source and destination workbooks (for testing the destination is just File --> New --> Book1)
* Create a named range in the destination workbook (Book1):
- Formula --> Name Manager --> New --> (give it a name) --> Refers To --> navigate to the source workbook --> click a header --> edit the generated value
- The final reference is =[Midas_Lawyers.xlsb]Lawyers!Table_Lawyers[#ALL], where workbook = <path>\Midas_Lawyers.xlsb, worksheet = Lawyers, table = Table_Lawyers (obviously the range in the source is formatted as a table)
- I'm not sure whether Table_Lawyers[#ALL] remains dynamic, or whether it's translated "under the covers" to the static value of the range at the point the named range is defined.
* The named range looks OK in the destination, i.e. there is no #REF problem.
* Close the source workbook. The named range in destination still looks OK, i.e. it can reference the data even when the source is closed.
* Select a range of cells in the destination workbook, in my case A1:N1000, which covers the range of the source.
* Type =T(able_Lawyers) in the formula bar. The "Table_Lawyers" autocompletes.
* Press Cntl-Shift-Enter for the array formula.

Given my approach above, can you further advise how to get rid of the zeros? And perhaps an explanation of why Excel puts them in the destination, since the source is a blank cell. Explicitly formatting the cells in the destination as Text does not help.

Regards,
Scott

[scottbass]

Oops...I stand corrected. When I close the source workbook, the named range in destination turns to #REF. Bummer, I could have sworn this worked.

Back to the drawing board. I need to have the external link work properly even when the source workbook is not open.

(I know there may be trickery where I can open the source workbook hidden, but I REALLY want this to work with the source workbook closed. I assume this is done all the time with say large budgeting workbooks and then a separate consolidated, summary workbook. I wouldn't think such a scenario would require opening up all the source workbooks for the summary workbook to work properly.)

[scottbass]

Hi All,

I've solved my own issue: http://office.microsoft.com/en-au/ex...103791160.aspx

I'm using a data connection to read in the data to the separate, closed workbook. I've set the properties to refresh data on workbook open. It appears to be performing ok and as desired.

Regards,
Scott

[scottbass]

However, I do have another minor issue. When the destination workbooks are open, this approach puts a lock on the source workbook.

Is there a way to update the source workbook while the destination workbooks are open? Perhaps a setting on the connection string? I tried marking the source workbook readonly, but that didn't work.