asked on

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

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?

Michal Zyzak

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)

swertz

ASKER

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?

terencino

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

swertz

ASKER

Well that sounded promising, but without the header row it still doesn't work. I'll try the Import Text method today.

swertz

ASKER

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.

terencino

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?

swertz

ASKER

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!

ASKER CERTIFIED SOLUTION

terencino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

swertz

ASKER

Aha....I hadn't thought of naming the sheets before the import. So you're using Import Text then? I'll try that too.

terencino

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.

swertz

ASKER

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.