Changing the Row reference of a formula

[pcarmour]

Hi,
I have a worksheet that gives a vertical summary of my monthly figures.
When I add a new month I need to change the row reference in the new column's formula.
You can see that I now add the new row reference number to Cell F1
How can I use this to change the row reference in column G:G bearing in mind that it's not always 120.

Please see attached.
I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

[shrivallabha]

I'd suggest following.

In Vertical sheets column headings put dates and format them as "mmmm"
Sheet Vertical | Cell C1 = 8/1/2013;
Sheet Vertical | Cell E1 = 9/1/2013;
Sheet Vertical | Cell G1 = 10/1/2013;

Once this is done. In cell C2, put this formula:
=INDEX(Summary!$B$1:$AO$150,MATCH(Vertical!C$1,Summary!$B$1:$B$150,0)-1,ROWS($A$1:$A1))
copy down. Then copy the formula across as and when you add column. Excel will adjust the references.

I am attaching modified sample file.

[SamT]

Can't do it Formulamatically with that example because the Each Month's row count is different.

Either set each month to start on a line = to a multiple of 40 (+ 1) or put each month's report on a different sheet with the subtotal rows in the same rows on each sheet.

In the first case, January would start in row 1 and its' subtotal rows would be in Rows 36, 37, and 38. Add two blank rows and February would start in Row 41, and it's subtotals in Rows 76, 78, & 79, March in 81, & 116 to 119, etc.

The easiest method with your existing work book is to Select the entire column "G", press Ctrl+H, enter 120 in the top box and 136 in the bottom box, then press Replace All.

[Teeroy]

I'd suggest following.

In Vertical sheets column headings put dates and format them as "mmmm"
Sheet Vertical | Cell C1 = 8/1/2013;
Sheet Vertical | Cell E1 = 9/1/2013;
Sheet Vertical | Cell G1 = 10/1/2013;

Once this is done. In cell C2, put this formula:
=INDEX(Summary!$B$1:$AO$150,MATCH(Vertical!C$1,Summary!$B$1:$B$150,0)-1,ROWS($A$1:$A1))
copy down. Then copy the formula across as and when you add column. Excel will adjust the references.

I am attaching modified sample file.

Hi shrivallabha,

Very neat way of transposing the data using the output position with Index :thumb. You could also have used Row()-1 as the final term to get the same result (to me it's slightly easier to follow).

[shrivallabha]

Hi shrivallabha,

Very neat way of transposing the data using the output position with Index :thumb. You could also have used Row()-1 as the final term to get the same result (to me it's slightly easier to follow).

Thanks, Teeroy. I used ROWS because it is not volatile and avoided ROW as it is volatile. See Charles William's article here:
http://www.decisionmodels.com/calcsecretsi.htm

[Teeroy]

Thanks for the reference Shrivallabha, very interesting reading. It has led me to reconsider using Now().
I didn't find Row() listed as volatile (but some function definitions are contentious) so I checked the function using the spreadsheet on that website and Row() doesn't show to be volatile.

[pcarmour]

Hi Shrivallabha,

Fantastic, Amazing - That works really well, thank you so much.

:clap::clap:

And thank you Sam T and Teeroy for your time and comments.

[shrivallabha]

Thanks for the feedback pcarmour.

I didn't find Row() listed as volatile (but some function definitions are contentious) so I checked the function using the spreadsheet on that website and Row() doesn't show to be volatile.
I agree. I had read it somewhere to the following effect:

ROW() / COLUMN() functions when used without range reference act like volatile functions i.e. ROW() is volatile while ROW(A1) is not
I think it was somewhere on decisionmodels but I could not find it now. Probably my memory isn't serving me right :D