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”:
=IF(D3=”S”,(IF(F3=”S”,(H3+(3*365)),(MAX((H3+(3*365)),(I3+(3*365)))))),(IF(D3=”W”,(MAX((H3+(3*365)),(I3+(3*365)),(K3))),(IF(D3=”H”,(MAX((H3+(3*365)),(I3+(3*365)),(K3),(L3+(2*365)))),(IF(D3=”D”,(“BYE”),(“OOPS”))))))))
Any help would be appreciated.