Solved

Importing a multiple text files with formulas into Excel tabs/worksheets

Posted on 2013-01-31
11
331 Views
Last Modified: 2013-02-19
Greetings.

I'm trying to import 3 tab-delimited text files into 3 tabs (worksheets) in an Excel 2010 workbook, where the text files actually contain valid Excel formulas that perform lookups between the tabs.  My ultimate goal is to compare 3 lists of email addresses and identify the ones on each tab that do not exist on the other tabs.  Since Excel imports files into new workbooks, this involves opening the files in 3 separate workbooks and then moving the worksheets into a single workbook OR executing a copy/paste into tabs in a single workbook.

Either way, the formulas simply don't calculate, even if you force recalculation of the entire sheet.  The formula is there and correct, but the cell is populated with a default value from my formula somehow (detail below).  But if I put my cursor in one of the cells and hit Enter, it calculates the result correctly!

Here's a simple case that illustrates the problem.  Assume that I have imported text files called Text1.txt and Text2.txt that look like this:

Text1.txt:
EmailAddress                    OnText2
person1@example.com   =IF(ISERROR(MATCH(A2,Text2!A:A,0)), "NO", "")
person2@example.com   =IF(ISERROR(MATCH(A3,Text2!A:A,0)), "NO", "")
person3@example.com   =IF(ISERROR(MATCH(A4,Text2!A:A,0)), "NO", "")
person4@example.com   =IF(ISERROR(MATCH(A5,Text2!A:A,0)), "NO", "")

Text2.txt:
EmailAddress                       OnText1
person100@example.com   =IF(ISERROR(MATCH(A2,Text1!A:A,0)), "NO", "")
person2@example.com       =IF(ISERROR(MATCH(A3,Text1!A:A,0)), "NO", "")
person101@example.com   =IF(ISERROR(MATCH(A4,Text1!A:A,0)), "NO", "")
person4@example.com       =IF(ISERROR(MATCH(A5,Text1!A:A,0)), "NO", "")

What I find is that both of the second columns 'OnText2' and 'OnText1' are populated entirely with the value "NO", when clearly some of them should be "", until I click on a cell and hit Enter.  How can I make them update normally?

In reality, I'm doing all of this from a Powershell script and it works great except that I can't get Excel to calculate the cell values when I'm done.  But this problem is reproducible through manual import operations as well.  Is there a better method of populating the formulas that I should be considering?
0
Comment
Question by:swertz
  • 6
  • 4
11 Comments
 
LVL 4

Expert Comment

by:Michal_Zyzak
ID: 38841775
In reference to one of your statements:
"What I find is that both of the second columns 'OnText2' and 'OnText1' are populated entirely with the value "NO", when clearly some of them should be "", until I click on a cell and hit Enter"

So it seems that Excel needs to evaluate your formulas more than once.
It may be a case where Excel imports data cell after cell and evaluates the formulas when the data they are referencing to is not yet there.
After all those text files are getting imported one by one and not in parallel.

Try following:
Disable Auto-calculate function via Options -> Formulas
After data import manually force a calculation (F9 button)
0
 
LVL 2

Author Comment

by:swertz
ID: 38841990
Setting calculation to manual didn't change anything.  But I think you're on the right track, based on what I see using the "Evaluate Formula" feature.  As I step through the formula calculations, I can see that the MATCH operation MATCH(A2,Text2!A:A,0) is returning #N/A when it should be returning a value.  If it can't find a value that is in fact there, I assume it's because it can't locate the tab.

So how can I make this work?
0
 
LVL 16

Expert Comment

by:terencino
ID: 38850010
Try deleting the header row (with EmailAddress & OnText in it). You will need to adjust your cell references to account for the change. This fixes the calculation issue. For some reason the data in the rows following the first row inherit some residual data type from the first row that the import enforces, even though it seems to calculate initially. Access does something similar. Annoying.

Another option for your import strategy is to generate the text files only with your PowerShell script, then in Excel, use the Data > Import Text function to link to the text files directly (one for each sheet) , so when the text files are replaced, just hit Refresh All and you are done. This simplifies the Excel side of things anyway.

Hope that helps
...Terry
0
 
LVL 2

Author Comment

by:swertz
ID: 38851047
Well that sounded promising, but without the header row it still doesn't work.  I'll try the Import Text method today.
0
 
LVL 2

Author Comment

by:swertz
ID: 38851805
Interestingly enough, the Import Text method suffers from the same problem: imported formulas containing references to sheets that do not yet exist will never calculate until you edit them (hit F2-Enter or cursor into the cell and Enter).  It's definitely a problem with cross-worksheet formulas only, and I'm at a loss to figure out how to fix it.

For now I'm working around it by importing the formulas with an apostrophe in front of them so they're "commented out" and then once all of the worksheets exist my PowerShell script runs through and removes the apostrophe on the first row and copy/pastes it downward.

I'd still love to know how to make it work correctly though.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 16

Expert Comment

by:terencino
ID: 38852597
Interesting, especially since removing the header row makes it work for me! I wonder can you provide a sample of your files. Also what version of Excel are you using?
0
 
LVL 2

Author Comment

by:swertz
ID: 38853465
That information is in my original post.  I'm using Excel 2010 and the text files are as above.  Please let me know if you get different results, as that would be interesting as well!
0
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38853492
I named the sheets as Text1 and Text2 before importing the files, so both sheets existed before any data was entered. That's why they worked. With the headers just as you had them
0
 
LVL 2

Author Comment

by:swertz
ID: 38853510
Aha....I hadn't thought of naming the sheets before the import.  So you're using Import Text then?  I'll try that too.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38853584
Yes I prefer to use Import Text, I can just change the text file and it updates without trouble and I don't have to rebuild the Excel files for which the data is only a small part usually.
0
 
LVL 2

Author Comment

by:swertz
ID: 38907935
Terencino's suggestion appears correct: the tabs named in the formulas must exist at the time that the files are imported or the calculations will never work.  However, with a large number of rows it was too slow to start with those tabs in place with no data, since the imported formulas generated errors until the data was later imported on the referenced tabs.  

Ultimately my solution was to import the text files with formulas that were de-activated, or rather preceded with a single quote so that they were not calculated, and then after all of the tabs were created my PowerShell script ran through and removed all of the quotes.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now