Find Next Bill Number

  • I make the bills randomly as and when I get information.


    Please see the attached file


    You can see the bill numbers mentioned randomly.


    I am having many more parties but shown only few here, I would like to create a formula in which Cell F2 would display the next bill number


    For Eg. in the Cell F2 it shows next bill number as 350


    When I create a new bill in S. No. 1 as 350, the Cell F2 should automatically change to 351 as next bill number.


    BUT when F2 shows 350 and I enter 353 or any other number besides 350 it should give an error.

  • Re: Find Next Bill Number


    For the first part, add this formula to cell F2:


    =1+MAX(OFFSET(E7,0,0,COUNTA(C7:C65536),1))


    For the second part, add a Data Vaildation formula to the cells in E7 down:


    =E7-LARGE(OFFSET($E$7,0,0,COUNTA($C$7:$C$65536),1),2)=1


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Find Next Bill Number


    I have done as you told and Cell F2 shows the next bill number
    I have also entered the formula data validation


    But when I enter the bill number it gives an error
    The Value You entered is not valid
    A user has restricted values that can be entered into this cell.

  • Re: Find Next Bill Number


    That is what the Data Validation should do. It's basically not allowing you to enter that number because it's not one greater than the previous highest number. You can customise the error message if required.


    Or are you saying that you still get the error message even if you enter a number that should be allowed?

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Find Next Bill Number


    Quote from Batman;737509

    That is what the Data Validation should do. It's basically not allowing you to enter that number because it's not one greater than the previous highest number. You can customise the error message if required.


    Or are you saying that you still get the error message even if you enter a number that should be allowed?


    Yes sir I get the error message even if I enter the number as shown.


    Secondly when I enter the code in data validation and press enter it gives an error saying "The formula currently evaluates an error. Do you want to continue"


    Please see the attached file.

  • Re: Find Next Bill Number


    The problem relates to the fact that your data in column E is now blank. The process would work if you already had data in that range before you set up the data validation. If you want a process to work with a blank range, assuming that your number sequence will start from 1 then you could use


    =IF(COUNT(OFFSET($E$7,0,0,COUNTA($C$7:$C$65536),1))<3,E7=COUNT(OFFSET($E$7,0,0,COUNTA($C$7:$C$65536),1)),E7-LARGE(OFFSET($E$7,0,0,COUNTA($C$7:$C$65536),1),2)=1)

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!