Excel formula

[Phil_887]

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

[#2]

@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

[#3]

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

[#4]

@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"))

[#5]

@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

[#6]

@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"))

[#7]

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

[#8]

@Phil_887 

Hi,

I don't provide paid consultancy, perhaps someone else will jump in.

[#9]

Thanks. No problem.
is there anyone else who might be willing / able to assist?
Regards

Phil