Insert Row Formula Strangeness

Originally Posted by Cavar

I hope someone can help explain why this is happening and a way to resolve it.

I am building an HTML file, using C#, representing budget data from a SQL database. The C# app calls a program called CZ Excel Converter, which uses Excel, to convert it to an XLS file. I have Excel 2003 installed. I have attached samples of both formats as well.

There are two budget formats produced depending on user settings. In both of the example the # symbol represents the current row number.

Format #1 (Annual) has a column (D) that contains the following formula, =SUM(E#:P#). Columns E-P are the cells where the user enters the monthly budget amounts and the sum column (D) is updated.

Format #2 (Monthly) has a column (D) that the user enters the yearly budget and monthly columns (E-O) that have the following formula, =ROUND(D#/12,2), and the December column (P) has the following formula, =ROUND(D#-SUM(E#:O#).

For both formats, each column has a SUM formula as the last row to total each month.

Both of these formats work as intended, until I insert a new row and the formulas don't work as expected.

When inserting a new row for the annual format by right clicking on any row between 7-13 and then entering in a value into any of the monthly columns (E-P) then only the total row for that column gets updated, not yearly budget cell (D) for the row.

I get a normal blank inserted row

When inserting a new row for the monthly format by right clicking on any row between 7-10 and then entering in a value into the yearly budget cell (D) for the row, then only the total cell for that column gets updated, but none of the monthly columns are. However, if you do the same thing on rows 11-13, then everything works as intended.

I still get a normal blank inserted row

Another strange thing, and this may happen to just me, is that if I insert a new row by right clicking on row 13 and then enter in the yearly budget, then the montly column amounts are populated correctly as are the monthly total cells, but the formula seem to run slower and I can actually see each monthly cell highlight instead of it happening instantly if I did the same thing on row 12.

Nothing strange, I still get a normal blank inserted row

On a side note, if I try to reproduce the budget spreadsheet manually, then none of the inserted row formulas work for either format.

I know this is a lot to take in and all this may be caused by the CZ Excel Converter program, but if anyone can help I would appreciate it.

Thanks,
CT

What you describe does not occur here, no formula is inserted into any inserted row, the normal 'drag' or formula-fill is required.

hth
---

Originally Posted by Cavar

When you right click on row 11 or 12 in the monthly sample file and insert a new blank row, then enter a numeric value in the 2006 Budget column (D) you don't see the monthly columns update? But, if you do the same thing on rows 7-10 none of the monthly columns are updated?

I can reproduce the behavior easily.

Also, when inserting a new row on 11 or 12 and entering a value as stated above, and all values have been updated, you can then click on any of the monthly rows and see the formulas have been copied automatically, but not on rows 7-10.

If you need me to, I can make a short video of this happening.

Thanks,
CT

As stated, no.

What you describe does not occur here, no formula is inserted into any inserted row, the normal 'drag' or formula-fill is required.

Whilst there is a difference between Sample 1 row and other rows, I would hardly clssify =Round(0,2) (which is overwritten when you enter an amount) as a 'required' formula.

see the attached.
---

Originally Posted by Cavar

Sorry to keep going on about this, especially since you can't reproduce it, but it's driving me crazy.

I have made an animated gif showing exactly what I am talking about. Unfortunately, it's too large zipped to upload here, so I will have to try and find another way to get it to you.

Thanks,
CT

Have you tried to unload the CZ Excel Converter program, or checked for Addins (Tools, Addins), or deleted any Personal.xls files (with Excel closed), or checked Alt/F11 for any additional code?

Perhaps it coud be cleared that way.

---

Originally Posted by Cavar

I haven't tried any of those yet, but I am pretty sure it may have something to do with the CZ Excel.

If I create a new Excel workbook and manually replicate the formulas I can't reproduce the behavior as I can when I create the Excel document from an HTML file using CZ Excel.

We have to use CZ Excel to produce the files the way we are, for the moment, and would love to have ALL rows work the same instead of just some of them, but it's not a huge deal since it's been working this way for awhile and we end up doing server side validation on each file uploaded anyway.

I will try to upload the animated gif somewhere this weekend so you can see it happen, but if it never gets figured out then it'll still be ok.

Thanks,
CT

If this is the SmartSoft product, did you try on http://smartpdfconverter.com/support.htm

They may have some quick-fix already known.

---

Originally Posted by Cavar

It's not that product, it's this one.

Thanks,
CT

That site advertises ", only $159.95, 30-day money-back guarantee, technology support and upgrades are free forever for registered users." - and they would be the best equipped to answer your question.

hth
---