Drop down lists with formulas

[kantoinette]

Hello,

I created a spreadsheet that tracks invoices we have funded a different bank account.

I am wondering if there is a way to create a drop down list that can calculate due dates based on their terms?

INVOICE NUMBER

CUSTOMER

INVOICE DATE

DUE DATE

INVOICE AMOUNT

PAID AMOUNT

OUTSTANDING AMOUNT

STATUS

PAST DUE AGE

SELECTED

29729​

APEX

3/24/2022

4/23/22

$ 200.35

$200.35​

PAST DUE

1 - 30 Days

1​

This is what my spreadsheet data entry looks like. Invoice number is column A.

What I would like to do is have the due date column (D) have a drop down list have a drop down list that I can select their payment terms. (Due on receipt, Net 15, Net 30, Net 45, Net 60, Net 90) After I chose what their payment terms are for example Apex is net 30. I would like it to automatically calculate the due date in that cell based on the original invoice date. A different customer has Net 60 terms, I would like it to calculate the due date based on the invoice date.

Is there anyway something like this can be done? I have spent a couple hours trying to figure out a way and I am completely stumped.

Haaaaaalllllpppppp!

Thank you!

 

[etaf]

make a custom list in data validation
then use that field and add to the invoice date

But are the terms working days or absolute days

I have created a terms column - with a dropdone
and the formula for due date

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NVOICE NUMBER	CUSTOMER	INVOICE DATE	DUE DATE	INVOICE AMOUNT	PAID AMOUNT	OUTSTANDING AMOUNT	STATUS	PAST DUE AGE	SELECTED	terms
2	29729	APEX	3/24/22	5/8/22	$ 200.35		$200.35	PAST DUE	1 - 30 Days	1	45				(Due on receipt, Net 15, Net 30, Net 45, Net 60, Net 90)
3															15
4															30
5															45
6															60
7															90
8
Sheet1

Cell Formulas
Range	Formula
D2	D2	=C2+K2

Cells with Data Validation
Cell	Allow	Criteria
K2	List	=$O$3:$O$7

 

[kantoinette]

Thank you so much!!

 

[etaf]

you are welcome