Problem with formula
 

Hello All,

My problem is I got this formula, which achieve the results I want
apart from one solution. The solution that I am looking for this
formula isn't happening. The equation is: -

=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(D17:D20)))

The solution that I am looking for if any number is selected out of
D17:D20 it should be display in D15, but the equation you must select
all 4 cells from D17:D20 for the min value to display in D15.

Anyone there that can help me?


many thanks

ims

Problem with formula
 

Problem with formula
 

Hi Ken,

Thanks for your response, but i am looking for a formula similar to the
one below with a small change.


Ken Johnson wrote:
wrote:
Hello All,

My problem is I got this formula, which achieve the results I want
apart from one solution. The solution that I am looking for this
formula isn't happening. The equation is: -

=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(D17:D20)))

The solution that I am looking for if any number is selected out of
D17:D20 it should be display in D15, but the equation you must select
all 4 cells from D17:D20 for the min value to display in D15.

Anyone there that can help me?


many thanks

ims

Hi ims,

I'm not sure I understand what you are trying to do.

Sounds like you want D15 to show the value in the selected cell if that
selected cell is from the range D17:D20, but if all 4 of the D17:D20
cells are selected then you want D15 to show the minimum of the D17:D20
cell values.

I don't know of any functions that return a value depending on the
range of selected cells.
However, you could use this Worksheet_SelectionChange event
procedure...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$D$17:$D$20"
Range("D15").Value = WorksheetFunction.Min(Target)
Case "$D$17", "$D$18", "$D$19", "$D$20"
Range("D15").Value = Target.Value
Case Else
Range("D15").ClearContents
End Select
End Sub

To get the code in place...

1. Copy it
2. Right click the worksheet's sheet tab then choose "View Code" from
the popup menu
3. Paste the code into the worksheet module
4. Press Alt + F11 to get back to Excel
5. The code requires that Security level be Medium and Macros Enabled.
To do this go Tools|Macro|Security|Medium|OK|Close|Open|Enable Macros.

Ken Johnson

Problem with formula
 

Hi Ken,

Thanks for your response, but i am looking for a formula similar to the
one below with a small change.


Ken Johnson wrote:
wrote:
Hello All,

My problem is I got this formula, which achieve the results I want
apart from one solution. The solution that I am looking for this
formula isn't happening. The equation is: -

=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(D17:D20)))

The solution that I am looking for if any number is selected out of
D17:D20 it should be display in D15, but the equation you must select
all 4 cells from D17:D20 for the min value to display in D15.

Anyone there that can help me?


many thanks

ims

Hi ims,

I'm not sure I understand what you are trying to do.

Sounds like you want D15 to show the value in the selected cell if that
selected cell is from the range D17:D20, but if all 4 of the D17:D20
cells are selected then you want D15 to show the minimum of the D17:D20
cell values.

I don't know of any functions that return a value depending on the
range of selected cells.
However, you could use this Worksheet_SelectionChange event
procedure...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$D$17:$D$20"
Range("D15").Value = WorksheetFunction.Min(Target)
Case "$D$17", "$D$18", "$D$19", "$D$20"
Range("D15").Value = Target.Value
Case Else
Range("D15").ClearContents
End Select
End Sub

To get the code in place...

1. Copy it
2. Right click the worksheet's sheet tab then choose "View Code" from
the popup menu
3. Paste the code into the worksheet module
4. Press Alt + F11 to get back to Excel
5. The code requires that Security level be Medium and Macros Enabled.
To do this go Tools|Macro|Security|Medium|OK|Close|Open|Enable Macros.

Ken Johnson

Problem with formula
 

Hi ims,

If my interpretation of your aim is correct, then I'm almost certain
there isn't a formula that will do that.

I could email a worksheet with the code inplace if you like.

Ken Johnson

Problem with formula
 

Many thanks Ken but No thanks, I have too many formulas on one sheet
with the same output.

Cheers anyway

Ims


Ken Johnson wrote:
Hi ims,

If my interpretation of your aim is correct, then I'm almost certain
there isn't a formula that will do that.

I could email a worksheet with the code inplace if you like.

Ken Johnson