ALL YOUR EXCEL NEEDS
|FREE Excel STUFF|
See also: Dynamic Named Ranges | Vlookup | Hlookup Formula | Left Lookup in Excel | Excel Lookup Functions | Multi-Table Lookup | Dynamic Excel Lookups
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money
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;
Or, if don't mind see formula errors IF invalid argument criteria is used, it is simply;
<< Back to Excel Formulas Index << Back to Excel Named Ranges
See Also: Multi-Selected ListBox & Loop through all Controls on a UserForm & Move ListBox Item Up/Down
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.
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
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 [email protected] 31 days after purchase date.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft