INSERTING NEW LINES WITH OFFSET?

Zippeee

New Member
Joined
Jan 12, 2005
Messages
7
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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
Joined
Jan 12, 2005
Messages
7
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
Joined
Jan 20, 2004
Messages
3,460
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
Joined
Jan 12, 2005
Messages
7
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
Joined
Jan 20, 2004
Messages
3,460
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.
 

Forum statistics

Threads
1,148,528
Messages
5,747,221
Members
424,069
Latest member
kamkwok1hh

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top