# INSERTING NEW LINES WITH OFFSET?

#### Zippeee

##### New Member
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?

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### tactps

##### Well-known Member
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

##### New Member
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

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

#### Zippeee

##### New Member
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

##### Well-known Member
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.

Replies
5
Views
326
Replies
4
Views
316
Replies
6
Views
199
Replies
0
Views
219
Replies
3
Views
347

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,488
Messages
5,770,380
Members
425,613
Latest member
martinijr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back