Hi

I have a subreport that is working off an excel database, the excel "database" is actually a table with 2 columns for the sake of arguments lets call it
Date and Stock Price
In my report I want to have a table like this

    Date            Stock Price
This Month   
1 Month Ago   
6 Months Ago   
12 Months Ago   
24 Months Ago   
   Launch   


I have set up formulas to pull the relative dates from my database for the left most column, using formulas, so I have 6 formula "This month", "This Month -1", "this Month -6" etc. Now I want someway so that I can pull the equivalent stock price corresponding to the displayed date for the Stock Price Column

If this where excell I would do a vlookup but I cant seem to find an answer anywhere on how to do the equivalent thing in Crystal Reports 2008.

Thank you for your help and time.

Nick


Edited by Silvrhelm - 19 Jan 2012 at 6:33am

Well My table of dates and stock price only has one value for each month so for the last day in every month there is an entry.

I didnt think about writing a complex formula like that, I just have 6 formula
This Month = Maximum(table.date) = 31/12/11
This Month-1 = DateAdd('m',-1,{@This Month}) = 30/11/11
This Month-6 = DateAdd('m',-6,{@This Month}) = 30/06/11
etc

So I tried these two formula
sum(table.stockprice,table.date,{@This Month})
Thinking this was kind of
sum stockprice if date = {@This Month}
But I get an Error it highlights teh {@This Month} and says "Group Condition Must be a string" I tried using totext and that didnt work.

I also tried sum(table.stockprice,{@ThisMonth})

Then it highlights @ThisMonth and says "This field cannot be used as a group condition field"

if anyone can help me along with this, or even talk me through how i might combine all this into just 2 formula like kostya seemed to be hinting at, that would be great.

Thanks
Nick

Edited by Silvrhelm - 19 Jan 2012 at 11:25pm