If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Keeping row number after inserting lines
Two ideas. The first would be to use the MATCH function to find a certain
date within column F, giving you the row number you need. e.g., =MATCH(F29,I:I,0) Where F29 contains your start date. or, rearrange your formula and simplyify it with SUMPRODUCT. Assuming your dates are in column A, lets say your start and end dates are in F29 and G29 respectively. =SUMPRODUCT(--($A$2:$A$1000=F29),--($A$2:$A$1000=G29),--($E$2:$E$1000=D31),$I$2:$I$1000) Note that with SUMPRODUCT, all array sizes must be equal, and you can't call out the entire column unless using XL 2007. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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? |
#3
|
|||
|
|||
Keeping row number after inserting lines
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? |
#4
|
|||
|
|||
Keeping row number after inserting lines
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? |
#5
|
|||
|
|||
Keeping row number after inserting lines
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? |
Thread Tools | |
Display Modes | |
|
|