Evaluate a formula

Evaluate a formula

Posted by: sally on 22 April 2020, 4:37 am EST

• 

  sally

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 22 April 2020, 4:37 am EST

  Hi Grapecity,

  Our application allows a customer to read specified cells from an Excel worksheet. We use the Spread control to accomplish this.

  Normally there is not an issue, but sometimes the calculation of the cell value includes complex formulas such as:
  
  =IF(ISERROR(MATCH(DATE(YEAR(AZ2)-1,MONTH(AZ2),DAY(AZ2)),2:2,0)),0,(INDIRECT(ADDRESS(5,MATCH(DATE(YEAR(AZ2)-1,MONTH(AZ2),DAY(AZ2)),2:2,0)))+INDIRECT(ADDRESS(6,MATCH(DATE(YEAR(AZ2)-1,MONTH(AZ2),DAY(AZ2)),2:2,0))))*(1-INDIRECT(“Summary!”&ADDRESS(MATCH(“TMS Churn”,SUMMARY!$A:$A,0),MATCH(YEAR(AZ2),SUMMARY!$A$5:$K$5,0)))))

  The spread does not like this formula and doesn’t include it in the cell calculation. In trying to evaluate the formula (which does work in Excel), I went through Spread help to make sure all formulas such as MATCH, ADDRESS, etc are accepted by Spread (they are).

  What else can I do to figure out why this formula isn’t acceptable to Spread? I’ve also checked the cell references in the formula to make sure they are valid.

  Thanks,
  
  Sally

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 23 April 2020, 5:57 pm EST

  Hello Sally,

  When I use the above formula in excel and spread, I get the same result “0”. Could you please share the excel file so that I can find the difference between them. This will be very helpful for us.

  Thanks,
  
  Mohit

• 

  sally

  • Post Options:
  • Link
    Copy

  Posted 24 April 2020, 4:13 am EST

  Hi Mohit,
  
  Thanks for your response. I’ve attached the Excel file. The customer is trying to read the number from the Revenue tab, cell BB53. The Sum formula in that cell refers to cell BB$9. The formula in that cell refers to the Bookings tab cell AZ6. If you go to that cell you’ll see the formula in question. If you can give me any indications of what the problem might be, that would be great.

  Thanks,
  
  SallyCopy for GC.zip

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 27 April 2020, 12:34 am EST - Updated 30 September 2022, 4:32 am EST

  Hello,

  I load the excel file in the SpreadDesigner and formula evaluate successfully. Please refer to the attached image:

  Thanks,
  
  Mohit
  
  

  

  1330×394 37.4 KB

• 

  sally

  • Post Options:
  • Link
    Copy

  Posted 27 April 2020, 4:24 am EST - Updated 30 September 2022, 4:32 am EST

  Hi Mohit,

  Yes, it does appear in the designer however, when I load the file in our application, the number isn’t appearing (see attached). I’m using the following command:
  
  bRtn = spdLinkSheet.OpenExcel(sExcelName). Could that be my problem?
  
  

  

  862×367 41 KB

  Thanks,
  
  Sally

• 

  sally

  • Post Options:
  • Link
    Copy

  Posted 27 April 2020, 5:04 am EST

  Hi Mohit (again),
  
  I added a new project with just a spread control and I loaded the Excel worksheet. The number does come up so the problem is within the application. I’ll take a look and let you know what I find.

  Thanks,
  
  Sally

• 

  sally

  • Post Options:
  • Link
    Copy

  Posted 19 May 2020, 5:54 am EST

  Hi Mohit,

  Sorry not to respond sooner, but I wanted to let you know that we resolved the issue by removing and re-adding the spread control to the form.

  Thanks for your help,
  
  Sally