Forum Discussion

Phil_887's avatar
Phil_887
Copper Contributor
Feb 06, 2024

Excel formula

Hi  Please can someone in the community help.   I have some data in a formatted table and I am looking for a suitable formula that will help me with the following:   Column C contains a list of ...
excel
Phil_887's avatar
Phil_887
Copper Contributor
Feb 06, 2024
Thank you so much Hans it works really well. One final question, how do I amened your suggested statement so that if Column J is set to Credit Note, the input in C2 would be Credit Note 008. The next entry below would need to continue the sequence of invoice numbers.

Many thanks

Phil
HansVogelaar's avatar
HansVogelaar
MVP
Feb 06, 2024

Phil_887 

Like this:

=IF(OR(J2={"Completed","Let","Sold"}), SUM(COUNTIF(J$2:J2, {"Completed","Let","Sold"}))+4732, IF(J2="Credit Note", "Credit Note 008", "Pending"))

  • Phil_887's avatar
    Phil_887
    Copper Contributor
    Feb 07, 2024

    HansVogelaar 

    Hi Hans, You have been so very helpful.

    My formula now reads:

    =IF(OR(A2={"COMPLETED"}), SUM(COUNTIF(A$2:A2,{"COMPLETED"}))+4732, IF(A2="CREDIT NOTE", "CREDIT NOTE 008", "PENDING"))

     

    How do I make a final adjustment so  that when there is another credit note in the data set it comes up with the next number in the sequence i.e. credit note 009 and so on and so.

     

    I really appreciate your input / assistance.

     

    Regards

     

    Phil

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 07, 2024

      Phil_887 

      Since you now only look at COMPLETED, part of the formula can be simplified.

       

      =IF(A2="COMPLETED", COUNTIF(A$2:A2,"COMPLETED")+4732, IF(A2="CREDIT NOTE", "CREDIT NOTE "&TEXT(COUNTIF(A$2:A2, "CREDIT NOTE")+7, "000"), "PENDING"))

      • Phil_887's avatar
        Phil_887
        Copper Contributor
        Feb 09, 2024
        Hi Hans,
        Thank you so much for your replies. I am the MD of a small Commercial Real Estate advisory company here in the UK. I have a reporting function to the other board directors / owners and would like to improve my use of Excel so that I can increase my efficiency/ productivity and ability to report accurate financial data.

        Are you able to provide weekly one to one tuition via a Teams call so that I can share my data set with you and improve my use of Excel.
        My company is Brecker Grossmith. E-mail: email address removed for privacy reasons / UK cell: +44 (0) 7842 385 898. Please can you let me know your thoughts and confirm if you can help with this. How much you would charge for your time?

        I look forward to hearing from you.
        Regards and best wishes

        Phil Richards MRICS


Resources