I need to create a list that is associated with a particular cell where I can
sum the values in the list and display the sum in the associated cell.

For example, if I right-click on cell A1, I would like to be able to
create/edit a list that can give me the sum of its values in cell A1.

It would be like creating a comment balloon where instead of having a text
area, I would have a small list and whatever calculation I do to that list
the result would be displayed back in the cell associated with it.

I have done extensive search on this and have found nothing like this, but
if anyone knows if I can do this and would like to help me, I would
appreciate it.

Thanks.

You could do it, but excel really isn't designed to work this way. Changing
comments doesn't cause excel to recalculate--so you could change a comment (or a
few comments) and the display in the cell could be out of date.

On top of that, you'd have to build all the parsing routines that you wanted
into a user defined function.

For instance, if you programmed the UDF to accept commas 1,2,3 and you used
1,2,3;4, you'd either get something you didn't want or your UDF would have to be
"smart" enough to accept either.

And if you list was typed in as 1,2,3,see Msantos for more info,5,6,7
what would you do?

But if you want, you could try this UDF:

Option Explicit
Function EvaluateComment(Optional myCell As Range) As Variant
Application.Volatile
Dim myStr As String

If myCell Is Nothing Then
Set myCell = Application.Caller
End If

If myCell.Comment Is Nothing Then
EvaluateComment = ""
Else
myStr = myCell.Comment.Text
myStr = Application.Substitute(myStr, ",", "+")
myStr = Application.Substitute(myStr, ";", "+")
EvaluateComment = myCell.Parent.Evaluate(myStr)
End If

End Function


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

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=evaluatecomment(A99)
(to look at the comment in cell 99).

or
=evaluatecomment()
to look at the comment in the cell holding the function.

Msantos wrote:

I need to create a list that is associated with a particular cell where I can
sum the values in the list and display the sum in the associated cell.

For example, if I right-click on cell A1, I would like to be able to
create/edit a list that can give me the sum of its values in cell A1.

It would be like creating a comment balloon where instead of having a text
area, I would have a small list and whatever calculation I do to that list
the result would be displayed back in the cell associated with it.

I have done extensive search on this and have found nothing like this, but
if anyone knows if I can do this and would like to help me, I would
appreciate it.

Thanks.

--

Dave Peterson

Thanks Dave, it worked for me almost exactly like I imagined. What I
envisioned though was something like instead of the comment box a little grid
or worksheet. But the solution you gave me works too. And thank you for the
short course in macros, that saved me a lot of time researching on how I
would implement this solution.

Thanks a lot.

"Dave Peterson" wrote:

You could do it, but excel really isn't designed to work this way. Changing
comments doesn't cause excel to recalculate--so you could change a comment (or a
few comments) and the display in the cell could be out of date.

On top of that, you'd have to build all the parsing routines that you wanted
into a user defined function.

For instance, if you programmed the UDF to accept commas 1,2,3 and you used
1,2,3;4, you'd either get something you didn't want or your UDF would have to be
"smart" enough to accept either.

And if you list was typed in as 1,2,3,see Msantos for more info,5,6,7
what would you do?

But if you want, you could try this UDF:

Option Explicit
Function EvaluateComment(Optional myCell As Range) As Variant
Application.Volatile
Dim myStr As String

If myCell Is Nothing Then
Set myCell = Application.Caller
End If

If myCell.Comment Is Nothing Then
EvaluateComment = ""
Else
myStr = myCell.Comment.Text
myStr = Application.Substitute(myStr, ",", "+")
myStr = Application.Substitute(myStr, ";", "+")
EvaluateComment = myCell.Parent.Evaluate(myStr)
End If

End Function


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

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=evaluatecomment(A99)
(to look at the comment in cell 99).

or
=evaluatecomment()
to look at the comment in the cell holding the function.

Msantos wrote:

I need to create a list that is associated with a particular cell where I can
sum the values in the list and display the sum in the associated cell.

For example, if I right-click on cell A1, I would like to be able to
create/edit a list that can give me the sum of its values in cell A1.

It would be like creating a comment balloon where instead of having a text
area, I would have a small list and whatever calculation I do to that list
the result would be displayed back in the cell associated with it.

I have done extensive search on this and have found nothing like this, but
if anyone knows if I can do this and would like to help me, I would
appreciate it.

Thanks.

--

Dave Peterson

Be careful with that function. Don't depend on any of the values displayed
until your worksheet is recalculated.

(I still wouldn't do this.)

Msantos wrote:

Thanks Dave, it worked for me almost exactly like I imagined. What I
envisioned though was something like instead of the comment box a little grid
or worksheet. But the solution you gave me works too. And thank you for the
short course in macros, that saved me a lot of time researching on how I
would implement this solution.

Thanks a lot.

"Dave Peterson" wrote:

You could do it, but excel really isn't designed to work this way. Changing
comments doesn't cause excel to recalculate--so you could change a comment (or a
few comments) and the display in the cell could be out of date.

On top of that, you'd have to build all the parsing routines that you wanted
into a user defined function.

For instance, if you programmed the UDF to accept commas 1,2,3 and you used
1,2,3;4, you'd either get something you didn't want or your UDF would have to be
"smart" enough to accept either.

And if you list was typed in as 1,2,3,see Msantos for more info,5,6,7
what would you do?

But if you want, you could try this UDF:

Option Explicit
Function EvaluateComment(Optional myCell As Range) As Variant
Application.Volatile
Dim myStr As String

If myCell Is Nothing Then
Set myCell = Application.Caller
End If

If myCell.Comment Is Nothing Then
EvaluateComment = ""
Else
myStr = myCell.Comment.Text
myStr = Application.Substitute(myStr, ",", "+")
myStr = Application.Substitute(myStr, ";", "+")
EvaluateComment = myCell.Parent.Evaluate(myStr)
End If

End Function


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

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=evaluatecomment(A99)
(to look at the comment in cell 99).

or
=evaluatecomment()
to look at the comment in the cell holding the function.

Msantos wrote:

I need to create a list that is associated with a particular cell where I can
sum the values in the list and display the sum in the associated cell.

For example, if I right-click on cell A1, I would like to be able to
create/edit a list that can give me the sum of its values in cell A1.

It would be like creating a comment balloon where instead of having a text
area, I would have a small list and whatever calculation I do to that list
the result would be displayed back in the cell associated with it.

I have done extensive search on this and have found nothing like this, but
if anyone knows if I can do this and would like to help me, I would
appreciate it.

Thanks.

--

Dave Peterson


--

Dave Peterson

(I still wouldn't do this.)

Neither would I! <g

Biff

"Dave Peterson" wrote in message
...
Be careful with that function. Don't depend on any of the values
displayed
until your worksheet is recalculated.

(I still wouldn't do this.)

Msantos wrote:

Thanks Dave, it worked for me almost exactly like I imagined. What I
envisioned though was something like instead of the comment box a little
grid
or worksheet. But the solution you gave me works too. And thank you for
the
short course in macros, that saved me a lot of time researching on how I
would implement this solution.

Thanks a lot.

"Dave Peterson" wrote:

You could do it, but excel really isn't designed to work this way.
Changing
comments doesn't cause excel to recalculate--so you could change a
comment (or a
few comments) and the display in the cell could be out of date.

On top of that, you'd have to build all the parsing routines that you
wanted
into a user defined function.

For instance, if you programmed the UDF to accept commas 1,2,3 and you
used
1,2,3;4, you'd either get something you didn't want or your UDF would
have to be
"smart" enough to accept either.

And if you list was typed in as 1,2,3,see Msantos for more info,5,6,7
what would you do?

But if you want, you could try this UDF:

Option Explicit
Function EvaluateComment(Optional myCell As Range) As Variant
Application.Volatile
Dim myStr As String

If myCell Is Nothing Then
Set myCell = Application.Caller
End If

If myCell.Comment Is Nothing Then
EvaluateComment = ""
Else
myStr = myCell.Comment.Text
myStr = Application.Substitute(myStr, ",", "+")
myStr = Application.Substitute(myStr, ";", "+")
EvaluateComment = myCell.Parent.Evaluate(myStr)
End If

End Function


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

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=evaluatecomment(A99)
(to look at the comment in cell 99).

or
=evaluatecomment()
to look at the comment in the cell holding the function.

Msantos wrote:

I need to create a list that is associated with a particular cell
where I can
sum the values in the list and display the sum in the associated
cell.

For example, if I right-click on cell A1, I would like to be able to
create/edit a list that can give me the sum of its values in cell A1.

It would be like creating a comment balloon where instead of having a
text
area, I would have a small list and whatever calculation I do to that
list
the result would be displayed back in the cell associated with it.

I have done extensive search on this and have found nothing like
this, but
if anyone knows if I can do this and would like to help me, I would
appreciate it.

Thanks.

--

Dave Peterson


--

Dave Peterson

No, it works for me just fine. When I have to add a new number to the comment
bubble, I just press F9 and the worksheet is recalculated.

The reason I need this is just because I have limited space on the worksheet
I am working on (to maintain good esthetics) so when I use up the available
cells in a column and I need to have more values in that column, I don't have
to add more blank cells.

"Dave Peterson" wrote:

Be careful with that function. Don't depend on any of the values displayed
until your worksheet is recalculated.

(I still wouldn't do this.)

Msantos wrote:

Thanks Dave, it worked for me almost exactly like I imagined. What I
envisioned though was something like instead of the comment box a little grid
or worksheet. But the solution you gave me works too. And thank you for the
short course in macros, that saved me a lot of time researching on how I
would implement this solution.

Thanks a lot.

"Dave Peterson" wrote:

You could do it, but excel really isn't designed to work this way. Changing
comments doesn't cause excel to recalculate--so you could change a comment (or a
few comments) and the display in the cell could be out of date.

On top of that, you'd have to build all the parsing routines that you wanted
into a user defined function.

For instance, if you programmed the UDF to accept commas 1,2,3 and you used
1,2,3;4, you'd either get something you didn't want or your UDF would have to be
"smart" enough to accept either.

And if you list was typed in as 1,2,3,see Msantos for more info,5,6,7
what would you do?

But if you want, you could try this UDF:

Option Explicit
Function EvaluateComment(Optional myCell As Range) As Variant
Application.Volatile
Dim myStr As String

If myCell Is Nothing Then
Set myCell = Application.Caller
End If

If myCell.Comment Is Nothing Then
EvaluateComment = ""
Else
myStr = myCell.Comment.Text
myStr = Application.Substitute(myStr, ",", "+")
myStr = Application.Substitute(myStr, ";", "+")
EvaluateComment = myCell.Parent.Evaluate(myStr)
End If

End Function


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

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=evaluatecomment(A99)
(to look at the comment in cell 99).

or
=evaluatecomment()
to look at the comment in the cell holding the function.

Msantos wrote:

I need to create a list that is associated with a particular cell where I can
sum the values in the list and display the sum in the associated cell.

For example, if I right-click on cell A1, I would like to be able to
create/edit a list that can give me the sum of its values in cell A1.

It would be like creating a comment balloon where instead of having a text
area, I would have a small list and whatever calculation I do to that list
the result would be displayed back in the cell associated with it.

I have done extensive search on this and have found nothing like this, but
if anyone knows if I can do this and would like to help me, I would
appreciate it.

Thanks.

--

Dave Peterson


--

Dave Peterson

Maybe you could just use a formula directly in the cell:

=1+2+3+5+18

And add to that expression when you need to.

Msantos wrote:

No, it works for me just fine. When I have to add a new number to the comment
bubble, I just press F9 and the worksheet is recalculated.

The reason I need this is just because I have limited space on the worksheet
I am working on (to maintain good esthetics) so when I use up the available
cells in a column and I need to have more values in that column, I don't have
to add more blank cells.

"Dave Peterson" wrote:

Be careful with that function. Don't depend on any of the values displayed
until your worksheet is recalculated.

(I still wouldn't do this.)

Msantos wrote:

Thanks Dave, it worked for me almost exactly like I imagined. What I
envisioned though was something like instead of the comment box a little grid
or worksheet. But the solution you gave me works too. And thank you for the
short course in macros, that saved me a lot of time researching on how I
would implement this solution.

Thanks a lot.

"Dave Peterson" wrote:

You could do it, but excel really isn't designed to work this way. Changing
comments doesn't cause excel to recalculate--so you could change a comment (or a
few comments) and the display in the cell could be out of date.

On top of that, you'd have to build all the parsing routines that you wanted
into a user defined function.

For instance, if you programmed the UDF to accept commas 1,2,3 and you used
1,2,3;4, you'd either get something you didn't want or your UDF would have to be
"smart" enough to accept either.

And if you list was typed in as 1,2,3,see Msantos for more info,5,6,7
what would you do?

But if you want, you could try this UDF:

Option Explicit
Function EvaluateComment(Optional myCell As Range) As Variant
Application.Volatile
Dim myStr As String

If myCell Is Nothing Then
Set myCell = Application.Caller
End If

If myCell.Comment Is Nothing Then
EvaluateComment = ""
Else
myStr = myCell.Comment.Text
myStr = Application.Substitute(myStr, ",", "+")
myStr = Application.Substitute(myStr, ";", "+")
EvaluateComment = myCell.Parent.Evaluate(myStr)
End If

End Function


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

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=evaluatecomment(A99)
(to look at the comment in cell 99).

or
=evaluatecomment()
to look at the comment in the cell holding the function.

Msantos wrote:

I need to create a list that is associated with a particular cell where I can
sum the values in the list and display the sum in the associated cell.

For example, if I right-click on cell A1, I would like to be able to
create/edit a list that can give me the sum of its values in cell A1.

It would be like creating a comment balloon where instead of having a text
area, I would have a small list and whatever calculation I do to that list
the result would be displayed back in the cell associated with it.

I have done extensive search on this and have found nothing like this, but
if anyone knows if I can do this and would like to help me, I would
appreciate it.

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson