Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-23
3
Medium Priority
?
207 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

688 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