Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Table Formula question

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Yorkshire
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Table Formula question

    I have vlookup formula in a table which I have changed the table array details for to a new array. The table array was changed following an upgrade of our intranet where the original information came from.

    I have amended the formula as it is in about 100 work books using a macro which opens the workbooks and then does a simple replace. This has worked OK and updated the details as required. However if a new line is inserted in the table it keeps the old table array details in the lookup. Is there a way of forcing the table to use the new array details rather than the old ones when lines are inserted.

    By the way the lines are inserted using a macro as the workbooks and sheets are protected, I know I could add replace into the macro which would solve the issue but didn't really want to go down this route.

    StewartS

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Formula question

    Hi StewartS,

    Yes, there is a way to do this. If the formulas are coming back as something different, Excel is seeing the calculated column formula, which is written into the XML of the file automatically (see a quick write-up about it here: Remembering Table Formulas | EXCEL TABLES). To do this manually, you would need to enter the formula, and then* click the button to overwrite all cells with that formula.

    * Excel will only show this button under certain conditions. Such as, if you already have any value in a cell in the column, then you enter a formula, Excel doesn't want to assume it should just overwrite everything. However, if you amend the formula which is saved as the calculated column formula, Excel will go right to work.

    That being said, post your code and we'll amend it to work for you. With code I find it's best to use the Table objects instead of a regular Range object.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Yorkshire
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Formula question

    Zack Thanks for the response - at this stage I must say that your book is sitting on my desk and it has been very useful . Its just helped me redefine the way I loop through the workbooks.

    The existing formula is =IFERROR(VLOOKUP([@FUNC], lovs!h_prjnew ,2,FALSE),""), at present the named range for the array is at sheet level so is lovs!h_prjnew. Which was based upon the old intranet page details.

    The new Array FuncDet will be a Dynamic range using Offset and CountA to define the named range, I've done this to exclude some unneeded header details which come down from the web page. The new formula will be =IFERROR(VLOOKUP([@FUNC], FuncDet ,2,FALSE),""). This will be in a table called Detail on a sheet called Input and the formula is in a column called Function Code.

    The initial code I used within a loop was just a standard replace --- Cells.Replace What:="lovs!h_prjnew", Replacement:="FuncDet", _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
    False, ReplaceFormat:=False
    Which is within a loop which opens each workbook, unprotects the sheet, does the replace and then reprotects the sheet then saves and closes the workbook.

    The concept of the table is that a user enters a 4 character code in the Func column and the Vlookup brings back the description for that code. The details from the Intranet are updated each time the workbook is opened to ensure that the information is always up to date.

    I assume that the suggested revised code will change the formula details in the underlying xml as described in your blog.

    Thanks for your help.

    StewartS

  4. #4
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Formula question

    First of all, thank you very much for the kind words about the book. I greatly appreciate it, and certainly hope you enjoy it! It was a lot of fun to write.

    One of the benefits of Tables is having calculated columns. As I said, itís written into the XML of the file when entered, which is how the formula will get auto-completed in new rows added. Changing the calculated column formula can be tricky though. To do this, we donít need to do a Find/Replace, but just change the DataBodyRange.Formula, which should force Excel to re-enter the calculated column.

    The key is the whole column needs to be done at the same time. Technically a Find/Replace does each cell individually, even though itís one action. The manual way to do this would be to select the entire column (body only, no header, no footer), enter your formula, then press CTRL+ENTER to confirm for every cell at once. (Or clear all cells and confirm with just ENTER.)

    As I understand it, you have some sort of loop to iterate through multiple workbooks. Below is what I would use for each workbook in the iteration. My assumption is you will be able to change the objects in the code below for the objects in your code. So the TargetBook variable is the workbook variable in the iteration. Thereís a bit of redundancy and error checking in the code, like if the sheet exists, etc. You donít have to use all that if you donít want to, but I generally find itís better to err on the side of caution than have your code throw an error. But it can make for much longer code than originally anticipated.

    Below is a routine and three functions to assist. The code I would point you to is in the first routine. Copy/paste the other functions into your project as well. If you need help integrating this first routine into your code just let us know (and post your code as well).

    Code:
     Sub StewartTableCode()
    
        Dim TargetBook As Workbook
        Dim TargetTable As ListObject
        Dim TargetColumn As ListColumn
    
        Set TargetBook = ActiveWorkbook    '<- for testing purposes only, change to your iterative workbook
    
        ' If you're not already, I would turn off a few application settings prior to any looping...
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        ' Make sure the sheet exists
        If WorksheetExists("Input", TargetBook) Then
            ' Make sure the Table exists
            If TableExists("Detail", TargetBook.Worksheets("Input")) Then
                ' Make sure the "Function Code" column exists
                If TableColumnExists("Function Code", TargetBook.Worksheets("Input").ListObjects("Detail")) Then
                    ' Make sure the "FUNC" column exists
                    If TableColumnExists("FUNC", TargetBook.Worksheets("Input").ListObjects("Detail")) Then
    
                        ' Set our objects and enter the function, ensure the column is clear first just in case
                        Set TargetTable = TargetBook.Worksheets("Input").ListObjects("Detail")
                        Set TargetColumn = TargetTable.ListColumns("Detail")
                        TargetColumn.DataBodyRange.ClearContents
                        TargetColumn.DataBodyRange.Formula = "=IFERROR(VLOOKUP([@FUNC],FuncDet,2,FALSE),"""")"
    
                    End If
                End If
            End If
        End If
    
        ' Good to do in an iteration, just at the end before the next loop
        Set TargetBook = Nothing
        Set TargetTable = Nothing
        Set TargetColumn = Nothing
    
        ' Set everything back after running your code
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End Sub
    
    
    Function TableColumnExists(ByVal ColumnName As String, ByVal Table As ListObject) As Boolean
    '
    ' Returns True if the specified column name exists in the specified Table.
    '
    ' Author:       Zack Barresse
    '
    ' Syntax:       TableColumnExists(ColumnName,Table)
    '
    ' Parameters:   ColumnName. String. Required. Column name to check if it exists.
    '               Table. ListObject. Required. Table to look at.
    '
        On Error Resume Next
        TableColumnExists = CBool(Table.ListColumns(ColumnName).Index <> 0)
        On Error GoTo 0
    
    End Function
    
    
    Function TableExists( _
             ByVal TableName As String, _
             Optional ByVal TableSheet As Worksheet _
             ) As Boolean
    '
    ' Returns True if a Table exists with the specified name on the specified (or active) sheet.
    '
    ' Author:       Zack Barresse
    '
    ' Syntax:       TableExists(TableName,[TableSheet])
    '
    ' Parameters:   TableName. String. Required. Table name to check if it exists.
    '               TableSheet. Worksheet. Optional. Worksheet to look for Table in. The active sheet
    '               is used if not specified
    '
        If TableSheet Is Nothing Then
            If ActiveSheet Is Nothing Then Exit Function
            Set TableSheet = ActiveSheet
        End If
        On Error Resume Next
        TableExists = CBool(Len(TableSheet.ListObjects(TableName).Name) <> 0)
        On Error GoTo 0
    
    End Function
    
    
    Function WorksheetExists( _
             ByVal SheetName As String, _
             Optional TargetBook As Workbook _
             ) As Boolean
    '
    ' Returns True if the specified worksheet is found in the specified workbook.
    '
    ' Syntax:       WorksheetExists(SheetName,[TargetBook])
    '
    ' Parameters:   SheetName. String. Required. The name of the worksheet to test existence.
    '               TargetBook. Workbok. Optional. Specify the workbook to look in. If omitted
    '                   the active workbook will be used.
    '
        If TargetBook Is Nothing Then
            If ActiveWorkbook Is Nothing Then Exit Function
            Set TargetBook = ActiveWorkbook
        End If
        On Error Resume Next
        WorksheetExists = CBool(Len(TargetBook.Worksheets(SheetName).Name) <> 0)
        On Error GoTo 0
    
    End Function
    The only thing the above routine doesn't do is ensure the "FuncDet" named range exists. If you want a routine for that, I typically use another function, like this:

    Code:
    Public Function IsExistingRangeName( _
            ByVal WorkbookOrWorksheet As Object, _
            ByVal RangeName As String _
        ) As Boolean
    '
    ' Return True if the name exists in the workbook and is the name of a range, ' False otherwise.
    '
    ' Syntax
    '
    ' IsExistingName(Workbook, RangeName)
    '
    ' Workbook - The workbook in which to look for the name.
    '
    ' RangeName - The range name being sought.
    '
    ' oWorksheet - The worksheet on which the worksheet level name is defined.
    '   Optional. If omitted then the name is assumed to be a workbook level name.
    
        Dim Name As Name
    
        If Not TypeName(WorkbookOrWorksheet) = "Workbook" And Not TypeName(WorkbookOrWorksheet) = "Worksheet" Then
            Exit Function
        End If
    
        On Error Resume Next
        Set Name = WorkbookOrWorksheet.Names(RangeName)
        On Error GoTo 0
    
        If Not Name Is Nothing Then
            IsExistingRangeName = Name.RefersTo <> Name.RefersToR1C1
        End If
    
    End Function
    Let us know if this works. HTH
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Yorkshire
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Formula question

    Zack

    Thanks, I'm just looking at this so will let you know how it goes.

    StewartS

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Yorkshire
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Formula question

    Zack

    After a couple of attempts this worked fine for me, ( my loop and some additional details I added in creating problems rather than your code) thanks for the help.

    StewartS

  7. #7
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Formula question

    Thanks for letting us know. Glad it works for you.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •