Trying to add a summary of past results

[shadenem3]

I'm just getting back into Excel after a few years of not using it, so the solution to my question will probably be ridiculously simple. I've been trying for a day now and just can't get my head around it. So here goes:

I have a worksheet that i simply insert a week-ending date at the top (cell b10) and it retrieves data from various areas of my workbook, goes through a series of complicated formulae (all shown), and spits out a final result for the week ("weekly profit" in cell f30).

For the second part of my sheet i have to show a summary of the past 10 weeks results. I have the week-ending dates all in a column on the left but would like to retrieve the end-result from cell f10 for each of the weeks.

Essentially, i guess what I'm trying to end up with is a formula or such to say " if the date = a week ago, what would be the value of cell f10, 2 weeks ago, 3 weeks ago etc..." with out all the formulae and intermediate results being shown.

I've played with what-if analysis, and goal seek a bit, but can't get where i want to be. Worse case scenario, I copy the worksheet over 10 times and retrieve the results using VLOOKUP. I was hoping to be able to do this without it being shown.

Thanks

 

[texasaswang]

I am no expert, but you create the table initially.
Each week, include a sub that deletes the first line in your table, then drops down to the end, and copies in the newly created date from the other worksheet. Copy over the profit value, convert both to values rather than formulas.

There may be more elegant methods, but I have done this on occasion.

I can provide sample code, but it is pretty basic.

Joe