Thanks Luke

I have checked it and it seems to stick to the row I defined initially so I
can insert and delete rows to my hearts content and the block of data for
month X is now pretty much absolute

Cheers

"Luke M" wrote:

You might want to recheck that. That formula will always return a value of
29, which doesn't seem to fit with what you described in your original
problem.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MurrayBarn" wrote:

Dont worry about helping - have found the solution:

=cell("row",f29)

Regards



"MurrayBarn" wrote:

I have a spreadsheet that has twelve sections (ie one per month of financial
year). In each section there is data pasted in from another worksheet and
below that data is a summary table that uses various formulas to sum the
above data based on variables in the data. So, in the below formula, in F29 I
manually type in the starting row for the data, G29 has the ending row
manually typed in, D31 is the variable that states which data to sum and
Column E has the data that D31 is looking for.

=SUMIF(INDEX($E:$E,F29):INDEX($E:$E,G29),D31,INDEX ($I:$I,F29):INDEX($I:$I,G29))

The formula works very well, HOWEVER some months have much more data than
others so my bookkeeper has to manually change the row numbers (defined for
Month X in F29 and G29 above). Every now and then she forgets to change the
row numbers or has a typo which means I have to check this spreadsheet every
month.

My question is, what is the formula that I can put in F29 and G29 (and the
corresponding cells for each other month) that will always return the
starting row and ending row of month X even if rows in between are added or
deleted or if rows in a prior month are changed.

In other words say April's data starts on row 121 and ends on row 149. So I
manually type in 121 and 149 in the appropriate "F and G 29". Later, say
March gets new data and I have to insert rows for March. Now April starts on
row 126 and ends on 155, but the summary box still shows 121 and 149 until I
change it manually. How do I do it automatically?