[ms office - 2013] How to search for something in Excel

[abillybob]

This may sound like a complete noob question and I have tried Googling it but can't find the specific answer I need. Basically my Line manager had a look at the inventory I created and didn't like how it was made.

I have two Excel Spreadsheet (1 for User Inventory and 1 for Room inventory) both of which have multiple workbooks that coincide to the name of either the User or Room devices are assigned to.

 

Is there anyway I can make Excel find all words such as iPad and list every iPad for me in the Spreadsheet?

 

He wants me to list everything in one big blob on one worksheet so it can be ordered alphabetically. I'd rather it not be like this because it'd just be a mess in my eyes.

[pcstru]

I think he is right. You are mixing up data storage and presentation, which always makes analysis very difficult. If you put the data in one 'table', you can then use filtering to hide data you don't want to see.

 

I don't think there is an easy way to search through multiple workbooks. You could code up a macro to do it.

[abillybob]

Oh right ok That makes me sad it was so pretty and organised and now I'm having to just putt in one big blob *sigh*

[featured_spectre]

Try this.

 

Sub Find_Data()
' Written by Barrie Davidson
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub

 

I didn't make it, I found it elsewhere, but its useful and does what you're asking..

[WayTooTricky]

You can just select multiple worksheet and control-F in the usual way. That'll scroll through the different tabs but only in the one file.