So, say I am selling for packages. Each package has a different price.
You can Pay in full within 14 days or on the 20th of the following month and the 20th of the 4-month following.
I want to set up a spreadsheet that I can record this with minimal input.
Basically, all I want to do is write in the date and the name in column A and B. Then select a programme and Pay type from drop-down boxes in C and E (this is done) Then I want it to automatically add in everything else. So far it adds in the full amount D and the collect months 1 and 2 (not exactly how I want is as per point 1). This is to help with work but also personal learning.
- Make it so If I select PIF from the drop-down lists in column E, the collect months 1 and 2 (G and I columns) will automatically say PIF. Currently, I have it as =VLOOKUP(C3,Sheet5!$A$20:$D$23,4,FALSE) in the hopes to do this with a table I had. I had C3:D5,Sheet… so on but it only saw C3. I could have G and I as =D/2 but prefer to also incorporate PIF not just DDs as PIF are not divided by 2 payments.
- Make the Due date for a DD be the 20th of the following month. So, if they joined on the 27 May it would say June 20, if they joined 4 June it would say July 20. For the PIF it to automatically calculate 14 days from the day's date. If I do =A4+14 that would work, but I could not put that formula into each box as some are DD.