Excel issue - how to return data from a list
Closed
Katie Watkins
-
May 14, 2010 at 05:55 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 14, 2010 at 08:57 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 14, 2010 at 08:57 PM
Related:
- Excel issue - how to return data from a list
- Display two columns in data validation list but return only one - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to change your best friends list on snapchat to 3 - Guide
- My contacts list names - Guide
- Counter strike 1.6 cheats list - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 14, 2010 at 06:01 AM
May 14, 2010 at 06:01 AM
There is no formula that can do it. You have to have a macro to do that.
Are you looking for some sort of search feature, where on a sheet you enter a machine id and you get a sheet which has all the matches from all the sheets?
Are you looking for some sort of search feature, where on a sheet you enter a machine id and you get a sheet which has all the matches from all the sheets?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 14, 2010 at 08:57 PM
May 14, 2010 at 08:57 PM
Try this
Assumption:
1. The list of machines are on a sheet titled "Search List" and in column A
2. The search results will be posted on a sheet titled "Search Result"
3. The headers are on row 1
4. The search results are refreshed each time and previous search are deleted.
Assumption:
1. The list of machines are on a sheet titled "Search List" and in column A
2. The search results will be posted on a sheet titled "Search Result"
3. The headers are on row 1
4. The search results are refreshed each time and previous search are deleted.
Sub SearchResult() Dim sSrchLstSheet As String Dim lSrchLstStartRow As Long Dim lSrchLstEndRow As Long Dim Sheet As Object Dim sSearchFor As String Dim sResultSheet As String Dim lRsltSheetRow As Long Dim bScreenUpdating As Boolean 'current status of screen updating Dim bDisplayAlerts As Boolean ' show show warnings Dim iFilterCol As Integer bScreenUpdating = Application.ScreenUpdating bDisplayAlerts = Application.DisplayAlerts Application.ScreenUpdating = False On Error GoTo Error_Handle sSrchLstSheet = "Search List" lSrchLstStartRow = 1 sResultSheet = "Search Result" iFilterCol = 1 lSrchLstEndRow = Sheets(sSrchLstSheet).Cells(Rows.Count, "A").End(xlUp).Row On Error Resume Next If bDisplayAlerts Then Application.DisplayAlerts = False Sheets(sResultSheet).Delete If bDisplayAlerts Then Application.DisplayAlerts = True On Error GoTo Error_Handle Sheets.Add ActiveSheet.Name = sResultSheet lRsltSheetRow = 0 Do While (lSrchLstStartRow <= lSrchLstEndRow) sSearchFor = Sheets(sSrchLstSheet).Cells(lSrchLstStartRow, "A") For Each Sheet In Sheets If (Sheet.Name = sSrchLstSheet) Then GoTo Next_Sheet If (Sheet.Name = sResultSheet) Then GoTo Next_Sheet If (Sheet.Cells(1, Columns.Count).End(xlToLeft).Column = 1) Then If Sheet.Cells(1, 1) = "" Then GoTo Next_Sheet End If If (lRsltSheetRow = 0) Then Sheets(sResultSheet).Range("1:1") = Sheet.Range("1:1").Value lRsltSheetRow = 2 End If Sheet.Select Cells.Select If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter End If Selection.AutoFilter Field:=iFilterCol, Criteria1:="=" & sSearchFor, Operator:=xlAnd, Criteria2:="<>" lastrow = Cells(Rows.Count, iFilterCol).End(xlUp).Row If (lastrow > 1) Then Rows(2 & ":" & lastrow).Copy Sheets(sResultSheet).Range("A" & lRsltSheetRow).PasteSpecial lRsltSheetRow = Sheets(sResultSheet).Cells(Rows.Count, iFilterCol).End(xlUp).Row + 1 End If If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False Next_Sheet: Next Sheet lSrchLstStartRow = lSrchLstStartRow + 1 Loop GoTo End_Sub Error_Handle: MsgBox Err.Description End_Sub: Application.ScreenUpdating = bScreenUpdating Application.DisplayAlerts = bDisplayAlerts End Sub
May 14, 2010 at 06:04 AM
May 14, 2010 at 06:06 AM
May 14, 2010 at 06:11 AM
Have large data table ie
Machine name Software installed IP address user id
GB234W Microsoft Word 10.10.10.1 kwatkins
GB234W Microsoft Excel 10.10.10.1 kwatkins
GB235W Microsoft Word 10.10.10.2 jsmith
Now in my list of machine ID's, I have GB234W listed, but not GB235W, so I only want to see the rows containing data on that machine...
Does that help?
May 14, 2010 at 07:04 AM
May 14, 2010 at 07:06 AM