On Thu, 2 Dec 2004 04:47:06 -0800, "Suzanne"
wrote:

I have the following formula to capture staff timekeeping hours, identified
by codes, e.g., "3", "3.1", "3.1.1", "3.1.1.1" which is formatted as TEXT
(the time associated with each code is formatted as a number).

{=SUM(IF('L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan
04'!$B$2:$B$200="3.1",'L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan
04'!$C$2:$C$200))}

Problem: The formula is capturing all the data for codes which are three or
more digits ("3.1.1") but not data where the codes are one ("3") or two
digits ("3.1")

Two other findings...
1. If I go to the worksheet and physically type the same number over the
original, the data might (not always) show up, but a global search and
replace of the numbers to a formatted text number doesn't work.
2. Even though the cells are formatted as TEXT, if I physically put an
apostrophe in front of the one/two digit numbers, they will (always) show up.

I greatly appreciate any advice on how to correct this or globally place an
apostrophe in front of one/two digit numbers as I would really don't want to
go through an entire year of data for each person in our office.



It seems as if your formula is getting confused because some of the values in
the ranges get interpreted as numbers rather than text. One way to force them
to text would be to use the TEXT funtion in your formula. You can substitute
for your ranges (rg) the formula: =TEXT(rg,"@")


--ron