Syntax Error on Table Formula

[MayoRR5]

Hi guys, I have a couple of queries.... shocking I know.

I have bookmarked some tables and I'm getting a

!Syntax Error, (
​

on this formula below
{ =(PVTableB(B2))*(PVTableD(B2)) \# "0.00" }



Also, I have bookmarked some cells on another table and get a zero when trying to reference them directly using
{ =FITRate \# "0.00" }


I get a syntax error (same as above) when using
{ =SUM(FITRate*1) \# "0.00" }


But I can get the value to appear when I use
{ =SUM(PVTableC B2) \# "0.00" }


These are numeric values.

I'm fairly new to the word formula topic, so all help is appreciated.

Thanks in advance.

 

[kevlray]

Just because I was curious, I tried to do a similar formula. Unfortunately I ran into the same issues. But I did find a solution as weird as it is. {=sum(PVTableB B2)*sum(PVTable DB2)\#"0.00"}

 

[MayoRR5]

Update guys. I ended removing the cell bookmarks and adding table bookmarks only and then referencing the cell reference each time (PVTableB A2, etc.) and it worked.

Couple of notes I've made once I completed the exercise are as follows:-

1. When a formula was working and I wanted to check it, I was using the formula button on the ribbon to view it. Once I was happy I'd hit OK and get a syntax error. This procedure was removing the space between the table reference and the cell reference, i.e.

=(SUM(PVTableB B2))*(SUM(PVTableC B7)) would return correct result
=(SUM(PVTableBB2))*(SUM(PVTableCB7)) would return syntax error

Make sure the space stays there or there is no cell reference. (Alt + F9 does not do this)


2. My tables only ever had number values (aside from headers, etc.) entered but in order to have my formula return a value based on these, I had to SUM each individual cell separately in the formula and then add / multiply them, i.e.

=SUM(PVTableB B2)*(PVTableC B7) would return syntax error
=(SUM(PVTableB B2))*(SUM(PVTableC B7)) would return correct result

I don't have a reason of why the first attempt wouldn't work because as far as I can tell it's correct.


3. My document has 4 tables, with approx. 100 cells all calculating formulas (Solar PV calculations) and I've found the following issue. When I update these cells, it seems to update row by row and some of the rows don't update. That's because the cells above are referencing the cells below and are not using the updated values to update. My solution was record a macro where I selected my entire document and ran the update function (F9) several times (10 in total, just to be safe ) and assigned this function to keyboard shortcut. This resulted in the cells referencing the updated data and showing correct results.


I've found this exercise made me appreciate the tool that is MS Excel (and Access ) because with so many cells I couldn't "copy" the code across the entire table. I had to manually adjust the cell references for each cell. A tedious exercise but worth it for the final product, where I have a quotation template that professional, uniformed (before they were a Excel spreadsheet that would look nothing like the rest of the quotation) and independent of any other programs.

I hope this info. is helpful or if I've just been talking s#!t, let me know. hahaha

Cheers,
MayoRR5