Solved

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

Posted on 2011-09-23
3
201 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
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

919 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now