|Oct 2019||% Share|
Hi guys, little bit of an involved question here. The above is an example of my table for sales. I currently have two reports: One standard one that just prints this exact table out (works fine), and one report where I need to move Apples from the Vegetables category into the Fruits category.
My current way of doing this is finding the value of Apples in column 1, cutting the entire row, and then finding the value of Oranges in column 1 and insert the Apples row above the Oranges row.
Now my problem is this: The totals, and % share are formulas. The Totals and % share will readjust if I add a row, but my Apple % share formula does not update when I move the row. So my Apple % share formula reads =B3/B$5*100, but when I move it it reads =B8/B$5*100 when I want =B8/B$10*100 (to reflect the market share of the new category it's in).
I can't just add a find&replace to my macro at the beginning of the script that changes the values from B$5 to B$10 because rows will be added as products are added. If I add Strawberries for example into the Fruits category the total line moves to row 11 instead of row 10. So I need to identify the row number that the Fruits Total is in and then be able to replace my Apple % Share formula with that row number so the % share reads correctly.
I thought I had an idea of how to do it but my efforts in searching up how to make it work have just confused me. I'd appreciate any help I can get! Thanks again.