# Ranking based on earliest data input

#### ExcelNewbie2020

##### New Member
Experts,

 Value Rank Name 1 10 5 Name 2 Name 3 200 1 Name 4 Name 5 Name 6 Name 7 1000 2 Name 8 Name 9 Name 10 500 3

Is there any excel function that will rank the values based on the first data inputted?
on the above table rank # 1 goes to Name3 because i inputted the value 200 1st, rank #2 is Name7 because i inputted 1000 2nd and so on..

I am looking for a function that once i input a value, the rank/sequence will update automatically. Thank you very much..

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

##### Well-known Member
You Sholud add Time & Date input and then Sort based on
You can Use now Function for this also. and try this method.
In File\Options\Formulas check Enable iterative calculation Then, assuming that A1 is currently blank, enter

Excel Formula:
``=IF(A1 = 0,TODAY(),A1)``

##### Well-known Member
Sorry try this at C1:
Excel Formula:
``=IF(A1<>"",IF(C1="",NOW(),C1),"")``

#### Peter_SSs

##### MrExcel MVP, Moderator
Assuming that you won't ever delete or change a value in column B, & column C at least contains a heading to start with, you could try this worksheet change event code with a copy of your workbook.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, c As Range
Dim NextRank As Long

Set Changed = Intersect(Target, Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row))
If Not Changed Is Nothing Then
NextRank = Columns("C").SpecialCells(xlConstants).Count
Application.EnableEvents = False
For Each c In Changed
c.Offset(, 1) = NextRank
Next c
Application.EnableEvents = True
End If
End Sub``````

#### ExcelNewbie2020

##### New Member

You Sholud add Time & Date input and then Sort based on
You can Use now Function for this also. and try this method.
In File\Options\Formulas check Enable iterative calculation Then, assuming that A1 is currently blank, enter

Excel Formula:
``=IF(A1 = 0,TODAY(),A1)``

Sorry try this at C1:
Excel Formula:
``=IF(A1<>"",IF(C1="",NOW(),C1),"")``
it gives me circular reference error..thanks

##### Well-known Member
Are you do this.
In File\Options\Formulas check Enable iterative calculation (add tick mark) Then

#### ExcelNewbie2020

##### New Member

Are you do this.
In File\Options\Formulas check Enable iterative calculation (add tick mark) Then
sorry, after enabling still not working. I guess i did not make my explanation clear. The value and rank column is initially empty. the data in the rank column will depends on data that will be inputted (in random) in value column.

#### ExcelNewbie2020

##### New Member
Assuming that you won't ever delete or change a value in column B, & column C at least contains a heading to start with, you could try this worksheet change event code with a copy of your workbook.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, c As Range
Dim NextRank As Long

Set Changed = Intersect(Target, Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row))
If Not Changed Is Nothing Then
NextRank = Columns("C").SpecialCells(xlConstants).Count
Application.EnableEvents = False
For Each c In Changed
c.Offset(, 1) = NextRank
Next c
Application.EnableEvents = True
End If
End Sub``````
this actually works using vba. However, Is there any chance this can be done thru excel formula? thank you so much

#### Peter_SSs

##### MrExcel MVP, Moderator
Enable iterative calculation
Just be aware that this is an application-wide setting so applies to all formulas in all sheets in all workbooks while the setting is in place.

Replies
1
Views
308
Replies
28
Views
998
Replies
0
Views
85
Replies
3
Views
46
Replies
8
Views
105

1,129,993
Messages
5,639,423
Members
417,089
Latest member
jonstr101

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back