Forum Discussion
Phil_887
Feb 06, 2024Copper Contributor
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
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_887Copper ContributorThank 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
PhilLike this:
=IF(OR(J2={"Completed","Let","Sold"}), SUM(COUNTIF(J$2:J2, {"Completed","Let","Sold"}))+4732, IF(J2="Credit Note", "Credit Note 008", "Pending"))