Formula not reading imported data

[pskwaak]

I have a worksheet, "Source List," that is copied from a database into Excel.
Column O lists customer codes; other columns identify associated data (name,
badge#, etc.) of employees that work with that customer.

I have another worksheet, "Profile." Column A lists the same customer
codes; column F contains SUMIF formulas extracting data from the Source List
identifying the number of employees working for that customer.

The problem is that formulas for some codes result in "0," when in fact
there are employees working for that customer. If I manually type over the
code in column A (without changing the formula), the formula reads properly,
.. I changed the cell/column format to "text" on both Source List and Profile
worksheets -- to no avail.

Why isn't the formula reading the customer code?

Hope I'm clear on this .... thanks

 

[Niek Otten]

Here's my standard list


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

=================================================================
Your Numbers don't behave (like numbers)
Niek Otten, May 11, 2006

Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc.
In short:

Your Numbers look like Numbers, but they really are Text.
Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text!

Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's
ISNUMBER() function to check your cells; maybe you solved your problem in the first step!

· Format an empty cell as Number. Enter the number 1 in it. Edit>Copy.
Select your "numbers". Edit>Paste Special, check Multiply. Hopefully your cells are "real" Numbers now
· If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number
of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM()
function to remove them
· If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function
to remove most of them
· If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use
David McRitchie's TRIMALL() function to remove them. It can be downloaded here:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall



=================================================================

|I have a worksheet, "Source List," that is copied from a database into Excel.
| Column O lists customer codes; other columns identify associated data (name,
| badge#, etc.) of employees that work with that customer.
|
| I have another worksheet, "Profile." Column A lists the same customer
| codes; column F contains SUMIF formulas extracting data from the Source List
| identifying the number of employees working for that customer.
|
| The problem is that formulas for some codes result in "0," when in fact
| there are employees working for that customer. If I manually type over the
| code in column A (without changing the formula), the formula reads properly,
| . I changed the cell/column format to "text" on both Source List and Profile
| worksheets -- to no avail.
|
| Why isn't the formula reading the customer code?
|
| Hope I'm clear on this .... thanks
|

 

[pskwaak]

Thanks, Niek, I'll try the TRIM or CLEAN functions. The customer codes
consist of letters only, no numbers....will these functions work with letters?