I'm working on a spreadsheet that, thanks to this forum, ALMOST works. I have to have the ability to copy a section of the spreadsheet and insert it at a designated insertion line. Each section is a subtotal and the grand total line has to change each time. Each section ALSO has a multiplier that will change based on new information, but the multiplier has to stay constant within the section.
Initially I had an absolute reference to the cell, but when copied, the section referred to the absolute and not the "new" absolute. I tried naming the cell, but the I had the same result. Finally, I used this formula:
=IF(ISERROR(G12*P27),"-",G12*P27)
=IF(ISERROR(G13*P27),"-",G13*P27)
=IF(ISERROR(G14*P27),"-",G14*P27)
etc.
On top of it all, I was tasked with making sure error messages don't show, hence the "IF and ISERROR formula.
When I pick up the section, the numbers change (as they should) to:
=IF(ISERROR(G28*P43),"-",G28*P43)
=IF(ISERROR(G29*P43),"-",G29*P43)
etc.
Everything works as it should, EXCEPT, if a user needs to insert new lines within a section, the formula does not work right (the "P" row cell reference changes - See below). We've provided 15 rows for data input, however, the user's need may exceed 15 - they may need dozens of new rows (unlikely, but possible).
=IF(ISERROR(G19*P35),"-",G19*P35) (for example)
I was trying to use an offset with the above formula, like one I used on Grand Total line:
=SUBTOTAL(9,G12:OFFSET(G49,-1,0))
but I can't make it work.
I'm a novice and I may not have built formulas the "right way" but everything has worked. Because a partner in the firm designated the format of the spreadsheet, I have to make it work in its present form. If there is a solution working around what I've already done, it would be great. Advice?
Thank you in advance.
Initially I had an absolute reference to the cell, but when copied, the section referred to the absolute and not the "new" absolute. I tried naming the cell, but the I had the same result. Finally, I used this formula:
=IF(ISERROR(G12*P27),"-",G12*P27)
=IF(ISERROR(G13*P27),"-",G13*P27)
=IF(ISERROR(G14*P27),"-",G14*P27)
etc.
On top of it all, I was tasked with making sure error messages don't show, hence the "IF and ISERROR formula.
When I pick up the section, the numbers change (as they should) to:
=IF(ISERROR(G28*P43),"-",G28*P43)
=IF(ISERROR(G29*P43),"-",G29*P43)
etc.
Everything works as it should, EXCEPT, if a user needs to insert new lines within a section, the formula does not work right (the "P" row cell reference changes - See below). We've provided 15 rows for data input, however, the user's need may exceed 15 - they may need dozens of new rows (unlikely, but possible).
=IF(ISERROR(G19*P35),"-",G19*P35) (for example)
I was trying to use an offset with the above formula, like one I used on Grand Total line:
=SUBTOTAL(9,G12:OFFSET(G49,-1,0))
but I can't make it work.
I'm a novice and I may not have built formulas the "right way" but everything has worked. Because a partner in the firm designated the format of the spreadsheet, I have to make it work in its present form. If there is a solution working around what I've already done, it would be great. Advice?
Thank you in advance.