Problem with formula

[ims121uk]

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(D1720)>0),"",IF(COUNTIF(D1720,D15)=4,D15,MIN(D1720)))

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

Anyone there that can help me?


many thanks

ims

 

[Ken Johnson]

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(D1720)>0),"",IF(COUNTIF(D1720,D15)=4,D15,MIN(D1720)))

The solution that I am looking for if any number is selected out of
D1720 it should be display in D15, but the equation you must select
all 4 cells from D1720 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 D1720, but if all 4 of the D1720
cells are selected then you want D15 to show the minimum of the D1720
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

 

[ims121uk]

Hi Ken,

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

 

[ims121uk]

Hi Ken,

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

 

[Ken Johnson]

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

 

[ims121uk]

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

Cheers anyway

Ims