Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## boostinu13

22 posts

Geek

#272049 7-Jun-2020 13:43

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.

## elpenguino

1338 posts

Uber Geek

Subscriber

#2500051 7-Jun-2020 14:13

Since you want to do something if...... Have you considered the 'iif' function?

## boostinu13

22 posts

Geek

#2500052 7-Jun-2020 14:15

elpenguino: Since you want to do something if...... Have you considered the 'iif' function?

News »

New Zealand software Rfider tracks coffee from Colombia all the way to New Zealand businesses
Posted 3-Aug-2020 10:35

Logitech G launches Pro X Wireless gaming headset
Posted 3-Aug-2020 10:21

Sony Alpha 7S III provides supreme imaging performance
Posted 3-Aug-2020 10:11

Sony introduces first CFexpress Type A memory card
Posted 3-Aug-2020 10:05

Marsello acquires Goody consolidating online and in-store marketing position
Posted 30-Jul-2020 16:26

Fonterra first major customer for Microsoft's New Zealand datacentre
Posted 30-Jul-2020 08:07

Everything we learnt at the IBM Cloud Forum 2020
Posted 29-Jul-2020 14:45

Dropbox launches native HelloSign workflow and data residency in Australia
Posted 29-Jul-2020 12:48

Spark launches 5G in Palmerston North
Posted 29-Jul-2020 09:50

Lenovo brings speed and smarter features to new 5G mobile gaming phone
Posted 28-Jul-2020 22:00

Withings raises \$60 million to enable bridge between patients and healthcare
Posted 28-Jul-2020 21:51

QNAP integrates Catalyst Cloud Object Storage into Hybrid Backup solution
Posted 28-Jul-2020 21:40

Vector and AWS join forces to accelerate the future of energy
Posted 28-Jul-2020 21:35

JBL launches new mobile earbuds and PC speakers
Posted 22-Jul-2020 16:04

Ingram Micro brings virtualisation intelligence to market
Posted 21-Jul-2020 13:25

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.