Home |
Search |
Today's Posts |
#1
|
|||
|
|||
choosing a cell with a comment
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. |
#2
|
|||
|
|||
choosing a cell with a comment
=if((a1+a2)=0.3,if((a1+a2)<=0.49,Sheet2!$F$22,She et2!$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,"")),"") "kate nz" wrote: 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. |
#3
|
|||
|
|||
choosing a cell with a comment
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. kate nz wrote: 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. -- Dave Peterson |
#4
|
|||
|
|||
choosing a cell with a comment
Thank you sooooo much - I have been staring at that for hours - is it the $
parameters that make a difference? "bpeltzer" wrote: =if((a1+a2)=0.3,if((a1+a2)<=0.49,Sheet2!$F$22,She et2!$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,"")),"") "kate nz" wrote: 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. |
#5
|
|||
|
|||
choosing a cell with a comment
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). "kate nz" wrote: Thank you sooooo much - I have been staring at that for hours - is it the $ parameters that make a difference? "bpeltzer" wrote: =if((a1+a2)=0.3,if((a1+a2)<=0.49,Sheet2!$F$22,She et2!$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,"")),"") "kate nz" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in cell comment | Excel Discussion (Misc queries) | |||
displaying a comment only when the cell is selected | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
How do I copy text from a cell into a comment? | Excel Discussion (Misc queries) | |||
How can I move the comment box to the left of the cell? | Excel Worksheet Functions |