Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Keeping row number after inserting lines
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? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting new lines with corresponding number | Excel Discussion (Misc queries) | |||
inserting new lines with number | Excel Discussion (Misc queries) | |||
Keeping Formulas the same when inserting columns | Excel Discussion (Misc queries) | |||
Inserting Lines or Copying lines with formulas but without data | Excel Discussion (Misc queries) | |||
Inserting new row but keeping formulae | Excel Worksheet Functions |