Comments in excel 2003

[Klemen25]

Hello!

I get the data from data warehouse.
I than input the data in Excel.

I have a list of accounts in column A.
In column B, C, D... are months from January to December, and then for
each month specific amounts for certain account.
I have to input comments for certain specific amounts (if they are
much higher or lower as to provide explanation for these exceptions).

So I do this for each month- but when I get data for new month, also
new accounts appear.
If they would not- I could just import new data (for all months) and
then copy- paste special- comments, and all comments would be in the
right cell.
But as the new accounts appear, I have problems inputting the comments
from the previous months.
Is there any way to solve this (perhaps if it is possible to tell the
comment- you must always be on March for account 123449)?
Or how would you do this?

Thank you!

 

[Dave Peterson]

First, I would think that using a separate cell to hold your comments would make
your live much easier. You could just use =vlookup() or =index(match()) to
return the text.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

But...

One of the "features/bugs" of excel is that you can return comments using a user
defined function.

Actually, you could use a UDF to retrieve the value and comment:

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

With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
end with

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 myLookupCell.Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myLookupCell.Comment.Text
End If
End With
End If

End Function

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, 'sheet2'!a:e, 5, false)