Solved

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

Posted on 2011-09-23
3
205 Views
Last Modified: 2012-05-12
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)
0
Comment
Question by:awhonn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 36589580
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 36589590
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
 

Author Closing Comment

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

thank you!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question