Suzanne,

I don't fully understand your formula since it appears to be looking only
for 3.1, but perhaps that's just an abbreviated example.

I fooled around a little with this and found the following which may be
helpful.

First of all, 3 and 3.1 would both be recognized by Excel as numbers.
That's probably why those values are causing you difficulty.

If I do a replace 3.1 with '3.1, that seems to work. That would be in the
data area. A "replace all" will start changing the formula and won't be
helpful.

Also, and I don't know if this is helpful or not, if I format one of those
cells to text, then replace 3.1 with '3.1, and THEN replace it back to 3.1 I
seem to get a text value that works. It appears that formatting the cell as
text does not make it a text value for your purposes. However, if you then
put the ' in front of it, and take it away, the cell then becomes text.

Art


"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.