General discussion


Link Excel Spreadsheet to another Excel Spreadsheet

By eveready1010 ·
I have an Excel Spreadsheet that I am using like a database, and another to format and sort the data in multiple different ways. To explain this easier, I will call the first spreadsheet with all of the information in it, spreadsheet #1, and the other spreadsheet I will call Spreadsheet #2. Spreadsheet #2 is linked to Spreadsheet #1 using >Data, >Get External Data.

The problem is that Spreadsheet #1 has one column with a long formula, but on spreadsheet #2, the date that is supposed to show up as a result of the formula, doesn't show up. It shows up on spreadsheet #1.

The formula will also show text if conditions are met in other columns on spreadsheet #1, and they show up just fine on spreadsheet #2, but not the dates. It is like only half of the formula is working throught the linked spreadsheet.

Is there a limit to size of the formula that can be used on spreadsheet #1, in order for the result to show up on spreadsheet #2?

(I hope this makes sense!)

Here is the connection string which is being used to link the two spreadsheets:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=C:\Documents and Settings\zzz\Desktop\storageDB.xls;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

and here is the formula that is coppied in the whole column "D":


Any help would be appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

you may be beyond me but I'll give it a go

by Neon Samurai In reply to Link Excel Spreadsheet to ...

First, why the ODBC code to link to the second XLS?

- Save both XLS to a generally accessible folder.

- use VLOOKUP( ) from XLS2 to pull data rows from XLS1

- OR use sum(if( )) (sum with nexted if) to consolidate rows of data from XLS1 into XLS2

- OR use index/select to have XLS2 select a specific column from a specific row within XLS1

If you have both files open (remember to open XLS1 before XLS2 in future) the cell formula will update very quickly and use only the file name in the formula. If XLS1 is left closed, you'll notice the formula show the long path to XLS1 and take forever to update since each indavidual cell opens XLS1, Updates, then closes XLS1.

I think the ODBC step is overkill but you may be using that in VBA code where I'm sticking to the cell formulas for the same function in my own files.

If we call XLS1 the source data table then you may want to insert columns on the far left. I use worksheet freezing to mark the top left of the data keeping it seporate from the left most "calculating" columns. The "calculating" columns usualy start with a unique identifier in colA (concatination of other column values from same data row). Other columns within the left area can make any calculations you need. I also use if statements to show 1 or 0 in a column identifying the "1" rows where I need to select only a group of data rows.

I'd need to know a bit more about the problem or see an example set of workbooks before I could offer read detail but hopefully that helps.

Related Discussions

Related Forums