Dynamic Ranges from Different Sheets Question

dynamic_ranges_and_sheets_question.xlsx

Hello all

I´m having problems with the max formula using dynamic ranges in different sheets.
I need to use ranges involving the last rows of different sheets, but the problem is that each of them are updated daily (new rows are added).

Please check the attached file for a more detailed explanation of the problem.

I´ll highly appreciate any help you can provide.

Thanks in advance.

Convert each of the Symbol sheets to Tables
Rename the Table as "tab" & Symbol
For example: tabLYG, tabMTU, etc

Formula:

=MAX(INDEX(INDIRECT("tab" &  Tabla1[[#This Row],[Symbol]] & "[Close]"),

         MATCH(MAX(INDIRECT("tab"& Tabla1[[#This Row],[Symbol]]& "[Date]")),

         INDIRECT("tab" & Tabla1[[#This Row],[Symbol]] & "[Date]"),0)-251):

         INDEX(INDIRECT("tab" &  Tabla1[[#This Row],[Symbol]] & "[Close]"),

         MATCH(MAX(INDIRECT("tab" & Tabla1[[#This Row],[Symbol]] & "[Date]")),

         INDIRECT("tab" & Tabla1[[#This Row],[Symbol]] & "[Date]"),0)))

See the attached updated example.

Regards, TMS

The reason for suggesting Tables is that they automatically maintain Named Ranges for the columns. I guess you could create Dynamic Named Ranges for each sheet, but I think you'd need two for each sheet. One for the date column and another for the Close Price. A lot of Dynamic Named Ranges.

I don't think the software would/should treat a sheet with a Table any differently. And you could easily convert each sheet to a Table using VBA.


Regards, TMS

Ahhh, ... Money and mouth, huh?

Give me a while, I'll have a look at it.

Regards, TMS

OK, this is the code to add the Tables and Formulae:

' Module: mAddTables

' dynamic_ranges_and_sheets_question TMS v3.xlsm
' author:    TMShucks Excel Aid

' title:    Dynamic Ranges from Different Sheets Question
' thread:   http://www.excelforum.com/excel-general/991616-dynamic-ranges-from-different-sheets-question.html
' from:     nicoan

Option Private Module
Option Explicit

Sub sAddTables()
Dim sh As Worksheet
Dim lLR As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

On Error GoTo lblFormula

' convert ranges to Tables on Symbol sheets
' loop through each sheet in the workbook sheets collection
For Each sh In ThisWorkbook.Worksheets
    ' applies to everything except Control sheet
    If sh.Name <> "Control" Then
        With sh
            ' determine last row of data on the sheet
            lLR = .Range("A" & .Rows.Count).End(xlUp).Row
            ' insert a Table
            ' and name it based on the worksheet name (Symbol)
            .ListObjects.Add( _
                xlSrcRange, _
                .Range("$A$1:$F$" & lLR), , _
                xlYes) _
                    .Name = "tab" & sh.Name
        End With
    End If
Next 'sh

lblFormula:
On Error GoTo 0
On Error GoTo lblExit

' add formulas to Control sheet
With Sheets("Control")
    ' having named the Tables using the sheet (Symbol) name
    ' we can use INDIRECT to reference the Symbol
    .Range("Tabla1[All Time Max Price]").FormulaR1C1 = _
        "=MAX(INDIRECT(""tab"" & Tabla1[[#This Row],[Symbol]]  & ""[Close]""))"
    .Range("Tabla1[1Y Max Price]").FormulaR1C1 = _
        "=MAX(INDEX(INDIRECT(""tab"" &  Tabla1[[#This Row],[Symbol]] & ""[Close]"")," & Chr(10) & "              MATCH(MAX(INDIRECT(""tab""& Tabla1[[#This Row],[Symbol]]& ""[Date]"")),INDIRECT(""tab"" & Tabla1[[#This Row],[Symbol]] & ""[Date]""),0)-251):" & Chr(10) & "              INDEX(INDIRECT(""tab"" &  Tabla1[[#This Row],[Symbol]] & ""[Close]"")," & Chr(10) & "              MATCH(MAX(INDIRECT(""tab"" & Tabla1[[#This Row],[Symbol]] & ""[Date]"")),INDIRECT(""tab"" & Tabla1[[#This Row],[Symbol]] & ""[Date]""),0)))"
    ' just add some formatting to the data for consistency
    .Range("Tabla1[[All Time Max Price]:[1Y Max Price]]").NumberFormat = "0.00"
End With

lblExit:
On Error GoTo 0

With Application
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

Please see the attached updated workbook to try it. I don't know how it will perform with 400 sheets ...

Regards, TMS

I note that you have been logged on the forum since I provided a solution for you.

Does it meet your needs?




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.