1

I've got a workbook set up in Excel 365 (2016) to Get External Data into columns 1-8. I have the first two columns configured do some formatting based on G1=1. The formatting works fine if I type a 1 in the appropriate column. However, I have the data connection writing data to column G, and the import from the external data source does NOT trigger the conditional formatting. If I find a cell that retrieved a 1 from the data connection and re-type the 1, the formatting is triggered. Explicitly formatting column G as number does not help. Is there a way to conditionally format based on data retrieved via an external data source?

3
  • 1
    It's a little unclear, but I might have an idea. Since you haven't inserted a table to underscore your paragraph, not placed an image, I will try try to GUESS a little. Note that a 1 ins't the same as a "1" or " 1". If you understand that then you can further troubleshoot.
    – ejbytes
    Nov 18, 2016 at 23:13
  • Display a graph and the formula you are using. Are you returning a VALUE or a TEXT? Returning a String or Integer or Double? Hence my comment above.
    – ejbytes
    Nov 19, 2016 at 0:16
  • 1
    Yes, this was a datatype issue. Both the the data source ( FileMaker Pro ) and Excel are a bit loosey-goosey with data types, but they both seem to strictly enforce the source datatype on import. I had been using some generic columns in text format to stage the data, so Excel saw the incoming data as text. I changed the source column to number and all is good. As you suggest, I may have been able to solve the problem by changing the formula to G1="1" instead of G1=1, but this seems less prone to user error on the Excel side. If you provide a formal Answer I can call this answered. Nov 23, 2016 at 20:14

1 Answer 1

0

As the Original Poster found out, the culprit is usually an analysis check on data types.

When importing data, if left in it's default settings, the data will be imported as text. A number is a number, text or otherwise, but it's not until you have to perform calculations that you might encounter errors in data types.

"In computer science and computer programming, a data type or simply type is a classification of data which tells the compiler or interpreter how the programmer intends to use the data. Most programming languages support various types of data, for example: real, integer or Boolean."
-- https://en.wikipedia.org/wiki/Data_type

To add to the explanation above, a type of data can also be: Character, String, or 'literal'.

In Excel it's important to also remember that if you change a number's format from text to number, you'll have to refresh that cell. One way to refresh a cell after the format has changed is to select the cell, press F2, then enter.

Conclusion: Often in the computer world we might look at a number and assume it's identity. A computer may read a number in many different ways: 1, "1", " 1".

Troubleshooting: Verify the cell's format and checking for extra characters (i.e. spaces). Also remember to Refresh the cell if the format is changed. This is not in any way a natural process of reasoning unless you have been exposed to computer science studies and even if you are, these conclusions are easily overlooked and often are.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .