I am trying to reference a cell in multiple places. (Text only, no equations
other than =)

I have comments in that cell I would like to carry on to all the points that
are called out as equal to that cell.

I've named the cell. (TeamA1)
On other pages I say "= TeamA1"
This tells me PointAB = Air Force
My comments are things like
Fighting Falcons
Blue & White

Is there a way to do this. I don't want to copy and paste.


Thanks,
--
Brian

You'll need a macro.

Saved from a previous post:

If you want to use a user defined function to get the value and the comment --
but not the formatting, you could use:

Option Explicit
Function GetValueAndComment(FCell As Range) As Variant

Application.Volatile

Dim TCell As Range

Set TCell = Application.Caller

If TCell.Comment Is Nothing Then
'do nothing
Else
TCell.Comment.Delete
End If

If FCell.Comment Is Nothing Then
'do nothing
Else
TCell.AddComment Text:=FCell.Comment.Text
End If

If FCell.Value = "" Then
GetValueAndComment = ""
Else
GetValueAndComment = FCell.Value
End If

End Function

You'd use it like this:
=GetValueAndComment(A1)

The value in A1 would appear in the cell and the comment would get copied, too.

The application.volatile is there to update the comments if you change them.
(Changing the comment won't make the function run, but it'll catch up with the
next recalculation.)

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.
Into a test cell and type:
=GetValueAndComment(A1)

Brian wrote:

I am trying to reference a cell in multiple places. (Text only, no equations
other than =)

I have comments in that cell I would like to carry on to all the points that
are called out as equal to that cell.

I've named the cell. (TeamA1)
On other pages I say "= TeamA1"
This tells me PointAB = Air Force
My comments are things like
Fighting Falcons
Blue & White

Is there a way to do this. I don't want to copy and paste.

Thanks,
--
Brian

--

Dave Peterson

Dave,

Works like a champ!

Sorry I wasn't able to find it in the previous posts.

Thanks
--
Brian


"Dave Peterson" wrote:

You'll need a macro.

Saved from a previous post:

If you want to use a user defined function to get the value and the comment --
but not the formatting, you could use:

Option Explicit
Function GetValueAndComment(FCell As Range) As Variant

Application.Volatile

Dim TCell As Range

Set TCell = Application.Caller

If TCell.Comment Is Nothing Then
'do nothing
Else
TCell.Comment.Delete
End If

If FCell.Comment Is Nothing Then
'do nothing
Else
TCell.AddComment Text:=FCell.Comment.Text
End If

If FCell.Value = "" Then
GetValueAndComment = ""
Else
GetValueAndComment = FCell.Value
End If

End Function

You'd use it like this:
=GetValueAndComment(A1)

The value in A1 would appear in the cell and the comment would get copied, too.

The application.volatile is there to update the comments if you change them.
(Changing the comment won't make the function run, but it'll catch up with the
next recalculation.)

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.
Into a test cell and type:
=GetValueAndComment(A1)

Brian wrote:

I am trying to reference a cell in multiple places. (Text only, no equations
other than =)

I have comments in that cell I would like to carry on to all the points that
are called out as equal to that cell.

I've named the cell. (TeamA1)
On other pages I say "= TeamA1"
This tells me PointAB = Air Force
My comments are things like
Fighting Falcons
Blue & White

Is there a way to do this. I don't want to copy and paste.

Thanks,
--
Brian

--

Dave Peterson