We help IT Professionals succeed at work.

Insert Formulas

Jagwarman
Jagwarman asked
on
151 Views
Last Modified: 2014-05-12
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
Comment
Watch Question

Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
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.
Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Glenn that does it for me

Author

Commented:
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 RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
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?

Author

Commented:
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

Author

Commented:
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 RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
thanks Glenn
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.