Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfer Comment to VLOOKUP Cell on Search
I have a spreadsheet that displays a cell's contents in column E based on a
numberical identifier in column B. These cells targeted by the VLOOKUP cell are located in a seperate part of the same worksheet. I would like to transfer the comment of the target cell along with the cell data when the VLOOKUP cell populates. Here is the basic VLOOKUP formula I am using: =VLOOKUP($B7,$Y$22:$AT$503,2) Thanks for any help straightening this out, in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfer Comment to VLOOKUP Cell on Search
Cell Comments cannot be returned using formulas alone.
VBA is required. See Debra Dalgleish's site for various macros for dealing with Comments. http://www.contextures.on.ca/xlcomments03.html Gord Dibben MS Excel MVP On Thu, 22 May 2008 11:14:00 -0700, CCorreia wrote: I have a spreadsheet that displays a cell's contents in column E based on a numberical identifier in column B. These cells targeted by the VLOOKUP cell are located in a seperate part of the same worksheet. I would like to transfer the comment of the target cell along with the cell data when the VLOOKUP cell populates. Here is the basic VLOOKUP formula I am using: =VLOOKUP($B7,$Y$22:$AT$503,2) Thanks for any help straightening this out, in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfer Comment to VLOOKUP Cell on Search
Thanks Gord,
I actually went over Debra's how-to previously and that provided me with the solution for displaying graphics from a server side folder in the spreadsheet. I forgot to mention that the comment boxes do not have text, they only display the pictures linked to the outside folder. I assume I will need to use the following code from Debra's tutorial as the basis for my VBA, however I do not know how to retrieve the picture file path from the original comment and insert it in the comment that is created using this code, instead of the date and time. Sub CommentDateTimeAdd() 'adds comment with date and time, ' positions cursor at end of comment text Dim strDate As String Dim cmt As Comment strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub This would be easier to do in Access and I am familiar with how to do it there, however this is not my spreadsheet so I can't just transfer all the data over and do it there. "Gord Dibben" wrote: Cell Comments cannot be returned using formulas alone. VBA is required. See Debra Dalgleish's site for various macros for dealing with Comments. http://www.contextures.on.ca/xlcomments03.html Gord Dibben MS Excel MVP On Thu, 22 May 2008 11:14:00 -0700, CCorreia wrote: I have a spreadsheet that displays a cell's contents in column E based on a numberical identifier in column B. These cells targeted by the VLOOKUP cell are located in a seperate part of the same worksheet. I would like to transfer the comment of the target cell along with the cell data when the VLOOKUP cell populates. Here is the basic VLOOKUP formula I am using: =VLOOKUP($B7,$Y$22:$AT$503,2) Thanks for any help straightening this out, in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfer Comment to VLOOKUP Cell on Search
You may be able to cobble something together with this macro to insert a picture
in a Comment. http://www.contextures.on.ca/xlcomments03.html#Picture I haven't figured out how to get the path and name from the source Comment however. Gord On Thu, 22 May 2008 14:02:01 -0700, CCorreia wrote: Thanks Gord, I actually went over Debra's how-to previously and that provided me with the solution for displaying graphics from a server side folder in the spreadsheet. I forgot to mention that the comment boxes do not have text, they only display the pictures linked to the outside folder. I assume I will need to use the following code from Debra's tutorial as the basis for my VBA, however I do not know how to retrieve the picture file path from the original comment and insert it in the comment that is created using this code, instead of the date and time. Sub CommentDateTimeAdd() 'adds comment with date and time, ' positions cursor at end of comment text Dim strDate As String Dim cmt As Comment strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub This would be easier to do in Access and I am familiar with how to do it there, however this is not my spreadsheet so I can't just transfer all the data over and do it there. "Gord Dibben" wrote: Cell Comments cannot be returned using formulas alone. VBA is required. See Debra Dalgleish's site for various macros for dealing with Comments. http://www.contextures.on.ca/xlcomments03.html Gord Dibben MS Excel MVP On Thu, 22 May 2008 11:14:00 -0700, CCorreia wrote: I have a spreadsheet that displays a cell's contents in column E based on a numberical identifier in column B. These cells targeted by the VLOOKUP cell are located in a seperate part of the same worksheet. I would like to transfer the comment of the target cell along with the cell data when the VLOOKUP cell populates. Here is the basic VLOOKUP formula I am using: =VLOOKUP($B7,$Y$22:$AT$503,2) Thanks for any help straightening this out, in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to show original comment in cell when using Vlookup in Excel? | Excel Worksheet Functions | |||
copy comment content to cell content as data not as comment | Excel Discussion (Misc queries) | |||
How to get search and transfer | Excel Discussion (Misc queries) | |||
Using Vlookup in a string search of a cell | Excel Worksheet Functions | |||
Vlookup while pulling a cell color and comment | Excel Worksheet Functions |