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

kkaass

Board Regular
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.

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
=offset(a1,counta(a:a)-1,0)

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)

=max(A:A)

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)

Thanks alot konew1! Your given formula works perfectly well!

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

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.

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

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
AB
2serial15
3serial2
4serial3
5serial4
6serial5
Sheet1

Replies
1
Views
331
Replies
1
Views
187
Replies
5
Views
325
Replies
2
Views
193
Replies
3
Views
736

1,196,254
Messages
6,014,273
Members
441,810
Latest member
LouLou1234

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.

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