Formulas added in the column are lost after refresh

Discussion of issues, suggestions and bugs of Devart Excel Add-ins, our product line for connecting Excel to external data from cloud applications and databases
Post Reply
dsubramanian
Posts: 1
Joined: Thu 25 Oct 2018 06:37

Formulas added in the column are lost after refresh

Post by dsubramanian » Thu 25 Oct 2018 08:19

I have added formula to calculate values for a column , the formulas are getting lost once I commit the changes and data got refreshed ,is there any way to hold the formulas for the columns ? I have close to 100 formulas fields , which cant be added for every request.

Any help would be much appreciated

Pinturiccio
Devart Team
Posts: 2292
Joined: Wed 02 Nov 2011 09:44

Re: Formulas added in the column are lost after refresh

Post by Pinturiccio » Fri 26 Oct 2018 12:31

We could not reproduce the issue. Please tell us your Excel Add-ins version. Please also describe the steps you perform. What formula do you use and for which cells? Which actions should be performed to reproduce the issue?

Evaluator
Posts: 2
Joined: Fri 30 Nov 2018 09:17

Re: Formulas added in the column are lost after refresh

Post by Evaluator » Tue 04 Dec 2018 10:38

I have the same issue, using 2.0.2.0 Add-in to retrieve data from Zoho CRM v1 API.

Steps to reproduce:
  1. Open a new blank workbook in Excel
  2. Get data using connection (I used the Users table in Zoho CRM)
    A new Excel table 'Users' is created holding the retrieved data. It spans columns A:Q.
  3. Select cell R2
  4. Enter the formula =Users[Name]
    Column R is added to the table and filled with the users' names.
  5. On the Devart ribbon, click the 'Refresh' button
    You're asked "Are you sure you want to refresh data?"
  6. Click OK
    Query runs. Message is displayed: "An attempt to refresh a manually modified Excel table.
    The table was expected to contain 17 nonempty columns."
  7. Click OK
    Data is refreshed. Column R is now empty. Any formatting changes you applied to the table have gone.
I'm using the Add-in in Trial mode (I've purchased an activation key, but I'm not going to use it if you can't fix this issue).

Pinturiccio
Devart Team
Posts: 2292
Joined: Wed 02 Nov 2011 09:44

Re: Formulas added in the column are lost after refresh

Post by Pinturiccio » Thu 06 Dec 2018 12:07

When you refresh data in Devart Excel Add-ins 2.0.2, we do not recreate an Excel table, we just update data in it. This will prevent the crash of existing formulas that reference to not only a cell, but also to an Excel table column.

But when you add any value, not necessarily a formula, to the cell to the right of the table, the column on the right side is added to an Excel table. It is the Excel behavior, not the Devart Excel Add-ins one. After this, when you perform a refresh, an Excel table is recreated, since the table structure does not correspond to the select query (there are more columns than it should be). The column on the right side is deleted from the table. All other formulas that used column names become invalid.

You can use one of the following workarounds:
1. After your addition of the formula to the R2 cell, the R column is added to the Excel table and has the corresponding formatting. Press CTRL+Z at once. Formatting will disappear, but the formula will remain in the R2 cell. (but it will disappear from the other cells in the R column). You can use the Excel fill handle feature to make formula for each cell in the R column. The table structure is the same and during refresh, the table won't be recreated, and formula won't be deleted and remain valid.

2. Сreate a formula in the column located at the distance of 1 column from the table. In your case, it is the S2 cell.

Post Reply