I have a table with numbers used as a unique identifier. I need to find the max number in that column and add 1 to it. I need it to not change when I filter the column. Is there a formula I can use? If not what would a macro look like? Thank you!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Find max number in a table and add 1
0
Answers
0
Hi Josh(?) and welcome to the Forum.
The Excel worksheet function MAX isn't affected by filtering so you can use that.
If your identifiers are in column B say (and nothing above or below your table), this formula will work:
=MAX(B:B)+1where the B:B bit in bold is the range for which the largest number is returned by MAX (and 1 added to it to give your "Next available identifier").
It's better to point to the column of the table containing your identifiers however. If you've put a formula like the above in a cell, you can edit the B:B bit and select the part of the table containing the identifers- click in the column header and drag the cursor to the bottom then Enter. You'll then get a formula like this:
=MAX(Table1[[#All],[Column2]])+1where my second column in the table holds those numbers.
Hope this makers sense and solves your problem.