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