Dear Friends
I want a function which should auto short the numbers or letter
EX:
1
2
4
if i enter now 3 it should go above 4 automatically (I am not expert in VBA)

Hi,

This ARRAY formula will return an exact match or of none exists the higher
number

=MIN(IF(A1:A10=B1,A1:A10))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Fareez" wrote:

Dear Friends
I want a function which should auto short the numbers or letter
EX:
1
2
4
if i enter now 3 it should go above 4 automatically (I am not expert in VBA)

Function cannot do ** autosort *** by itself instead can display the same
values in a different column in sorted order. Try the below VBA solution for
autosort..

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.EnableEvents = True
End If
End Sub

--
Jacob


"Fareez" wrote:

Dear Friends
I want a function which should auto short the numbers or letter
EX:
1
2
4
if i enter now 3 it should go above 4 automatically (I am not expert in VBA)