I have sales data for multiple years in a table. The estimate ref numbers in this table are serial numbers like 1, 2, 3 and so on. Every year this number restarts at value 1.
I need to give a reference number for a new estimate which is one more than the max estimate number prevailing for the year chosen.
At the moment I use the below method. Please let me know if there is a better option.
I use Advanced Filter Copy method and paste the filtered table for the required year in a new sheet and then find the max number from the estimate column.
Any better idea that does not involve creating another temp sheet. In excel I would use Aggregate function. But I am not sure how to write that statement in VBE.
Bookmarks