4 Replies Latest reply: May 15, 2014 9:52 AM by Steve Caiels

# Monarch Formula not calculating correctly

I discovered that I can create Excel Formulas in Monarch as character based fields. When I import them into Excel, Excel treats them as text, but if you copy/paste the cell on to itself, Excel will evaluate it as a formula.

However, I found that Monarch is not doing it's job properly in it's formula.

I have one calc-field translating the Rowno() into a string:

Trim(Str(Rowno()+1,8,0))

In another calc-field I'm using XLRow to build my Excel Formula:

"=IF(COUNTIF(VACATION,C" + ")=0,"QTEQTE",VLOOKUP(C" + + ",VACATION,2,FALSE))"

This one works fine for all rows.

However the next calc-field does not:

"=VLOOKUP(B" + +",[SoCalReport.XLS]Skills!\$A\$2:\$B\$100,2,FALSE)&IF(COUNTIF(Dues!\$A\$2:\$B\$100,RIGHT(F" + ",3))=0," QTE",""Dues"")"

NOTE: I have a calc-field called QTE that is returning the Quote character (Chr\$(34))

At row 158, the Sheet field skips the row number, even though XLRow is calculating correctly.  Here is a copy directly from the Monarch Table:

[font="courier"]XLRow V&amp;H                                                              Sheet

156   =IF(COUNTIF(VACATION,C156)=0,&quot;&quot;,VLOOKUP(C156,VACATION,2,FALSE))  =VLOOKUP(B156,[SoCalReport.XLS]Skills!\$A\$2:\$B\$100,2,FALSE)&amp;IF(COUNTIF(Dues!\$A\$2:\$B\$100,RIGHT(F156,3))=0

157   =IF(COUNTIF(VACATION,C157)=0,&quot;&quot;,VLOOKUP(C157,VACATION,2,FALSE))  =VLOOKUP(B157,[SoCalReport.XLS]Skills!\$A\$2:\$B\$100,2,FALSE)&amp;IF(COUNTIF(Dues!\$A\$2:\$B\$100,RIGHT(F157,3))=0

158   =IF(COUNTIF(VACATION,C158)=0,&quot;&quot;,VLOOKUP(C158,VACATION,2,FALSE))  =VLOOKUP(B159,[SoCalReport.XLS]Skills!\$A\$2:\$B\$100,2,FALSE)&amp;IF(COUNTIF(Dues!\$A\$2:\$B\$100,RIGHT(F159,3))=0

159   =IF(COUNTIF(VACATION,C159)=0,&quot;&quot;,VLOOKUP(C159,VACATION,2,FALSE))  =VLOOKUP(B160,[SoCalReport.XLS]Skills!\$A\$2:\$B\$100,2,FALSE)&amp;IF(COUNTIF(Dues!\$A\$2:\$B\$100,RIGHT(F160,3))=0[/font][/quote]Any idea why that calc-field would skip over 158?

[size="1"][ May 04, 2004, 03:34 PM: Message edited by: Nick Osdale-Popa ][/size]

• ###### Monarch Formula not calculating correctly

My only work-around for this is that when the table is exported to Excel, is to have Excel copy the formula from the top row down to the rest of the rows.

However, this does not solve the error that Monarch is creating.

• ###### Monarch Formula not calculating correctly

Nick

I am in awe that you use Monarch to populate Excel like this! What a great idea.

I looked at the help & wonder if your report is filtered or sorted. As per the help under recno:

"Returns the record number of a record in the table.  You can think of the record number as the detail line number, since any given record number will always correspond to the same detail line from the report (e.g., Recno=1 always corresponds to the first detail  This applies even if the table has been sorted.  This is not the case for the row numbers that you see at the left edge of the table.[/b]   This is not the case for the row numbers that you see at the left edge of the table."

Hoping this helps

Cheers  smile.gif[/img]

• ###### Monarch Formula not calculating correctly

if it was being filtered/sorted, that whole row would be gone, and that is not the case. The Sheet field is adding another 1 to the the XLRow field at row 158 and I can't fathom why.  This is definitely looking to be some type of bug in Monarch.

• ###### Monarch Formula not calculating correctly

Hi Nick,

I've recreated your fields (by copy and paste) and it works fine for me.

So I have no idea why it is doing this, but have you tried removing the [] around the field names.

You only need them for long field names.

Or maybe you could take the XLRow field out and replace it with Trim(Str(Rowno()+1)) straight into the expression? you don't need the ,8,0 as you are trimming and have no decimals.

Failing that, you could always try the good old Jet update from Microsoft.  This can fix all sorts of export problems, and as rowno() must be created from the underlying table, it's just possible it is another "Microsoft" database feature? take a look at [url="http://support.microsoft.com/default.aspx?kbid=239114#3"]http://support.microsoft.com/default.aspx?kbid=239114#3[/url]

Cheers

Steve