Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Prodcut issue from .txt import
I've reviewed the many SUMPRODUCT threads here, but none of the
solutions seem to be working. I have been using the same Sumproduct formula and downloading new data from dvent Axys into excel using a general import. However, to make a long story short, we cannot use the excel export anymore. Now we have to save the report as a .txt file and import it into excel using the import wizard. I've been importing using fixed width. Once the data is pulled into excel, the sumproducts become #VALUE!. Here is the formula =SUM(('AXYS DATA'!$M$11:$M$65000="Research")*('AXYS DATA'!$K$11:$K $65000)*('AXYS DATA'!$I$11:$I$65000=SUMMARY!$B19)) (('AXYS DATA'!$M$11:$M$65000="Research") searches for the word "Research" in column M. However, after the .txt file is imported a routine is performed via macro to make the word show exactly as we want. First, the following formula is put in an adjacent column: =IF(I11="","",IF(M11="research","Research",IF(M11= "Best Execution","Best Execution",IF(M11="Directed","Directed","Other"))) ) Which returns one of the four words we're looking for. The if statement is then copied and the values pasted in column M. Therefore, the pasted values of this should return, unless I'm missing something. The ('AXYS DATA'!$K$11:$K$65000) is the variable row, as other columns are substituted to return the different sums. However, when I make this formula stand alone it does return a total. Therefore, I don't think this is the problem. (('AXYS DATA'!$I$11:$I$65000=SUMMARY!$B19)) "B19" is just filled with a broker code, that changes each row. Column B beside the sumproduct is pasted in from a filter of column I in the original workbook, so I know these cells are the same. If I manually export to excel, this workbook still works correctly. However, I will need to automate an export to .txt and use the import wizard. If anyone has any clue why my functions aren't working, please let me know. Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Prodcut issue from .txt import
Once the data is pulled into excel, the sumproducts become #VALUE!.
So, let's look at your formula: ('AXYS DATA'!$M$11:$M$65000="Research") ('AXYS DATA'!$I$11:$I$65000=SUMMARY!$B19) Both of those arrays will return either TRUE or FALSE so the problem is not being caused by either of those arrays. (array1)*('AXYS DATA'!$K$11:$K$65000)*(array3) If ('AXYS DATA'!$K$11:$K$65000) is not a number or an empty cell then the result will be #VALUE!. You may have leading/trailing spaces like this: _10_ _10 10_ Whe (array1)*(_10_)*(array3) = #VALUE! -- Biff Microsoft Excel MVP "Chicago Joe" wrote in message ... I've reviewed the many SUMPRODUCT threads here, but none of the solutions seem to be working. I have been using the same Sumproduct formula and downloading new data from dvent Axys into excel using a general import. However, to make a long story short, we cannot use the excel export anymore. Now we have to save the report as a .txt file and import it into excel using the import wizard. I've been importing using fixed width. Once the data is pulled into excel, the sumproducts become #VALUE!. Here is the formula =SUM(('AXYS DATA'!$M$11:$M$65000="Research")*('AXYS DATA'!$K$11:$K $65000)*('AXYS DATA'!$I$11:$I$65000=SUMMARY!$B19)) (('AXYS DATA'!$M$11:$M$65000="Research") searches for the word "Research" in column M. However, after the .txt file is imported a routine is performed via macro to make the word show exactly as we want. First, the following formula is put in an adjacent column: =IF(I11="","",IF(M11="research","Research",IF(M11= "Best Execution","Best Execution",IF(M11="Directed","Directed","Other"))) ) Which returns one of the four words we're looking for. The if statement is then copied and the values pasted in column M. Therefore, the pasted values of this should return, unless I'm missing something. The ('AXYS DATA'!$K$11:$K$65000) is the variable row, as other columns are substituted to return the different sums. However, when I make this formula stand alone it does return a total. Therefore, I don't think this is the problem. (('AXYS DATA'!$I$11:$I$65000=SUMMARY!$B19)) "B19" is just filled with a broker code, that changes each row. Column B beside the sumproduct is pasted in from a filter of column I in the original workbook, so I know these cells are the same. If I manually export to excel, this workbook still works correctly. However, I will need to automate an export to .txt and use the import wizard. If anyone has any clue why my functions aren't working, please let me know. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Import to Excel Issue with Excel 2007 and Excel 2003 on same | Excel Discussion (Misc queries) | |||
Data import issue from Access | New Users to Excel | |||
Import External Data -- Fill down formulas Issue | Excel Worksheet Functions | |||
Import Issue | Excel Discussion (Misc queries) | |||
issue with excel 2003 data import | Excel Discussion (Misc queries) |