So I have a spreadsheet that has prices, quantities, and extended prices for items.
| Item | Price | Qty | Extended |
| A.... | 3.00. | 3.....| 9..............|
| B.... | 2.00. | 3.....| 6..............|
| C.... | 5.00. | 3.....| 15............|
| D.... | 4.00. | 3.....| 12............|
Where the Extended column uses the formula (=b2*c2) on down.
The issue that I'm having is that this spreadsheet constantly gets new additions in the middle of it. For instance:
| Item | Price | Qty | Extended |
| A.... | 3.00. | 3.....| 9..............|
| B.... | 2.00. | 3.....| 6..............|
| E.... | ........ | ...... | 15............ |
| C.... | 5.00. | 3.....| 12............|
| D.... | 4.00. | 3.....| ................|
I selected the cells A4-C4 and inserted cells above those. The problem arises in the amount of information that I have on this spreadsheet. It has over 4000 rows currently. As you can see in the example, the formula of cell D4, which is displaying 15, changed with the insertion from (=b4*c4) to (=b5*c5)
To get the result that I want, I just copy the formula down from a box above it and gt it to show the correct value:
| Item | Price | Qty | Extended |
| A.... | 3.00. | 3.....| 9..............|
| B.... | 2.00. | 3.....| 6..............|
| E.... | ........ | ...... | ............... |
| C.... | 5.00. | 3.....| 15............|
| D.... | 4.00. | 3.....| 12............|
Problem is, with 4000 rows to go through, the thing takes forever to get to the bottom and fully update.
I've tried every combination of using the $ in the formula to keep it constant, but to no avail. So finally, the Question:
Is there a way to make it so that when I insert cells into my chart, like I described, the reference still stays the same (=b4*c4) before and after inserting, without having to go through and recopy the formula every time?
Thanks ahead of time.
