How to increase the value in one cell by 1 after adding a value in each cell of another column

[kkaass]

Hi,

I would like to ask a question regarding the use of IF statement.

So basically, in column A I have serial numbers and this column is known as 'Serial No'. What I really want is that in just one cell B2, the value becomes equal to the value added in each additional cell of column A.

So for example, in A2 when I add value 1, B2 should have the value 1, when I add value 2 in A3, B2 should have the value 2, when I add value 3 in A3, B2 should become 3 and so on.

So whenever I enter a number in each additional cell of column A, the cell B2 should become equal to the number added in the latest cell of A. I can't use a 'range' in this case for A because it is not confirmed how many cells in column A will be filled out, its not fixed.

There might be a simple answer to this, but I just can't seem to figure out. Any help would be greatly appreciated.

 

[njimack]

=offset(a1,counta(a:a)-1,0)

 

[JoeMo]

If your serial numbers are real numbers and not text enter in B2:
=LOOKUP(1E+307,A:A,A:A)

If text try:
=LOOKUP(2,1/(A:A<>""),A:A)

 

[konew1]

=max(A:A)

 

[kkaass]

Works perfectly! Thank you so much Neil!

Just two quick queries.
Can you please explain the formula as well, if possible? I don't think I've used this statement before, so understanding it might be helpful for future reference or if someone else asks me.

Also, if the column changes, so for instance, if the column of reference is B rather than A, so how will the formula change? Will it be:
=offset(b1,counta(a:a)-1,0)

 

[kkaass]

Thanks alot konew1! Your given formula works perfectly well!

 

[njimack]

Works perfectly! Thank you so much Neil!

Just two quick queries.
Can you please explain the formula as well, if possible? I don't think I've used this statement before, so understanding it might be helpful for future reference or if someone else asks me.

Also, if the column changes, so for instance, if the column of reference is B rather than A, so how will the formula change? Will it be:
=offset(b1,counta(a:a)-1,0)

You'll also need to change the range in the COUNTA function from A:A to B:B.

The function starts from A1 and then OFFSETs by the number of non-blank (COUNTA) cells in column A. This duplicates the functions used to create dynamic ranges - see here: http://www.contextures.com/xlNames01.html#Dynamic

 

[kkaass]

Thanks alot JoeMo! Both formulae that you suggested are working. Like you said, the first one works when I have numbers and the second one works very well when I have text.

Further to your suggestion about text, is there a way where I enter text in a cell of column A, but a number appears in the cell B2 and that number increases each time I enter text in each additional cell in Column A.

For example, if I enter 'Serial1' in A2, number '1' appears in B2, when I enter 'Serial2' in A3, number '2' appears in B2, if I enter 'Serial3' in A4, number '3' appears in B2 and so on.

 

[kkaass]

Thank you Neil! I'll have a look at the link that you sent.

 

[JoeMo]

Thanks alot JoeMo! Both formulae that you suggested are working. Like you said, the first one works when I have numbers and the second one works very well when I have text.

Further to your suggestion about text, is there a way where I enter text in a cell of column A, but a number appears in the cell B2 and that number increases each time I enter text in each additional cell in Column A.

For example, if I enter 'Serial1' in A2, number '1' appears in B2, when I enter 'Serial2' in A3, number '2' appears in B2, if I enter 'Serial3' in A4, number '3' appears in B2 and so on.

Like this?

Excel Workbook
	A	B
2	serial1	5
3	serial2
4	serial3
5	serial4
6	serial5
Sheet1