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

Solved

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

Posted on 2011-09-23

1 solution

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)

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

Join The Community

3 Comments

LVL 81

Expert Comment

by:byundt

ID: 365895802011-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

Accepted Solution

by:barry houdini

barry houdini earned 2000 total points

ID: 365895902011-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))

Author Closing Comment

by:awhonn

ID: 365896142011-09-23

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

thank you!

Featured Post

Question has a verified solution.

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

Introduction to SQL Server Containers

Article by: Paul

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.

SSMS failed to connect to SSIS

Article by: Vitor

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 …

SQL - The SELECT Statement

Video by: Zia

Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Microsoft SQL Server

How to use the IF function in Excel

Video by: Bob

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…