Drop down lists with formulas

kantoinette

New Member
Joined
May 10, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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 NUMBERCUSTOMERINVOICE DATEDUE DATEINVOICE AMOUNTPAID AMOUNTOUTSTANDING AMOUNTSTATUSPAST DUE AGESELECTED
29729​
APEX3/24/20224/23/22$ 200.35
$200.35​
PAST DUE1 - 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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,119
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLMNO
1NVOICE NUMBERCUSTOMERINVOICE DATEDUE DATEINVOICE AMOUNTPAID AMOUNTOUTSTANDING AMOUNTSTATUSPAST DUE AGESELECTEDterms
229729APEX3/24/225/8/22$ 200.35$200.35PAST DUE1 - 30 Days145(Due on receipt, Net 15, Net 30, Net 45, Net 60, Net 90)
315
430
545
660
790
8
Sheet1
Cell Formulas
RangeFormula
D2D2=C2+K2
Cells with Data Validation
CellAllowCriteria
K2List=$O$3:$O$7
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,119
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Forum statistics

Threads
1,175,531
Messages
5,897,955
Members
434,688
Latest member
vi28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top