Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to pull cells from other worksheets including color/comments?
Is there a way to pull ( not copy and paste) a cell from another worksheet
along with the cell comment and color? I am using an equation (i.e. =E25) in order to pull the cell from one worksheet to the other, but it only takes the value and not the color/comment. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to pull cells from other worksheets including color/comments?
You could use a macro to the copy and pasting--but formulas can pretty much only
return values to the cell that holds them. Actually, they can return the comment--but they can't change other formatting. 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) wendy wrote: Is there a way to pull ( not copy and paste) a cell from another worksheet along with the cell comment and color? I am using an equation (i.e. =E25) in order to pull the cell from one worksheet to the other, but it only takes the value and not the color/comment. Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull info from other worksheets | Excel Discussion (Misc queries) | |||
how to combine including blank cells | New Users to Excel | |||
Referencing cells in different worksheets | Excel Worksheet Functions | |||
Calculating without including Hidden Cells | Excel Discussion (Misc queries) | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) |