Ozgrid Excel Newsletter. Excel Newsletter Archives

Excel Tips | EXCEL VBA TIPS

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Excel Dynamic Formulas

Rather than bog you Spreadsheet down with hundreds, if not thousands of formulas, use a single formula with flexible and changeable Arguments. In this example I will use the INDEX/MATCH functions nested together. You can also instruct the end formula to return the corresponding cell, to the match, on the left or right. However, the the same principles can apply to most Excel formulas.

In this example I have used the range A2:D14 as my table range. I have also made good use of Named Ranges and Data Validation. The single formula, in this case, ends being;

=IF(ISERROR(INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column))),"Invalid Criteria",INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column)))

Or, if don't mind see formula errors IF invalid argument criteria is used, it is simply;

=INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column))

Download Example

<< Back to Excel Formulas Index << Back to Excel Named Ranges

Excel VBA Macro Codes

Transfer Selected Rows Of Multi-Select & Multi-Column ListBox To Range Of Cells On Worksheet

The VBA code below will transfer all selected rows and columns of a Multi-Selected ListBox. On loading the UserForm, the ColumnCount Property of the ListBox is set to as many columns as the RowSource Property.

Download working example

Private Sub UserForm_Initialize()
    'Set ListBox ColumnCount to the same as RowSource
    ListBox1.ColumnCount = Range(ListBox1.RowSource).Columns.Count
End Sub




Private Sub TransferButton_Click()
 Dim lItem As Long, lRows As Long, lCols As Long
 Dim bSelected As Boolean
 Dim lColLoop As Long, lTransferRow As Long
    
 'Pass row & column count to variables
 'Less 1 as "Count" starts at zero
 lRows = ListBox1.ListCount - 1
 lCols = ListBox1.ColumnCount - 1
 
    'Ensure they have at least 1 row selected
    For lItem = 0 To lRows
        'At least 1 row selected
        If ListBox1.Selected(lItem) = True Then
            'Boolean flag
            bSelected = True
            'Exit for loop
            Exit For
        End If
    Next
     
    'At least 1 row selected
    If bSelected = True Then
        With Sheet1.Range("D1", Sheet1.Cells(lRows + 1, 4 + lCols)) 'Transfer to range
            .Cells.Clear 'Clear transfer range
            For lItem = 0 To lRows
                If ListBox1.Selected(lItem) = True Then 'Row selected
                  'Increment variable for row transfer range
                  lTransferRow = lTransferRow + 1
                    'Loop through columns of selected row
                    For lColLoop = 0 To lCols
                       'Transfer selected row to relevant row of transfer range
                       .Cells(lTransferRow, lColLoop + 1) = ListBox1.List(lItem, lColLoop)
                        'Uncheck selected row
                        ListBox1.Selected(lItem) = False
                    Next lColLoop
                End If
            Next
        End With
         Unload Me
    Else ' NO listbox row chosen
         MsgBox "Nothing chosen", vbCritical
    End If

End Sub

Download working example

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft