INSERTING NEW LINES WITH OFFSET?

[Zippeee]

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.

 

[tactps]

I don't usnderstand why named ranges wouldn't work.

If there is a way to identify the absolute as you work through the macro, VBA may help.

It would be useful if you posted a copy of the spreadsheet (use Colo's HTML maker at the bottom of this page).

 

[Zippeee]

I guess I'm misunderstanding naming ranges or cells. I thought if I named a cell, that makes it an asolute reference.

It's entirely likely, too, that I'm not accurately describing what I need to do. Below please find a VERY simplified version of the sheet (which really has 29 columns and a minimum of 15 rows per section, possibly 100 sections per worksheet).

A B C D E F
1
2
3
4
5 TOTALS OF COMPUTATIONS FOR THE SECTION
6 BLANK ROW
7 TOTALS OF COMPUTATIONS (Rows 1-5)

In F1 the formula is D5*B1
In F2 the formula is D5*B2
In F3 the formula is D5*B3, etc.

I then copy this section so I have:

A B C D E F
6
7
8
9
10 TOTALS OF COMPUTATIONS FOR THE SECTION
11 BLANK ROW
12 TOTALS OF COMPUTATIONS (Rows 1-5 + Rows 6-10)

In F6 the formula is D10*B6
In F7 the formula is D10*B7
In F8 the formula is D10*B8, etc.

If I need more rows in a section, and copy, for example Row 8 and insert it above 9 I get this:

A B C D E F
6
7
8
9
10
11 TOTALS OF COMPUTATIONS FOR THE SECTION
12 BLANK ROW
13 TOTALS OF COMPUTATIONS (Rows 1-5 + Rows 6-11)

In F6 the formula is D11*B6 (Yippee!)
In F7 the formula is D11*B7 (Yippee!)
In F8 the formula is D11*B8 (Yes!)
In F9 the formula is D12*B9 (Arrrrrgggghhhh!!)
In F10 the formula is D11*B10

The result in column D will change every section (In Section 1 its A5/C5; Section 2, A10/C10; etc).

After long, painful, convoluted formulas everything was working until I had to test adding rows - and it doesn't matter where or how many rows I add in a section, the added rows are wrong.

Someone in my office said it's hopeless. Is this true?

Forgive the length and any resultant boredom.

 

[tactps]

I am still not quite getting it, but this may be what you are after:
=D$11*OFFSET(B1,ROW()-1,0)

 

[Zippeee]

Okay - NOW I'm REALLY embarrassed. I don't understand your suggestion. (I guess they'll let ANYONE try using Excel...)

Here is the "real" formula I'm using in the spreadsheet (what I referred to as "D5," "D10," and "D11":

=IF(ISERROR(G12*P27),"-",G12*P27)

How would I use the offset formula correctly?

If it matters, "P27" is:

=IF(ISERROR(E27/N27),"-",E27/N27)

E27 is: =SUM(C27-D27)
and
N27 is: =SUM(G27:J27:M27)

I just got ANOTHER vote of no confidence from my BOSS!

 

[tactps]

I'm completely lost now.

Can you actually post your worksheet using Colo's HTML Maker (see bottom of page).

Once I can see what is actually going on, I can probably give you the correct formulas.