Comments with Lookup Formulas??

Is there anyway to insert a formula into a comment box??

I have one sheet with three columns which contain defect code numbers.

I have another worksheet tab in the same excel file which contains a list of
defect code numbers and defect descriptions.

I want to display the defect description in a yellow comment box when I move
the cursor over the cell which contains the defect code number.

I looked at Excel's comment feature, but it appears that these comment boxes
can contain only fixed text.  Is there any way to incorporate a lookup
function into the comment box so that it can look up the corresponding list
in the other worksheet tab??

Thanks for any ideas,
John


0
1/20/2006 3:38:27 AM
excel 39879 articles. 2 followers. Follow

1 Replies
386 Views

Similar Articles

[PageSpeed] 17

You could write some code that could populate the comment with the results of an
=vlookup() function.

But if you change that part number, then the comment will be wrong until that
macro runs again.

This sounds like a nice idea until you start getting down to how many things can
go wrong (my opinion only).

If I were you, I'd just use an adjacent cell to contain that description.

But if you want a macro to do that:

Option Explicit
Sub AddCommentsToSelection()

    Dim myRng As Range
    Dim myCell As Range
    Dim myLookupRng As Range
    Dim lArea As Double
    Dim res As Variant
    
    Set myRng = Selection
    Set myLookupRng = Worksheets("sheet2").Range("a:b")
    
    For Each myCell In myRng.Cells
        If myCell.Comment Is Nothing Then
            'do nothing
        Else
            myCell.Comment.Delete
        End If
        
        res = Application.VLookup(myCell.Value, myLookupRng, 2, False)
        If IsError(res) Then
            'don't put anything there?
        Else
            myCell.AddComment Text:=res
            With myCell.Comment
                .Shape.TextFrame.AutoSize = True
                If .Shape.Width > 300 Then
                    lArea = .Shape.Width * .Shape.Height
                    .Shape.Width = 200
                    .Shape.Height = (lArea / 200) * 1.3
                End If
            End With
        End If
    Next myCell
            
End Sub

Just select the range to add the comments and run the macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

and I borrowed the resizing comment code from Debra Dalgleish's site:
http://www.contextures.com/xlcomments03.html#Resize

John Gilchrist wrote:
> 
> Is there anyway to insert a formula into a comment box??
> 
> I have one sheet with three columns which contain defect code numbers.
> 
> I have another worksheet tab in the same excel file which contains a list of
> defect code numbers and defect descriptions.
> 
> I want to display the defect description in a yellow comment box when I move
> the cursor over the cell which contains the defect code number.
> 
> I looked at Excel's comment feature, but it appears that these comment boxes
> can contain only fixed text.  Is there any way to incorporate a lookup
> function into the comment box so that it can look up the corresponding list
> in the other worksheet tab??
> 
> Thanks for any ideas,
> John

-- 

Dave Peterson
0
petersod (12004)
1/20/2006 2:48:37 PM
Reply:

Similar Artilces:

Lookup table with click-and-select?
Can someone point me in the right direction? I have several thermos bottles of various sizes that I use for tea. I have several varieties of tea that require different brewing parameters. I'd like to create a worksheet that will generate "brew slips" -- little slips of paper that show how much tea to use for each thermos. The left side of the sheet will be the brew sheet. It will have columns for ounces, cups, milliliters, grams of tea, etc. Something like this: Thermos Cups Oz mL g/pot A 2.0 16 473 ?? B 3.0 24 710 ?? C...

Path to Lookup from another document
Hi, first post ever ever! Can you help? When using worksheet "A" I want an equation to look at one cel reference in worksheet "B" searching for same reference and pul information through. Worksheet "B" may not be open when lookup needs t operate -- Boethius ----------------------------------------------------------------------- Boethius1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3049 View this thread: http://www.excelforum.com/showthread.php?threadid=50150 Open workbook B. Goto workbook A. In your cell, type = then Ctrl-F...

formula/function to change a number formatted as text to being an actual number
Is it possible to change a text string that represents a number i.e. "18" to the it's numeric equivalent i.e the number 18, as part of a function or formula? Joe Use VALUE(A2) to get a number from text. Or you could just use =A2*1 Andy. "Joe" <anonymous@discussions.microsoft.com> wrote in message news:CA77566A-D591-4135-8749-C8122B160969@microsoft.com... > Is it possible to change a text string that represents a number i.e. "18" to the it's numeric equivalent i.e the number 18, as part of a function or formula? Joe, If you need to do this t...

Lookup cell above next value >0
I have a table with two rows, 33 columns long. Top row has Years (2004 to 2036) in it and the row below is intended for the inputing of values in percentage form. Some of the cells in bottom row may be left blank or with 0 value. Elsewhere on my spreadsheet I want to create formulas that will return the top row value (year) for each year where the value in bottom row is greater than 0. For example, if A1 = 2004 A2 = 2005 A3 = 2006 A4 = 2007 B1 = 0 % B2 = 5 % B3 = blank B4 = 20% What formula in cell C1 will return value "2005" and in cell C2 the next year ...

Formula bar drop-down
Some cells have extended info or long formulae. If the cursor is on such a cell the formula edit area drops down to accommodate the contents. If I need to access the columns row (say, to tweak the width) or read the topmost data rows this is an inconvenience. Is there a means of restricting the edit bar to one line unless the cursor is actually in it. Maybe a keyboard toggle? rs --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.772 / Virus Database: 519 - Release Date: 10/1/2004 rs Several work-arounds. See google search ...

Formula?
Is there a formula to allow me to calculate a deduction=20 from a sum up to a certain limit then change the deduction=20 to another amount once this limit has been reached? Example would be To deduct 5% of sum up to a max of =A3170 then 4% thereafter Thanks IF(sum(range)>170,total*.05,total*.04) "Malc" <malcolm.shaxted@virgin.net> wrote in message news:675701c42eaa$9a6ab920$a401280a@phx.gbl... Is there a formula to allow me to calculate a deduction from a sum up to a certain limit then change the deduction to another amount once this limit has been reached? Example would...

Possible to See Comments on Charts Derived from "Commented on" Cel
Is it possible to have a data cell's comment (which shows up on mouse-over of the red triangle in the upper right of the cell) also show up on mouse-over of the data point on a chart that takes a data point or points from a "commented on" cell(s)? If so, how can you do it? I would like to be able to see comments when I mouse-over a data point on a chart if the data point was taken from a cell with a comment. This isn't a native Excel capability, but it's an interesting idea, and one that could be accomplished using VBA. - Jon ------- Jon Peltier, Micros...

Formula #9
is there a way to have cells with numbers and letters and have the system total them in another cell? TOTAL UFL Example 3UFL 4UFL 7 In your specific case, try: =SUMPRODUCT(1*(LEFT(A1:B1,SEARCH("U",A1:B1)-1))) HTH Jason Atlanta, GA >-----Original Message----- >is there a way to have cells with numbers and letters and >have the system total them in another cell? > > TOTAL UFL >Example 3UFL 4UFL 7 >. > ...

Record Item Comments
When you select the Record Item Comments option in the configuration for RMS SO, in what table and field in the database does this imformation get stored? Also, RMS warns that this option will cause the database to grow. How significant is this growth? Jason TransactionEntry.Comment The significance would depend on how often you use the comments and how much text you enter. It's only a 255 character field, so 255 Bytes per comment is the max. You could potentially get some additional wasted space because of the way SQL Server allocates space for a record, but it should be p...

vlookup returns actual formula
I am attempting to look up values in spreadsheet a from spreadsheet b. When I hit enter it does not calculate and displays the formula itself in the cell, i.e. (=vlookup(a2,sheetB,3,false). I've used vlookup from spreadsheet B before and it worked. Any ideas? Format the cell as General *and* re-enter the formula (F2, ENTER) -- Kind regards, Niek Otten Microsoft MVP - Excel "chetan s" <ccshah@gmail.com> wrote in message news:5da5a776-d595-49fc-9303-cc59a0aa6eab@t1g2000pra.googlegroups.com... |I am attempting to look up values in spreadsheet a from spreadsheet b. | When ...

Formula to identify a keyword in all cells of a column
I have a spreadsheet with about 5,000 rows andjust one column - column A. I would like to record a formula in column b where for every cell in column A that contains the word "web" somewhere in the description (the description could have up to 10 words), it will place the number "1" in column B. Can this be done? I assume that it is some sort of if/then statement, but when I try =IF(A2="*web*",1,0) with the wild card "*" it does not seem to work. Any ideas? Thank you, Paul =isnumber(find("web",a1))*1 "HTC" <HTC@discu...

Data for Chart with Blank Formula Cells
I am trying to chart a worksheet that contains many formulas. When th result of the formula is zero, the cell is instructed to leave the cel blank (which is important to the information being tracked). However when I chart the data, it charts a ZERO for the cell. I tried the interpolate feature, but because the cell has a formula i it, it is not being read as "blank", even though the value is blank. Help! If week one is 2, week three is "blank" (remember, cell actuall has a formula that is returning the blank), and week four is 6, I do get my chart to interpolate from...

Access a cell's Formula, rather than Value
Hello all: I would like to modify this formula so that I use the formula of the cell specified by x, rather than the value stored in the cell. =CHOOSE(x, Sheet1!A1, Sheet2!A1) Thanks, John Not possible, you would need VBA for this. I can't see how by using CHOOSE you would be able to do this even if it was possible to get the formula to replace x since x should be an index number either 1 or 2 (in this case) if you have 2 values? Or have I totally misunderstood? -- Regards, Peo Sjoblom "John Broderick" <none@none.com> wrote in message news:e1RJAHYmHHA.455...

Comments partially hidden due to "frozen" columns
I am using Excel 2007. I have "frozen" columns A thru C so they are always visible when a user scrolls horizontally. I have also added Comments to several cells in column B. When a user hovers the mouse cursor over a cell containing a comment in column B, the comment always appears to the right beginning in column C (and extends sometimes into columns D & E). However, if a user has scrolled horizontally to column Z, for example, and then hovers the mouse cursor over a cell containing a comment in column B, the comment becomes partially hidden. Is this behavio...

XL2007: Formulas not extending to next row?
All of a sudden, XL2007 stopped extending formulas and values down into the next row. I enter data in one column, and formulas and the values for calculations used to automatically fill into the cells in the rest of the row. Suddenly they've stopped! I have the Extend Data Range option turned on. This seemed to happen after I had to delete a row. Any suggestions? Ed ...

Filter by Formula
Is there a way to filter by a formula? For instance, I want to filte all data with dates < today(). I tried entering in 'less than' an 'today()' but it didn't work for me. Thanks -- theillknigh ----------------------------------------------------------------------- theillknight's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1099 View this thread: http://www.excelforum.com/showthread.php?threadid=31440 Hi have you tried 'Data - Filter - Advanced Filter'? "theillknight" wrote: > > Is there a way to filter by ...

XL2002 Clipboard doesn't paste formulae
Copy&Paste formulae while the marquee flashes is clear and not a problem. I think I remember correctly that XL2000's clipboard toolbar was puzzling in that it allowed you to paste cell formulae once, but only values when repeated. I never understood why but I accepted it because I was young then. XL2002's clipboard pane seems only ever to paste values (I see there is a suggestion to MS about changing this) but is there a setting to allow at least the first pasting to include formulae? >>is there a setting to allow at least the first pasting to include >>formula...

Multiple text-number in cell divider formula?
Dear, Is it possible in Excel to divide multiple text and numbers in the separate columns. Text in one and two numbers in separate For example: Jane & Joe 345 329 (in 3 columns) X-files 32 344 Thank you very much for help and support! Kindly, ekonomija On Tue, 18 May 2010 13:37:01 -0700, ekonomija <ekonomija@discussions.microsoft.com> wrote: >Dear, > >Is it possible in Excel to divide multiple text and numbers in the separate >columns. > >Text in one and two numbers in separate >For example: > >Jane & Joe 345 3...

Conditional Formatting Formula Help
Hi, I am trying to write a formula to be placed in the conditional formatting to color code certain cells but am having some trouble coming up with the formula. Basically I have about 240 rows of data, the columns are by date. What I want is for whenever a daily reading is above the 20 day average of todays data and the 19 previous cells of data it will be green; and when below its 20 day average, red. The formula will start in column W, row 2. So the formula should be something like (in laymans language) "IF W2 > AVERAGE(D2:W2)" FOR GREEN and "IF W2 < AVERAGE(D2:W2...

Change operater in Excel formula
Hello, depending on a certain circumstances, a formula must change from "X = Y" to "X <> Y". Can this be done (e.g. using "if", or otherwise) ? Thank you in advance. Merry Christmas, H.G. Lamy You didn't give us a lot to work with, but I think you are looking for something like this... =IF(<<your condition>>,<<formula with x=y>>,<<formula with x<>y>>) -- Rick (MVP - Excel) "H.G. Lamy" <Enterplan@web.de> wrote in message news:upAoGxlhKHA.6136@TK2MSFTNGP04.phx.gbl.....

In excel the formula bar behaves differently on different machines
Here is a problem which occurs on one machine and does not appear in another machine, though the machines are identical in all aspects, the make memory, software etc.. and Office 2003 is installed on both the machines. Question - In excel in a cell if there is a formula with references using a function (example sum or average) to four different cells in four different workbooks, then the entire path of the workbook in which the cell is pointing to is displayed in the formula bar, if the referred workbooks are not opened. Now if the refered workbooks are opened, pointing to that cell h...

Help with PMT formula
I'm new at this and need to know what this PMT function will do? PMT(D412,E4,C4) See: http://msdn2.microsoft.com/en-us/library/aa851078.aspx -- Gary''s Student - gsnu200773 "Janet H" wrote: > I'm new at this and need to know what this PMT function will do? > PMT(D412,E4,C4) If you click the fx button to the left of the address bar, it gives lots of helpful information about your formula. This formula is used to calculate interest. "Janet H" wrote: > I'm new at this and need to know what this PMT function will do? > PMT(D412,E4,C4)...

Pivot tabel formulas
I am trying to create a formula that calculates a Quarter change from year to year where the Quarters are in the columns - so i would need to add the formula to a new row but cant seem to get it to calculate the different years - is there an IF function that can be used? Data Year Qtr 1 Qtr 2 Qtr 3 Qtr 4 2009 $111,814,953 $176,858,096 $81,572,938 $83,166,330 2010 $116,999,566 $195,651,553 $102,976,126 $46,935,333 (formula to calc differences in Qtr's?????) Grand Total$228,814,519 $372,509,649 $184,549,063 $130,101,663 EXCEL 2007 Hi Carl. I have just put up a ...

Fill-colour a cell dependent on formula
Formula in C1 - =IF(A1<B1,"Green",IF(AQ<B1/0.95,"Red","Amber")) How can I fill-colour the cell to change to the colour (Green, Amber or Red) rather than show the word of the colour. Many thanks in advance. Take a look at Conditional Formatting in XL Help. In article <68E046F9-A489-4C1A-BCC1-49692478DAE1@microsoft.com>, Kim Duffy <KimDuffy@discussions.microsoft.com> wrote: > How can I fill-colour the cell to change to the colour (Green, Amber or Red) > rather than show the word of the colour. ...

Need a excel formula which can see the second moving
Dear sir, I set an excel formula =NOW() which show the time. However, the time will only refresh when I hit the formula. My question is that is there possible to set a excel formula which we can see the second (ss) moving? Many thanks, Wilchong -- Message posted via http://www.officekb.com You can use an OnTime event macro to perform periodic re-calculations. See: http://www.cpearson.com/excel/OnTime.aspx -- Gary''s Student - gsnu2007L "wilchong via OfficeKB.com" wrote: > Dear sir, > I set an excel formula =NOW() which show the time. However, the time wi...