Need formula to return the earliest Date with the first blank "$ Amount" field that matches a specific ID

kstrangfeld

New Member
Joined
Nov 13, 2014
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi! I have a spreadsheet that we use to track contracts, Purchase orders, and Invoices/payments. I am trying to create a field that will show me the next soonest invoice date that doesn't yet have a dollar amount populated (because that date is the next invoice that we haven't yet paid... the amount gets populated when we pay it). I've seen combinations of index, match, isblank, but can't get it to work.

Each payment has a unique ID and they tie back to an Order ID (Purchase Order). Each Order has a unique ID, and each Contract has a unique ID. Contract-->Order-->Payment. Many payments can belong to one order, and many orders can belong to one contract.

I'd like this field to live in the Contracts tab and have it look for the Contract ID, which is also in the Payment sheet as a vlookup.

Ideally I think this is something like: Look for Contract ID 1234 in the Payments sheet, then look in the Invoice Amount field for whichever rows have a blank Invoice Amount, and return the one with the next soonest date.

Any ideas? Appreciate the help!!

Karen
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Show us a sample of data with a mocked up solution. Please use XL2BB to show your example. Do not post a picture as we cannot manipulate data in a picture. Your sample should only be 8-12 records.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top