I have a vlookup sheet,I want to add notes to the cells in the vlookup
table,but when i get the information from the vlookup table to another sheet
the notes will not go over with the information .CAN ANYONE HELP.
P.S I hope i have explained this correctly.
"notes" are maybe "Cell Comments"?
These cannot be transferred by formula.
If not Comments, post back with more detail about "notes"
Gord Dibben MS Excel MVP
On Mon, 14 Aug 2006 16:02:01 -0700, crusty53
<crusty53@discussions.microsoft.com> wrote:
>I have a vlookup sheet,I want to add notes to the cells in the vlookup
>table,but when i get the information from the vlookup table to another sheet
>the notes will not go over with the information .CAN ANYONE HELP.
> P.S I hope i have explained this correctly.
yes they are cell comments.If they cannot be transferred by formular is there
another method of transfering them?
"Gord Dibben" wrote:
> "notes" are maybe "Cell Comments"?
>
> These cannot be transferred by formula.
>
> If not Comments, post back with more detail about "notes"
>
>
> Gord Dibben MS Excel MVP
>
>
> On Mon, 14 Aug 2006 16:02:01 -0700, crusty53
> <crusty53@discussions.microsoft.com> wrote:
>
> >I have a vlookup sheet,I want to add notes to the cells in the vlookup
> >table,but when i get the information from the vlookup table to another sheet
> >the notes will not go over with the information .CAN ANYONE HELP.
> > P.S I hope i have explained this correctly.
>
>
Not by anything built into excel--but you could use a User Defined Function.
Do you want to try a little macro?
Option Explicit
Function VlookupComment(myVal As Variant, myTable As Range, _
myColumn As Long, myBoolean As Boolean) As Variant
Application.Volatile True
Dim res As Variant 'could be an error
Dim myLookupCell As Range
res = Application.Match(myVal, myTable.Columns(1), myBoolean)
If IsError(res) Then
VlookupComment = "Not Found"
Else
Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
VlookupComment = myLookupCell.Value
With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
If myLookupCell.Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myLookupCell.Comment.Text
End If
End With
End If
End Function
This kind of function could be one calculation behind. If the comment in the
table changes, then you'll want to force a recalculation before you believe the
results.
Application.volatile true
means that excel will recalculate each of these formulas each time excel
recalculates. You may notice a slowdown in your workbook.
If you remove this line, then the results in the cell will be ok, but the
comment may be wrong.
(The results in the cell should always be ok--it's the comment that's the
trouble.)
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:
=VlookupComment(a1, 'sheet 33'!a:e, 5, false)
It looks a lot like =vlookup().
crusty53 wrote:
>
> yes they are cell comments.If they cannot be transferred by formular is there
> another method of transfering them?
>
> "Gord Dibben" wrote:
>
> > "notes" are maybe "Cell Comments"?
> >
> > These cannot be transferred by formula.
> >
> > If not Comments, post back with more detail about "notes"
> >
> >
> > Gord Dibben MS Excel MVP
> >
> >
> > On Mon, 14 Aug 2006 16:02:01 -0700, crusty53
> > <crusty53@discussions.microsoft.com> wrote:
> >
> > >I have a vlookup sheet,I want to add notes to the cells in the vlookup
> > >table,but when i get the information from the vlookup table to another sheet
> > >the notes will not go over with the information .CAN ANYONE HELP.
> > > P.S I hope i have explained this correctly.
> >
> >
--
Dave Peterson
Thanks Dave,It was too much for me ,but i will try it and thanks for the
site,I now have more reading but am very grateful for this info.
"Dave Peterson" wrote:
> Not by anything built into excel--but you could use a User Defined Function.
>
> Do you want to try a little macro?
>
> Option Explicit
> Function VlookupComment(myVal As Variant, myTable As Range, _
> myColumn As Long, myBoolean As Boolean) As Variant
>
> Application.Volatile True
>
> Dim res As Variant 'could be an error
> Dim myLookupCell As Range
>
> res = Application.Match(myVal, myTable.Columns(1), myBoolean)
> If IsError(res) Then
> VlookupComment = "Not Found"
> Else
> Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
> VlookupComment = myLookupCell.Value
> With Application.Caller
> If .Comment Is Nothing Then
> 'do nothing
> Else
> .Comment.Delete
> End If
>
> If myLookupCell.Comment Is Nothing Then
> 'no comment, do nothing
> Else
> .AddComment Text:=myLookupCell.Comment.Text
> End If
> End With
> End If
>
> End Function
>
> This kind of function could be one calculation behind. If the comment in the
> table changes, then you'll want to force a recalculation before you believe the
> results.
>
> Application.volatile true
> means that excel will recalculate each of these formulas each time excel
> recalculates. You may notice a slowdown in your workbook.
>
> If you remove this line, then the results in the cell will be ok, but the
> comment may be wrong.
>
> (The results in the cell should always be ok--it's the comment that's the
> trouble.)
>
> 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:
> =VlookupComment(a1, 'sheet 33'!a:e, 5, false)
>
> It looks a lot like =vlookup().
>
>
>
>
>
> crusty53 wrote:
> >
> > yes they are cell comments.If they cannot be transferred by formular is there
> > another method of transfering them?
> >
> > "Gord Dibben" wrote:
> >
> > > "notes" are maybe "Cell Comments"?
> > >
> > > These cannot be transferred by formula.
> > >
> > > If not Comments, post back with more detail about "notes"
> > >
> > >
> > > Gord Dibben MS Excel MVP
> > >
> > >
> > > On Mon, 14 Aug 2006 16:02:01 -0700, crusty53
> > > <crusty53@discussions.microsoft.com> wrote:
> > >
> > > >I have a vlookup sheet,I want to add notes to the cells in the vlookup
> > > >table,but when i get the information from the vlookup table to another sheet
> > > >the notes will not go over with the information .CAN ANYONE HELP.
> > > > P.S I hope i have explained this correctly.
> > >
> > >
>
> --
>
> Dave Peterson
>
how about a double vlookup?Put your notes re the cell in another column and
use another vlookup to return the appropriate note\ie say your original
lookup returns col3 you could say return col4 for your note or say col 1 for
info and col10 for note etc etc....
--
paul
paul.shepherd@nospamparadise.net.nz
remove nospam for email addy!
"crusty53" wrote:
> Thanks Dave,It was too much for me ,but i will try it and thanks for the
> site,I now have more reading but am very grateful for this info.
>
> "Dave Peterson" wrote:
>
> > Not by anything built into excel--but you could use a User Defined Function.
> >
> > Do you want to try a little macro?
> >
> > Option Explicit
> > Function VlookupComment(myVal As Variant, myTable As Range, _
> > myColumn As Long, myBoolean As Boolean) As Variant
> >
> > Application.Volatile True
> >
> > Dim res As Variant 'could be an error
> > Dim myLookupCell As Range
> >
> > res = Application.Match(myVal, myTable.Columns(1), myBoolean)
> > If IsError(res) Then
> > VlookupComment = "Not Found"
> > Else
> > Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
> > VlookupComment = myLookupCell.Value
> > With Application.Caller
> > If .Comment Is Nothing Then
> > 'do nothing
> > Else
> > .Comment.Delete
> > End If
> >
> > If myLookupCell.Comment Is Nothing Then
> > 'no comment, do nothing
> > Else
> > .AddComment Text:=myLookupCell.Comment.Text
> > End If
> > End With
> > End If
> >
> > End Function
> >
> > This kind of function could be one calculation behind. If the comment in the
> > table changes, then you'll want to force a recalculation before you believe the
> > results.
> >
> > Application.volatile true
> > means that excel will recalculate each of these formulas each time excel
> > recalculates. You may notice a slowdown in your workbook.
> >
> > If you remove this line, then the results in the cell will be ok, but the
> > comment may be wrong.
> >
> > (The results in the cell should always be ok--it's the comment that's the
> > trouble.)
> >
> > 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:
> > =VlookupComment(a1, 'sheet 33'!a:e, 5, false)
> >
> > It looks a lot like =vlookup().
> >
> >
> >
> >
> >
> > crusty53 wrote:
> > >
> > > yes they are cell comments.If they cannot be transferred by formular is there
> > > another method of transfering them?
> > >
> > > "Gord Dibben" wrote:
> > >
> > > > "notes" are maybe "Cell Comments"?
> > > >
> > > > These cannot be transferred by formula.
> > > >
> > > > If not Comments, post back with more detail about "notes"
> > > >
> > > >
> > > > Gord Dibben MS Excel MVP
> > > >
> > > >
> > > > On Mon, 14 Aug 2006 16:02:01 -0700, crusty53
> > > > <crusty53@discussions.microsoft.com> wrote:
> > > >
> > > > >I have a vlookup sheet,I want to add notes to the cells in the vlookup
> > > > >table,but when i get the information from the vlookup table to another sheet
> > > > >the notes will not go over with the information .CAN ANYONE HELP.
> > > > > P.S I hope i have explained this correctly.
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >
Paul,Great Idea THANKS!
"paul" wrote:
> how about a double vlookup?Put your notes re the cell in another column and
> use another vlookup to return the appropriate note\ie say your original
> lookup returns col3 you could say return col4 for your note or say col 1 for
> info and col10 for note etc etc....
> --
> paul
> paul.shepherd@nospamparadise.net.nz
> remove nospam for email addy!
>
>
>
> "crusty53" wrote:
>
> > Thanks Dave,It was too much for me ,but i will try it and thanks for the
> > site,I now have more reading but am very grateful for this info.
> >
> > "Dave Peterson" wrote:
> >
> > > Not by anything built into excel--but you could use a User Defined Function.
> > >
> > > Do you want to try a little macro?
> > >
> > > Option Explicit
> > > Function VlookupComment(myVal As Variant, myTable As Range, _
> > > myColumn As Long, myBoolean As Boolean) As Variant
> > >
> > > Application.Volatile True
> > >
> > > Dim res As Variant 'could be an error
> > > Dim myLookupCell As Range
> > >
> > > res = Application.Match(myVal, myTable.Columns(1), myBoolean)
> > > If IsError(res) Then
> > > VlookupComment = "Not Found"
> > > Else
> > > Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
> > > VlookupComment = myLookupCell.Value
> > > With Application.Caller
> > > If .Comment Is Nothing Then
> > > 'do nothing
> > > Else
> > > .Comment.Delete
> > > End If
> > >
> > > If myLookupCell.Comment Is Nothing Then
> > > 'no comment, do nothing
> > > Else
> > > .AddComment Text:=myLookupCell.Comment.Text
> > > End If
> > > End With
> > > End If
> > >
> > > End Function
> > >
> > > This kind of function could be one calculation behind. If the comment in the
> > > table changes, then you'll want to force a recalculation before you believe the
> > > results.
> > >
> > > Application.volatile true
> > > means that excel will recalculate each of these formulas each time excel
> > > recalculates. You may notice a slowdown in your workbook.
> > >
> > > If you remove this line, then the results in the cell will be ok, but the
> > > comment may be wrong.
> > >
> > > (The results in the cell should always be ok--it's the comment that's the
> > > trouble.)
> > >
> > > 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:
> > > =VlookupComment(a1, 'sheet 33'!a:e, 5, false)
> > >
> > > It looks a lot like =vlookup().
> > >
> > >
> > >
> > >
> > >
> > > crusty53 wrote:
> > > >
> > > > yes they are cell comments.If they cannot be transferred by formular is there
> > > > another method of transfering them?
> > > >
> > > > "Gord Dibben" wrote:
> > > >
> > > > > "notes" are maybe "Cell Comments"?
> > > > >
> > > > > These cannot be transferred by formula.
> > > > >
> > > > > If not Comments, post back with more detail about "notes"
> > > > >
> > > > >
> > > > > Gord Dibben MS Excel MVP
> > > > >
> > > > >
> > > > > On Mon, 14 Aug 2006 16:02:01 -0700, crusty53
> > > > > <crusty53@discussions.microsoft.com> wrote:
> > > > >
> > > > > >I have a vlookup sheet,I want to add notes to the cells in the vlookup
> > > > > >table,but when i get the information from the vlookup table to another sheet
> > > > > >the notes will not go over with the information .CAN ANYONE HELP.
> > > > > > P.S I hope i have explained this correctly.
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks