Date Formula

P

prhrmk

I need some help! I am not sure this can be done because I have neve
used this type of formula. I just hope that I am explaining thi
correctly!?

I am trying to pull information in from one workbook to another (link
by date range like - 5/1/2005 thru 5/31/2005 and I want a dollar valu
to be pulled in for that range. I want the formula to recognize th
dates and not the cells because if someone sorts by lastname and the
saves the cells with the dates will be out of order.

I hope this is enough information? :eek:

Thanks,
 
R

Rob Hick

you sound like you want to use the VLOOKUP formula. This allows you to
look up a particular value in the far-left column of a table and return
a value from the same row in a different column. I think it probably
works between workbooks but i don't think i've done it before.

search help in Excel for 'VLOOKUP' and it should give you all you need.

Rob
 
R

RagDyeR

With your dates in Column A of Sheet2,
And your dollars in Column B of Sheet2,

Enter the starting date of your search in D1 of Sheet1,
And the ending date in D2,
And try this formula in Sheet1:

=SUMPRODUCT((Sheet2!A1:A300>=D1)*(Sheet2!A1:A300<=D2)*Sheet2!B1:B300)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message
I need some help! I am not sure this can be done because I have never
used this type of formula. I just hope that I am explaining this
correctly!?

I am trying to pull information in from one workbook to another (link)
by date range like - 5/1/2005 thru 5/31/2005 and I want a dollar value
to be pulled in for that range. I want the formula to recognize the
dates and not the cells because if someone sorts by lastname and then
saves the cells with the dates will be out of order.

I hope this is enough information? :eek:

Thanks,
p
 
B

barrfly

Assuming that there are no repeat dates, I would use the vlooku
function and reference the dates that way. Make sure that you includ
the ,false) at the end of the function to accurately reference out o
order dates if someone does sort by another factor.

If you have several rows with the same date, then this will not be th
best solution as the vlook function will only recognize the firs
occurence. In this case You could try the sumif function but that wil
aggregate all values associated with each date.

Barrfl
 
B

barrfly

Another way to combine all of the values between a date range would b
to use the sumif function. I would set it up like this

=sumif(A1:A100,">=5/15/2005",C1:C100) -
sumif(A1:A100,">5/31/2005",C1:C100)

hope this help
 
Top