Having comments copied when using formulas

[Guest]

Hi there. I am using multiple worksheets for work and each employee has their
own sheets where they enter their daily numbers. The employee sheets are
linked to a master and updated automatically. However, if they put a comment
in the cell for a particular day, it is not copied over with the formula. I
know that it is possible if you use the copy and paste special function but I
am using the = function to copy from one worksheet to another (daily
sheet.xls]Aug 07 - Sept 07'!$B$4). Since I am only using the = function, the
comment is not forwarded? Any way I can change that? Please help… I am
totally frustrated. Thanks a million in advance.

 

[Dave Peterson]

First, I think I'd ask them to use an adjacent column for their comments. It
would make things lots easier for you and them (they could filter by that
additional column to see various comments).

But if you want...

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)

Hi there. I am using multiple worksheets for work and each employee has their
own sheets where they enter their daily numbers. The employee sheets are
linked to a master and updated automatically. However, if they put a comment
in the cell for a particular day, it is not copied over with the formula. I
know that it is possible if you use the copy and paste special function but I
am using the = function to copy from one worksheet to another (daily
sheet.xls]Aug 07 - Sept 07'!$B$4). Since I am only using the = function, the
comment is not forwarded? Any way I can change that? Please help… I am
totally frustrated. Thanks a million in advance.

 

[Guest]

Hi Dave - thanks for the great advice. It worked great except when I close
the other spreadsheet for the employee, I get a #value error on the master.
If I open the employee's sheet back up, it shows the values. Is there any way
to be sure the values in the master workbook stays even after the separate
sheets are closed? thanks a bunch

First, I think I'd ask them to use an adjacent column for their comments. It
would make things lots easier for you and them (they could filter by that
additional column to see various comments).

But if you want...

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)

Hi there. I am using multiple worksheets for work and each employee has their
own sheets where they enter their daily numbers. The employee sheets are
linked to a master and updated automatically. However, if they put a comment
in the cell for a particular day, it is not copied over with the formula. I
know that it is possible if you use the copy and paste special function but I
am using the = function to copy from one worksheet to another (daily
sheet.xls]Aug 07 - Sept 07'!$B$4). Since I am only using the = function, the
comment is not forwarded? Any way I can change that? Please help… I am
totally frustrated. Thanks a million in advance.

 

[Dave Peterson]

I read your multiple sheets as multiple sheets within the same workbook.

About the only thing you could do is to convert the formulas to values before
you close the other workbooks.

Hi Dave - thanks for the great advice. It worked great except when I close
the other spreadsheet for the employee, I get a #value error on the master.
If I open the employee's sheet back up, it shows the values. Is there any way
to be sure the values in the master workbook stays even after the separate
sheets are closed? thanks a bunch

First, I think I'd ask them to use an adjacent column for their comments. It
would make things lots easier for you and them (they could filter by that
additional column to see various comments).

But if you want...

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)

Hi there. I am using multiple worksheets for work and each employee has their
own sheets where they enter their daily numbers. The employee sheets are
linked to a master and updated automatically. However, if they put a comment
in the cell for a particular day, it is not copied over with the formula. I
know that it is possible if you use the copy and paste special function but I
am using the = function to copy from one worksheet to another (daily
sheet.xls]Aug 07 - Sept 07'!$B$4). Since I am only using the = function, the
comment is not forwarded? Any way I can change that? Please help… I am
totally frustrated. Thanks a million in advance.

 

[Guest]

thank you for your quick response. I will adjust accordingly.

I read your multiple sheets as multiple sheets within the same workbook.

About the only thing you could do is to convert the formulas to values before
you close the other workbooks.

Hi Dave - thanks for the great advice. It worked great except when I close
the other spreadsheet for the employee, I get a #value error on the master.
If I open the employee's sheet back up, it shows the values. Is there any way
to be sure the values in the master workbook stays even after the separate
sheets are closed? thanks a bunch

First, I think I'd ask them to use an adjacent column for their comments. It
would make things lots easier for you and them (they could filter by that
additional column to see various comments).

But if you want...

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)

Corkey wrote:

Hi there. I am using multiple worksheets for work and each employee has their
own sheets where they enter their daily numbers. The employee sheets are
linked to a master and updated automatically. However, if they put a comment
in the cell for a particular day, it is not copied over with the formula. I
know that it is possible if you use the copy and paste special function but I
am using the = function to copy from one worksheet to another (daily
sheet.xls]Aug 07 - Sept 07'!$B$4). Since I am only using the = function, the
comment is not forwarded? Any way I can change that? Please help… I am
totally frustrated. Thanks a million in advance.