I need help. On this surface, this seems pretty easy, but I am blanking on how to do it, so I'm hoping someone else can give me the answer...
I have a list of data, that I sort in different ways from time to time. After I sort the data, I split up the data into categories, add a sub-total, then add a grand total at the bottom of the sheet.
Here is the issue, sometimes there are 5 categories, sometimes there are 25 categories, but always the same number of rows in the list, so sometimes categories will have 3 items in them, sometimes they will have 25 items. So, adding the sub-totals in very "labor-intensive." I would like to be able to add a code to my sub-total line that can search above it for the same code (that indicates that it found another sub-total line), and use an index or offset formula in combination with the subtotal formula, to create the sub-total formulas. Is this possible?
As an example, lets say column A is blank. Column B contains Account Numbers. Columns C - X contain Data. The dataset goes from row 5 - 200. After the first sort, I can insert lines after rows 11, 20, 25, 38 and 80. In each of those lines, I would like to paste an entire row that contains a value in column A that indicates it is a sum row, a formula in column B that calculates the # of rows above it until it reaches the previous row that contains the sum row indicator in column A (say variable: "X1"), and formulas in column C - X that sum from the current row to the current row - "X1"
Does this make sense? Is it possible? Thanks
I have a list of data, that I sort in different ways from time to time. After I sort the data, I split up the data into categories, add a sub-total, then add a grand total at the bottom of the sheet.
Here is the issue, sometimes there are 5 categories, sometimes there are 25 categories, but always the same number of rows in the list, so sometimes categories will have 3 items in them, sometimes they will have 25 items. So, adding the sub-totals in very "labor-intensive." I would like to be able to add a code to my sub-total line that can search above it for the same code (that indicates that it found another sub-total line), and use an index or offset formula in combination with the subtotal formula, to create the sub-total formulas. Is this possible?
As an example, lets say column A is blank. Column B contains Account Numbers. Columns C - X contain Data. The dataset goes from row 5 - 200. After the first sort, I can insert lines after rows 11, 20, 25, 38 and 80. In each of those lines, I would like to paste an entire row that contains a value in column A that indicates it is a sum row, a formula in column B that calculates the # of rows above it until it reaches the previous row that contains the sum row indicator in column A (say variable: "X1"), and formulas in column C - X that sum from the current row to the current row - "X1"
Does this make sense? Is it possible? Thanks
