External data _ TEXT problem

I am having some trouble with importing data.Its an MTA file I can import it
ok.I bring it all through as general format.I have a series of formulas on
the second sheet ie =sheet1!A2 because all the references go haywire when the
new data comes in.It seems to be inserted rather than pasted.However there is
a column of numbers which i am trying to sum if (on sheet 2 ) if another
letter is A in the same row.=istext(B8) is true but
if(istext(B8),"text","not")) comes up as not and my sumif is always 0.
i am not sure if the formula should be =sumif(B8:B3000,A,E8:E3000) or
=sumif(B8:B3000,"A",E8:E3000) either.
--
paul
remove nospam for email addy!

Unless A is a name, you'll want to use this:

=sumif(B8:B3000,"A",E8:E3000)
(with the quotes around "A")

And shouldn't you be checking:
=if(istext(E8),"text","not")

or maybe...

=count(e8:e3000)
=counta(e8:e3000)

=Count() will return the count of real numbers in E8:E3000.
=counta() will tell you the count of alpha/numeric or alpha-numeric cells.

If they match, then it's not a numeric problem.

I don't know what a .mta file is, but if there are funny characters like
non-breaking spaces in that cell, then that could be the problem. (Does an .mta
file come from the Web (HTML stuff)??)

Chip Pearson has a nice addin that can help you determine each character in a
cell:
http://www.cpearson.com/excel/CellView.htm

If it turns out that you have spaces (or those non-breaking spaces), David
McRitchie has some code that will help clean this junk up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

paul wrote:
>
> I am having some trouble with importing data.Its an MTA file I can import it
> ok.I bring it all through as general format.I have a series of formulas on
> the second sheet ie =sheet1!A2 because all the references go haywire when the
> new data comes in.It seems to be inserted rather than pasted.However there is
> a column of numbers which i am trying to sum if (on sheet 2 ) if another
> letter is A in the same row.=istext(B8) is true but
> if(istext(B8),"text","not")) comes up as not and my sumif is always 0.
> i am not sure if the formula should be =sumif(B8:B3000,A,E8:E3000) or
> =sumif(B8:B3000,"A",E8:E3000) either.
> --
> paul
> remove nospam for email addy!

--

Dave Peterson

thanks Dave you helped me crack it there were trailing blanks or
spaces....=trim fixed it.MTA files open with notepad they are just data files
from the CAD package I use for prenailed framing and gangnail trusses for
houses(mostly)
--
paul
remove nospam for email addy!



"Dave Peterson" wrote:

> Unless A is a name, you'll want to use this:
>
> =sumif(B8:B3000,"A",E8:E3000)
> (with the quotes around "A")
>
> And shouldn't you be checking:
> =if(istext(E8),"text","not")
>
> or maybe...
>
> =count(e8:e3000)
> =counta(e8:e3000)
>
> =Count() will return the count of real numbers in E8:E3000.
> =counta() will tell you the count of alpha/numeric or alpha-numeric cells.
>
> If they match, then it's not a numeric problem.
>
> I don't know what a .mta file is, but if there are funny characters like
> non-breaking spaces in that cell, then that could be the problem. (Does an .mta
> file come from the Web (HTML stuff)??)
>
> Chip Pearson has a nice addin that can help you determine each character in a
> cell:
> http://www.cpearson.com/excel/CellView.htm
>
> If it turns out that you have spaces (or those non-breaking spaces), David
> McRitchie has some code that will help clean this junk up:
>
> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> (look for "Sub Trimall()")
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> paul wrote:
> >
> > I am having some trouble with importing data.Its an MTA file I can import it
> > ok.I bring it all through as general format.I have a series of formulas on
> > the second sheet ie =sheet1!A2 because all the references go haywire when the
> > new data comes in.It seems to be inserted rather than pasted.However there is
> > a column of numbers which i am trying to sum if (on sheet 2 ) if another
> > letter is A in the same row.=istext(B8) is true but
> > if(istext(B8),"text","not")) comes up as not and my sumif is always 0.
> > i am not sure if the formula should be =sumif(B8:B3000,A,E8:E3000) or
> > =sumif(B8:B3000,"A",E8:E3000) either.
> > --
> > paul
> > remove nospam for email addy!
>
> --
>
> Dave Peterson
>