Summing up content from hyperlink cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mort - 14 Sep 2007 08:26 GMT

Anyone have any idea on how I can make Excel sum up the displayed values of
several yperlink cells?

Reply to this Message

Gary''s Student - 17 Sep 2007 13:58 GMT

This is an EXCELLENT question !
I will use it next semester.

Let's say that in A1 thru A3 we have:

=HYPERLINK("http://www.microsoft.com","15")

each of the cells displays 15

However
=SUM(A1:A3) shows 0
This is because the 15's are actually text values. They must be converted
to numbers. Use:

=SUM(--A1:A3) which must be entered as an array formula with
CNTRL-SHIFT-ENTER rather than just ENTER

Signature

Gary''s Student - gsnu200745

> Anyone have any idea on how I can make Excel sum up the displayed values of
> several yperlink cells?

Reply to this Message

Dave Peterson - 17 Sep 2007 14:04 GMT

I didn't understand the original question, but why not just fix the formulas:

=HYPERLINK("http://www.microsoft.com","15")
becomes
=HYPERLINK("http://www.microsoft.com",15)

> This is an EXCELLENT question !
> I will use it next semester.
[quoted text clipped - 17 lines]
> > Anyone have any idea on how I can make Excel sum up the displayed values of
> > several yperlink cells?

Signature

Dave Peterson

Reply to this Message

Gary''s Student - 17 Sep 2007 14:42 GMT

You are correct.

The only advantage to my shabby little trick is that the OP needs to update
only a single SUM formula rather than a collection (potentially large) of
HYPERLINK formulas.

Signature

Gary''s Student - gsnu200745

> I didn't understand the original question, but why not just fix the formulas:
>
[quoted text clipped - 23 lines]
> > > Anyone have any idea on how I can make Excel sum up the displayed values of
> > > several yperlink cells?

Reply to this Message

Dave Peterson - 17 Sep 2007 16:51 GMT

It's a good bandaid <bg>. But sooner or later, there's gonna be more formulas
that refer to those cells. And someday, someone will miss the fix in the new
formula.

It always scares me when the original source isn't fixed--although, I'm sure
everyone has had to do scary things like this (when you depend on someone else
for the data, especially).

> You are correct.
>
[quoted text clipped - 35 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rate this thread: