Formula Updating - Changing Cell Reference

[Nitsirk82]

At the end of each quarter I need to update a spreadsheet. Presently to do this, I am manually changing 720 formulas (across 3 worksheets) with a find and replace. Each formula that is referencing another worksheet needs to be updated to reference just 3 rows down. So I highlight the changing cells, and change the 49 to 52, and the 50 to 53, etc. The formlas that are just simple calculations should not be changed. Is there a way to just select the changing cells and somehow add 3 to the entire worksheet (minus the cells that shouldn't change), instead of this painful manual process? Also, is there a simple way to make each date update 3 months out without me manually updating each cell?

Attached is a shortened version of my excel file. I took out the unrelated worksheets and highlighted the cells that need to be updated.

Claims Report.xls

Row 15 has date rantes from 4/11 - 3/12, everything needs to be shifted, next quarter Row 15 needs to display 7/11 - 6/12.

I hope I explained this clearly, thank you in advance for any help, tips, comments :-)

[vandan_tanna]

Attachment 157610

I have updated formula in row 15 of the "Product 1 Rolling 12 Mos" sheet.

You will need to change formula in the whole workbook to something similar. Once done changing cell O1 will shift everything as desired.

Is this what you are looking for?

[Nitsirk82]

Thank you, I'm starting to get somewhere with that. I don't completely understand the whole formula, the logic behind what it is doing so I can change it around for use all over. What's the negative 2 doing? When I used the formula in a different cell, I had to change that number, but I kinda guessed... lucky guess, to make it work.
Also, I'm having a difficult time using in cell B18, because there's a multiplication function in that cell.

[Nitsirk82]

I also suppose I could do those calculations on the other worksheet and then just reference the new cell??

[vandan_tanna]

I had to put in -2 since I we are using column number to offset rows. first data point is in column B --> column #2 so by doing -2 we get offset of 0, next column - column C - gets a row offset of 1.

Anyway, I have modified your worksheet a bit more.
1. I have changed that -2 to -3 ... now row number used for column B is the number you type in O1. Hopefully this makes sense.
2. I have defined 4 names to make lookup easier
3. I have updated formula in row 15 - 24of the "Product 1 Rolling 12 Mos" sheet.
4. I have also added some notes to the file

hopefully this gets you started.

PLEASE check for errors in my formula. I have tried to mimic what you were doing..but I might have made a mistake. so please check.

Here is the file: Attachment 157788

[Cutter]

Someone should, at some point, show what formula is being used so that members who can't download can understand what is going on.

[vandan_tanna]

Good point Cutter:

here is the formula:

Please Login or Register  to view this content.

this formula is copied across several columns

Here is how it works:

User types in starting row number in cell O1 (example 48)
above formula will return value from cell A48 into B15, A49 into C15, A50 in to D15 etc
This way user can increment number in O1 and all corresponding data will offset accordingly.

This is what OP was looking for.

[Nitsirk82]

Regarding your comments in my spreadsheet..

Pleae note that dates are not consistant in the claims tracker or total premium equivalnt sheets for ex. Claims tracker Row 27 is 6/1/2009 row 28 is 7/7/2009. If things were consistant we can use vlookup in stead of this complecated equaltion

That is an error that the dates aren't consistant. I never even noticed, I wasn't the one who built it, I'm just trying to improve it. But if a vlookup would be a better solution, I'd like to try that. I've attached a revised excel sheet, dates have been corrected. Point me in the right direction to doing a vlookup?
Claims Report.xls

[vandan_tanna]

Define TPE_Range as:

Please Login or Register  to view this content.

the B23 will be :

Please Login or Register  to view this content.

[Nitsirk82]

I'm sorry, I didn't quite follow that last post. Where does the first formula go?
Actually, don't worry about it, the solution in your first post is working for me, I'm going to run with that. Thank you so much for your help. You saved us hours of time.