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 invoice numbers. These need to start at 4733. 

Column J shows the status of the transaction. The input can be selected from a dropdown list.

 

If the selected input in an individual cell in Column J is "Under Offer", or "Negotiating", then I want the corresponding cell in Column C to show "Pending", however, if the input in Column J is "Completed", or "Let", or "Sold", then I want the corresponding cell in Column C to show an invoice number which will start in cell C1 with "4733".

 

Please can someone help me with some guidance on the structure of the formula I need.

 

Many thanks

 

Sincerely

 

Phil Richards

  • Phil_887 

    Let's say the data start in row 2.

    In C2:

     

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

     

    Fill down

    • Phil_887's avatar
      Phil_887
      Copper Contributor
      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
      • 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"))

Resources