I have 2 worksheets, one is for production projections and the other has
actual production numbers and dates that I use in greenhouse crop
production.
I want to compare the projections to the actual numbers but haven't a clue
on where to start.
The columns for item number and description are the same in both sheets. The
columns plant dates and quantities are different values. I have target dates
say of item 123, description Alyssum, target date 2/1/08, quantity 100. The
other sheet has item 123, Alyssum, plant date 2/5/08, quantity 106.
I was thinking about if() and and() or sumproduct() functions but don't know
how to proceed or is there a better way. I want to go from the item number
and then find the actual plant date that is closest to the projection target
date of that item number, and then pull in the actual planted quantity.
Any help is greatly appreciated.

--
Lee

I'm a little hazy on the request. May need a few more lines of data. But,
here's my assumptions:

You are looking to find which item # has a target date that is closest to
the plant date and also include the quantity.

So, I would try to keep it relatively simple...

in sheet1, column E:

=ABS(VLOOKUP(A2,Sheet2!A:D,3,0)-C2)
Make sure you have the format your cell as date if not already set. (ABS is
the absolute value, removing negative sign)

in Column F:
=VLOOKUP(A2,Sheet2!A:D,4,0)

Then sort this table by column E, ascending.

the first value is your closest date first, with quantity to the right...


"Lee" wrote:

I have 2 worksheets, one is for production projections and the other has
actual production numbers and dates that I use in greenhouse crop
production.
I want to compare the projections to the actual numbers but haven't a clue
on where to start.
The columns for item number and description are the same in both sheets. The
columns plant dates and quantities are different values. I have target dates
say of item 123, description Alyssum, target date 2/1/08, quantity 100. The
other sheet has item 123, Alyssum, plant date 2/5/08, quantity 106.
I was thinking about if() and and() or sumproduct() functions but don't know
how to proceed or is there a better way. I want to go from the item number
and then find the actual plant date that is closest to the projection target
date of that item number, and then pull in the actual planted quantity.
Any help is greatly appreciated.

--
Lee