Trying to add a summary of past results

shadenem3

New Member
Joined
Mar 9, 2011
Messages
8
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

texasaswang

New Member
Joined
Mar 13, 2011
Messages
43
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
 

Forum statistics

Threads
1,141,224
Messages
5,705,116
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top