Formulas not working in Excel sheet without Edit, linked to SQL table

I have seen similar questions but not this exact one yet.

I have a SQL table linked into my excel workbook. On one sheet I have populated the data in tabular format, on another, I have a report that uses several formulas similar to:   =Countif(Section3!AF2:AF200, TRUE)

The formulas generate 0 until I click through each field in the table, which is pretty extensive. The column in SQL is Varchar, the column in Excel says General. And because it is linked, anything I do to the excel sheet is temporary...

Any ideas for a permanent fix for this?

thank you!

(Using Excel 2007)
awhonnAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
If TRUE is in text format (rather than Boolean value) that's difficult to count with COUNTIF - try using SUMPRODUCT instead, i.e.

=SUMPRODUCT((Section3!AF2:AF200="TRUE")+0)

or to make sure any TRUE is counted...text or Boolean

=SUMPRODUCT((Section3!AF2:AF200="TRUE")+(Section3!AF2:AF200=TRUE))
0
 
byundtCommented:
One trick for getting formulas to update is:
1) Select the range containing the formulas
2) In the Home menu, go to the Find & Select item, choose Replace...
3) Put an equals sign in both the Find what and Replace with fields, then click Replace All
0
 
awhonnAuthor Commented:
This worked perfectly, thank you! Unfortunately the "True" is text rather than boolean, but this totally fixed the formulas.

thank you!
0
All Courses

From novice to tech pro — start learning today.