Sum() Formula won't pick up text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a formula that picks up values and text in another worksheet file. I used the Sum() function, because if I use the + and reference to the cell in the other worksheet file, it puts the long name in i.e.C:\data\exceldata\... etc. I want to share the file, and when I go to copy the files to a floppy, it keeps the Drive letter and folders in the formula. So, this is why I used the Sum() function. But, for some reason, the Sum() function won't pick up text, only values. How can I change this? I sure would appreciate any help you can give.
 
Hi Dale
not sure what you're trying to achieve. SUM can only process values
(how would you sum text values??). So maybe you can give an example
which explains your requirements with more detail :-)
 
Hi Dale,

Adding to Frank's reply, I'm guessing you may want to use COUNTIF or
COUNTA??

HTH
Regards,
Howard

Dale said:
I have a formula that picks up values and text in another worksheet file.
Click to expand...
I used the Sum() function, because if I use the + and reference to the cell
in the other worksheet file, it puts the long name in
i.e.C:\data\exceldata\... etc. I want to share the file, and when I go to
copy the files to a floppy, it keeps the Drive letter and folders in the
formula. So, this is why I used the Sum() function. But, for some reason,
the Sum() function won't pick up text, only values. How can I change this?
I sure would appreciate any help you can give.
 
Sorry about that. I guess I should know that the Sum() function only picks up values. All I want to do is pick up the value(or text) from another worksheet file to show in the worksheet where I have the summary of about 6 other files. When I first developed the formulas, I used the + and picked up the cell on the other file. But, Excel put the full details in the formula: C:\data\exceldata\..... etc. When I moved these files to a floppy so I could share them with another computer, it kept the C:\data\exceldata\...etc. instead of automatically chaning to A:\filename. So, when I learned on this bulletin board that by using the Sum() function, it would not use the full reference in the formula, I was delighted...until I found out that, of course, the Sum() function only picks up values

Is there another way around this?
 
Thanks for your reply Howard. With this application I only want to pick up the value or text for one cell on a separate file. I don't want to count them or add them up

Dale.
 
Copy the file, then copy and paste special values the area in question


: I have a formula that picks up values and text in another worksheet file. I
used the Sum() function, because if I use the + and reference to the cell in the
other worksheet file, it puts the long name in i.e.C:\data\exceldata\... etc. I
want to share the file, and when I go to copy the files to a floppy, it keeps
the Drive letter and folders in the formula. So, this is why I used the Sum()
function. But, for some reason, the Sum() function won't pick up text, only
values. How can I change this? I sure would appreciate any help you can give.
 
Thanks for your help. But, the copy and paste special
values will not work for what I am doing with this
worksheet. The master file (that has a sheet for each
branch office )will be at our head office, and each branch
office will then e-mail their separate sheet to the head
office to be saved in the same folder as the master
sheet. I want to link each cell in the master sheet to
the respective branch sheet, so it updates the master
sheet automatically. Once thing I did notice as well
about my problem with the full address being inserted in
the cell, that when the branch file is open, it doesn't
put the entire address in the cell; however, when I copy a
branch file and the master file to a floppy disk, and have
both files open, it still wants to refer to the branch
file on the C: drive. I sure hope you can help me.
-----Original Message-----
Copy the file, then copy and paste special values the area in question


: I have a formula that picks up values and text in another worksheet file. I
used the Sum() function, because if I use the + and reference to the cell in the
other worksheet file, it puts the long name in
Click to expand...
i.e.C:\data\exceldata\... etc. I
 
I have a formula that picks up values and text in another worksheet file.
I used the Sum() function, because if I use the + and reference to the cell
in the other worksheet file, it puts the long name in i.e.
C:\data\exceldata\... etc. I want to share the file, and when I go to copy
the files to a floppy, it keeps the Drive letter and folders in the formula.
So, this is why I used the Sum() function. But, for some reason, the Sum()
function won't pick up text, only values. How can I change this? I sure
would appreciate any help you can give.
Click to expand...

I've read the other responses and your follow-ups, so I'm responding to more
than just this, your original post.

You're trying to use

SUM(ReferenceToSingleCell)

rather than

+ReferenceToSingleCell

Are you trying to use these as entire formulas?

Regardless, if you believe that using SUM eliminates the 'problem' of Excel
converting base filename-only external references to full pathname external
references when you close the file containing the cells to which your external
reference links refer, you are mistaken. If you have a problem with

='[foo.xls]bar'!X99

becoming

='C:\one\subdir\and\another\and\another\yada\yada\yada\[foo.xls]bar'!X99

You'll have *exactly* the *same* problem using SUM. That is,

=SUM('[foo.xls]bar'!X99)

under the same changes will become

=SUM('C:\one\subdir\and\another\and\another\yada\yada\yada\[foo.xls]bar'!X99)

Try it if you don't believe me.

Excel has a long-standing limitation that it can't support multiple files with
the same base filename being open simultaneously. E.g, C:\what\foo.xls and
D:\ever\foo.xls can't be open at the same time because both have the same base
filename foo.xls. That allows the very short-sighted 'benefit' of only needing
to use the base filename in external references like '[foo]bar'!X99 when a file
with base filename foo.xls is open. However, Excel does *NOT* refer to whatever
foo.xls file is open. Rather, it refers to the specific foo.xls file that was
open when you created the external link to cells within it. E.g., if the file
C:\what\foo.xls were open when you enter either of the formulas

='[foo.xls]bar'!X99

or

=SUM('[foo.xls]bar'!X99)

then you save the file containing this external reference as X:\test.xls, then
you quit and relaunch Excel, open D:\ever\foo.xls, then open X:\test.xls again,
you'll find that the external references do *NOT* refer to D:\ever\foo.xls which
is open and seemingly available as '[foo.xls]'. Instead, both formulas above
would change to

='C:\what\[foo.xls]bar'!X99

or

=SUM('C:\what\[foo.xls]bar'!X99)

There's a lesson here. EXCEL DOES *NOT* PROVIDE RELATIVE FILE REFERENCES. *ALL*
EXTERNAL REFERENCES IN EXCEL INVOLVE *FULL*/*ABSOLUTE* PATHNAMES. There are no
exceptions, and *ALL* workarounds involve VBA, either altering the drive and
directory paths in all formulas or by using the alternatives in

http://www.google.com/[email protected]
 
Back
Top