Solved Vlookup with sum across multiple sheets

April 4, 2013 at 12:17:19
Specs: Windows 7
I am trying to create a summary sheet based on several sheets in my workbook. I say several, because the number will be changing throughout time. Therefore, I cannot easily create named ranges. Each sheet has the same layout- Column B contains Dates, C- Principal Payments, D-Interest, and F- Balance.

I cannot use a simple sumif function, because the starting date will not always be the same. I originally tried a UDF based on this post: ; however, that returns the first instance instead of the sum.

Unfortunately, I don't know much about VBA and can't alter the function on my own.

Any suggestions are welcome! Let me know if this wasn't clear and I can try and provide more information

See More: Vlookup with sum across multiple sheets

April 5, 2013 at 08:27:28
✔ Best Answer

As far as using a Named Range, you can create a dynamic named range to refer to all sheets via a couple of different methods.

This first method will create a Named Range across all sheets. StartSheet and EndSheet are the actual names of the first and last sheet in your workbook. As long as the name of the first and last sheet don't change, it will always include all sheets in between. In other words, as long as your new sheets are inserted between the first and last sheet, the named range will "update" to include the new sheet.


If you can't control where the new sheets will be added, or if they will always be added after the last sheet, then try this event triggered code:

First, create a Named Range using the method above so that it Refers to the first and last sheet and the desired cells. I used AllSheets as the Name.

Place this code in the ThisWorkbook module and it will fire whenever you add a new sheet, regardless of where you add it. It will reset the Named Range to include all sheets.

Make sure you update the R1C1 reference to reflect your desired range.

Note: If you move the last sheet from the last position to any other position, the Named Range will not change and will then refer to the first sheet through the new position of the sheet you moved. If you need to be able to move sheets arounds, specifically out of the last position, then we'll need a method that fires the code based on some other event so that the name always gets updated with the name of the last sheet.

Let me know if this works for you.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
  With ActiveWorkbook.Names("AllSheets")
'Note: The current R1C1 reference Refers To A1:C5
'You'll need to change that to match your range
    .RefersToR1C1 = "=Sheet1:" & Sheets(Sheets.Count).Name & "!R1C1:R5C3"
  End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 5, 2013 at 09:54:23
This is great! Thanks!

Report •
Related Solutions

Ask Question