My formulas can't 'see' numbers imported from Access in Excel, even when the format is the same.

Does anyone know why? Or how to make that happen w/o retyping all 200
numbers (employee numbers) multiple times on the list? I know that
when I retype the numbers then the formulas can 'see' the number and do
the calcualtion, but not if I don't retype. The formats are the
same(number).

A C Z AA
1 EmpNo Units Empno Ttl Units
2 77 7 77 10 =SUMPRODUCT(Empno=Z2)*(units))
3 77 1 9533 11
4 77 2 10058 14
5 9533 7
6 9533 1
7 9533 3
8 10058 6
9 10058 2
10 10058 3
11 10058 3

Any and all help would be greatly appreciated

Thanks
Chrism

Hi Chris,
If you don't want to use a macro you could select an
empty cell and copy it (Ctrl+C) then select the cells
to be reentered, edit, paste special, add

Note this could change formatted values such as dates
to numbers but formulas would remain intact, except for
the addition.

A better way is to select the cells or columns you want to
reenter and run the TrimALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
There are important notes along with macro, if you are
interested in finding out what you actually had in your cells.
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Chrism" <[email protected]> wrote in message news:[email protected]...
> Does anyone know why? Or how to make that happen w/o retyping all 200
> numbers (employee numbers) multiple times on the list? I know that
> when I retype the numbers then the formulas can 'see' the number and do
> the calcualtion, but not if I don't retype. The formats are the
> same(number).
>
> A C Z AA
> 1 EmpNo Units Empno Ttl Units
> 2 77 7 77 10 =SUMPRODUCT(Empno=Z2)*(units))
> 3 77 1 9533 11
> 4 77 2 10058 14
> 5 9533 7
> 6 9533 1
> 7 9533 3
> 8 10058 6
> 9 10058 2
> 10 10058 3
> 11 10058 3
>
> Any and all help would be greatly appreciated
>
> Thanks
> Chrism
>

I believe the root of the problem is how you're defining your fields in
Access. If you want to attack the problem there, you need to review your
field definitions; you may need to experiment. I also encountered this when
exporting from Access to Excel. I can't recall the details of my situation,
but I found that making some change to the field definitions solved the
problem.

David

"Chrism" <[email protected]> wrote in message
news:[email protected]...
> Does anyone know why? Or how to make that happen w/o retyping all 200
> numbers (employee numbers) multiple times on the list? I know that
> when I retype the numbers then the formulas can 'see' the number and do
> the calcualtion, but not if I don't retype. The formats are the
> same(number).
>
> A C Z AA
> 1 EmpNo Units Empno Ttl Units
> 2 77 7 77 10 =SUMPRODUCT(Empno=Z2)*(units))
> 3 77 1 9533 11
> 4 77 2 10058 14
> 5 9533 7
> 6 9533 1
> 7 9533 3
> 8 10058 6
> 9 10058 2
> 10 10058 3
> 11 10058 3
>
> Any and all help would be greatly appreciated
>
> Thanks
> Chrism
>

Hello Chris

You also seem to have missed a bracket in your formula. Should be
=SUMPRODUCT((Empno=Z2)*(units)). Check after trimming your data.

regards
Peter

"Chrism" wrote:

> Does anyone know why? Or how to make that happen w/o retyping all 200
> numbers (employee numbers) multiple times on the list? I know that
> when I retype the numbers then the formulas can 'see' the number and do
> the calcualtion, but not if I don't retype. The formats are the
> same(number).
>
> A C Z AA
> 1 EmpNo Units Empno Ttl Units
> 2 77 7 77 10 =SUMPRODUCT(Empno=Z2)*(units))
> 3 77 1 9533 11
> 4 77 2 10058 14
> 5 9533 7
> 6 9533 1
> 7 9533 3
> 8 10058 6
> 9 10058 2
> 10 10058 3
> 11 10058 3
>
> Any and all help would be greatly appreciated
>
> Thanks
> Chrism
>
>

Your Macro worked perfectly! I will put it to very good use, often.

Thank You kindly

Chrism