I'd like to set up sheet showing data from a number of contracts and have
formulas on worksheets tracking individual items refer to the "contracts"
worksheet. I'd like to have the "contracts" worksheet containing the
following headings:
Contract Item
StartDate
EndDate
Unit Price
I'd like to have Excel pick up the proper price when given a date and the
contract item. For example, on the sheet to track item7, I would have columns
with invoice date, units purchased, and unit price. I'd like a formula in
the unit price column to pick it out of the contracts worksheet based on the
invoice date being in the term of the contract and the contract item would be
item7 in this case.
Any given date will have multiple contracts (nine, actually). But only one
for each item.
Any thoughts?
Thank you.
EQC

please use validation if the types are not more along with vlookup.
Or else you can simply us vlookup
--
Thanks
Suleman Peerzade


"E.Q." wrote:

I'd like to set up sheet showing data from a number of contracts and have
formulas on worksheets tracking individual items refer to the "contracts"
worksheet. I'd like to have the "contracts" worksheet containing the
following headings:
Contract Item
StartDate
EndDate
Unit Price
I'd like to have Excel pick up the proper price when given a date and the
contract item. For example, on the sheet to track item7, I would have columns
with invoice date, units purchased, and unit price. I'd like a formula in
the unit price column to pick it out of the contracts worksheet based on the
invoice date being in the term of the contract and the contract item would be
item7 in this case.
Any given date will have multiple contracts (nine, actually). But only one
for each item.
Any thoughts?
Thank you.
EQC

In sheet: Contracts, you have in cols A to D, as posted:
Contract Item
StartDate
EndDate
Unit Price

In your other sheet to track item7, assume you have in cols A to C:
invoice dates, units purchased, and unit prices

In this sheet, to extract the unit prices into col C, you could use this
multi-criteria index n match below to lookup "item7" and check the invoice
date in col A for the appropriate valid contract period in "Contracts"

Put in C2, normal ENTER:
=INDEX(Contracts!D$2:D$10,MATCH(1,INDEX((Contracts !A$2:A$10="item7")*(Contracts!B$2:B$10<=A2)*(Contr acts!C$2:C$10=A2),),0))
Copy down as required. Adapt the ranges to suit your actuals in "Contracts".
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"E.Q." wrote:
I'd like to set up sheet showing data from a number of contracts and have
formulas on worksheets tracking individual items refer to the "contracts"
worksheet. I'd like to have the "contracts" worksheet containing the
following headings:

I'd like to have Excel pick up the proper price when given a date and the
contract item. For example, on the sheet to track item7, I would have columns
with invoice date, units purchased, and unit price. I'd like a formula in
the unit price column to pick it out of the contracts worksheet based on the
invoice date being in the term of the contract and the contract item would be
item7 in this case.
Any given date will have multiple contracts (nine, actually). But only one
for each item.
Any thoughts?
Thank you.
EQC

That worked. I'll keep this in my repetoire for sure.
But I'm trying to figure it out. Is the first 1 in the match statement
basically to identify when the three conditions that follow are true?

Welcome, good to hear.

Is the first 1 in the match statement basically
to identify when the three conditions that follow are true?

Yes
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"E.Q." wrote in message
...
That worked. I'll keep this in my repetoire for sure.
But I'm trying to figure it out. Is the first 1 in the match statement
basically to identify when the three conditions that follow are true?

You forgot to quote enough of the previous message text to put your reply
into context, but if you want to know which parameter does what in the MATCH
function, Excel help will tell you.
--
David Biddulph

E.Q. wrote:
That worked. I'll keep this in my repetoire for sure.
But I'm trying to figure it out. Is the first 1 in the match statement
basically to identify when the three conditions that follow are true?