choosing a cell with a comment

[Guest]

I need a formula that will allow me to display different text values based on
a sum of 2 cells from another worksheet depending on the value of the sum....

if the sum of the 2 cells is >=0.3 & <=0.49 then I need to display a comment
stored in cell F22 from another worksheet. If sum is <=0.5 I need comment
from cell F23. If the sum is<0.3 then I need the cell to remain blank.

 

[Guest]

=if((a1+a2)>=0.3,if((a1+a2)<=0.49,Sheet2!$F$22,Sheet2!$F$23),"")
(I'm guessing that F23 should be displaed if the sum is >0.49. If you
really intended F23 to be shown if the sum is >0.49 and <=0.5, and a blank if

0.50, then it's

=if((a1+a2)>=0.3,if((a1+a2)<=0.49,Sheet2!$F$22,if((a1+a2)<=0.50,Sheet2!$F$23,"")),"")

 

[Dave Peterson]

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

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


So to meet your requirements, you can do something like:

=IF(SUM(Sheet2!A1,Sheet2!C9)<0.3,"",
IF(SUM(Sheet2!A1,Sheet2!C9)<0.5,
getcomment(Sheet2!F22),getcomment(Sheet2!F23)))
(all one cell)

Actually it's not quite what you asked, but it does include 0.495 in its tests.

 

[Guest]

Thank you sooooo much - I have been staring at that for hours - is it the $
parameters that make a difference?

 

[Guest]

I don't know; difference from what?
The $ turns a relative cell reference to an absolute reference, so that when
you copy the formula, the reference remains fixed. Ex: if you copied this
formula down one row, the formula will still reference $F$22 and $F$23, etc.
Without the $, the formula would reference F23 and F24. Note that some
references should be relative (like a1 and a2, which should point to the next
row's data when copied) and others fixed (like f22 and f23, which must remain
fixed or you'll be pointing to nothing).