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)

awhonn

Asked: 2011-09-23

Advertise Here

1 Solution

LVL 81

byundtCommented: 2011-09-23

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

LVL 50

barry houdiniCommented: 2011-09-23

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))

awhonnAuthor Commented: 2011-09-23

This worked perfectly, thank you! Unfortunately the "True" is text rather than boolean, but this totally fixed the formulas.

thank you!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Expand your skills with this month's free Premium course.

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

Join & Write a Comment Already a member? Login.

Featured Post