How to exclude selected cell in a formula - for row numbering

[solmon]

1. I have an excel sheet with a column where I want every number should b numbered, but since it’s not sorted in the sequence this number, and when i want to insert a new number for this row, I need to know what is the biggest number that is already used in this column, also I want to make it quick and easy, so I want to make a macro with a formula that should give me the next number after the max number, but the problem is if let say the column is column G, and I enter a formula =MAX(G:G)+1, it will include the current cell, so I would like to know if it’s possible to include in the formula the whole column but exclude the current selected cell?
2. Also I would like to know if there is a different way how to add a number for each row based on the maximum number that is inserted in this column?

 

[jorismoerings]

Hi,

Just on 1.

If you're using VBA to create the New value based on the Max Function you're describing an non-existent problem. You're right when using formula's but within VBA you need to determine the Max value before you can use it to add 1 to it. Tha's just programming logic.

If you want VBA try this:

Sub Enter_new_number_as_previous_max_plus_1()

Dim rng As Range
Dim OldMax, NewMax As Double

'Set range from which to determine largest value
Set rng = Sheet1.Range("G:G")

'Worksheet function MAX returns the largest value in a range
OldMax = Application.WorksheetFunction.Max(rng)

'Create New line Number based old Max +1
NewMax = OldMax + 1

'enter the value in the active Cell
ActiveCell.FormulaR1C1 = NewMax

End Sub

 

[solmon]

Thanks. worked fine.
But for next time, do you know if there is a way how to exclude in a formula a single cell or a smaller range of cells from the bigger range of cells on which the formula is based to calculate it. as in my example that i want to get the max of this column excluding the current cell?

 

[jorismoerings]

That's such a general question for which i don't have a suitable answer. There are many solutions in creating an array of cells but those all need to be fit for purpose and hence there isn't 1 "right way" to do it.

Just an example;
assume:

1. your list is in column A;
2. Your list starts at A1;
3. Your numbering is:
   1. starting at 1
   2. sequential;
   3. randomly sorted.

from these assumptions, I conclude that the highest number in the list can never be greater than the number of rows which is used for the list. The highest number in the list is therefore equal to the number of rows used. Consequently concluded that Max + 1 = row number of the first empty cell. So in this case the value for Max value + 1 = Row()

Just an example of many possibilities.

Hope this makes sense.

 

[solmon]

thanks. it sounds good. but the problem with such a formula is when you sort the columns, and its getting updated to the new row value, so u cant rely on the number

 

[jorismoerings]

It was just an example and as you mention it does recalculate as soon as you resort the list. This proves my point the solution must be fit for purpose thus approach of a formula will change depending on the way you want to use the value.
Meaning no straight forward answer with a "right solution" is possible.

The earlier mentioned VBA works every time!