asked on

Insert Formulas

the attached is a copy of a spreadsheet that we use to balance/reconcile items. this is the first of a 3 stage process so on day one this form is completed. When opened the inputter may find they need more than the number of rows in the form so they will Press the Insert Rows button and insert rows in the appropriate section. This is fine on Day one as the formulas are already in place in the cells in columns 'N' and 'P' However, at the end of the process all of the details from Sheet1 are copied to Sheet 2 and my concern is the formulas may be lost or someone may add more rows etc. As a matter of control I would like to be able to insert the formulas again but now the 'Totals' rows may well be on different row.

Can an expert provide vba code that will be able to find the appropriate row and insert the formulas in 'N' and 'P' I am guessing the way to do it is find Book on row 19 and work from there, the last one being Balance which is the total of all the totals above . !

Many thanks
Insert-Totals.xlsx

Glenn Ray

You don't need VBA in order to keep the totals updated regardless of the number of rows. You can change the function in the total cells in columns N & P to determine the range of cells above them to sum up by looking for the category change in column B.

In order to work, there has to be an additional placeholder value inserted in cell B11. I used "x" and noted that the cell is formatted so that the font color already matches the fill color which is ideal.

So, the formula for the "Book" total - currently cell N19 - would be:
=SUM(INDIRECT("N"&MATCH("x",B:B,0)+1&":N"&ROW()-1))
and the related formula in column P would be:
=SUM(INDIRECT("P"&MATCH("x",B:B,0)+1&":P"&ROW()-1))

It basically looks for the total label above the current one and creates the sum range. Regardless of the number of rows inserted (or deleted) the SUM function should work.

I've attached a modified version of your workbook showing all the new functions inserted.

Regards,
Glenn
EE-Insert-Totals.xlsx

Jagwarman

ASKER

Hi Glenn

that's very clever I've not seen that fornula before. I think that will take care of the problem when I copy the sheet to a new sheet but I still have the problem of inserting the formula that will up up all of the total rows.

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

Thanks Glenn that does it for me

Jagwarman

ASKER

Hi Glenn in fact I have just discovered that when I paste the sheet1 to sheet2 the 'Balance' formula does not work as it is not pasting over Book, T_BS etc it is just using the cell formula. Am I doing something wrong or what ?

Thanks

Glenn Ray

Sorry about that. It sounds like you're cutting and pasting the range of data, rather than copying over an entire sheet. If you use the "Move or Copy Sheet" function to create a copy of the main sheet, localized versions of the range names will automatically be created and the Balance total formula will remain correct.

To make a copy (Excel 2010)
Menu: Home tab, Format (in Cells section), Move or Copy Sheet...
Select the location to move the sheet and check the "Create a copy" box.

Hope that helps,
Glenn

Jagwarman

ASKER

Hi Glenn,

the issue I have is that I can not 'Move or Copy' the sheet because the top 9 rows cannot be moved. I can only copy from B10 through T

Any Ideas?

Jagwarman

ASKER

to add to that dilemma I have also now discovered that even if I did copy/move any changes to the amounts I make in the new sheet to not get included in the total in the new sheet but if I make a change to an amount in the original sheet this is also picked up in the balance on the new sheet.

So I am thinking that maybe I should post a new Question as you solved the original and this is a whole lot different

Regards

Jagwarman

ASKER

If you are there Glenn I might have found the solution but need your expertise in coding.

Your above formula works great for adding formulas, I use the following code that finds the word then moves to the appropriate cell and puts the formula in that cell.

rngFound.Offset(0, 14).Select
rngFound.Offset(0, 14).Formula = "=SUM(INDIRECT(""P""&MATCH(""Trade Type"",C[-14],0)+1&"":P""&ROW()-1))"

I think I should be able to do the same with adding a name to the cell, so if I use the formula above something along the lines of

rngFound.Offset(0, 12).Select
ActiveWorkbook.Names.Add Name:="BK1" ??????? and this is where I get lost

can you assist?

Glenn Ray

Sorry that I'm just getting back to your questions.

I've put you on a "wild goose chase." If you create the balance formula initially by just referencing the subtotal cells above, you really don't need to use range names at all. In the example file you initially sent, the formula in cell N57 would just be
=N19+N29+N39+N49+N55

You can then copy the range anchored at B10:Txx and the formula would work fine without any conflicts (like seen with range names). The formula would also change appropriately as rows were added/removed above.

Again, with this solution, I don't think any coding would be necessary. However, if you did want to add a range name to a cell the easiest method is like so:

Range("N19").Name = "T_Book"

Open in new window

Jagwarman

ASKER

thanks Glenn