Parts list based on Equipment

[Radical_Magic63]

I am trying to create a way of generating parts needed in inventory based on what equipment an area may have.

For Example:

I have several convenience stores in various states. 2 Stores have 1 type of fountain, 1 store a another and 3 have yet a third type.

How can I select the piece of equipment and then generate a list of parts needed to maintain that equipment.

I am still very new to spreadsheets and have no experience with arrays and table other than old school BASIC (yea I'm that old).

Appreciate any help.

Thanks

Radical

[Logit]

Each of the following examples will fit your needs. Here is a macro for only one of the attached workbooks:

Option Explicit

Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    

    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
Sheets("Index").Activate
Sheets("Index").Range("A1").Select
End Sub
Sub RenameTabs()
Dim ran As Range
Dim cel As Object
Dim LastRow As Long
Dim i As Integer

    Set ran = Worksheets("Index").Range("B2:B100")
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For Each cel In ran

        For i = 1 To LastRow
            If IsEmpty(Cells(i, 1)) Then
            Else
                If cel.Value <> "" Then
                    
                    ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
             
                    ActiveSheet.Name = CStr(cel.Offset(0, 0).Value)
                Else
                    Exit For
                End If
               
            End If
        Next i
     Next cel
   Worksheet_Activate
    
End Sub

[Radical_Magic63]

Thanks for the replay. I was lucky and found a video on you tube for what I was looking for. As I said I'm a complete novice but was able to adapt what he did for my purpose.

Now what I'm trying to do is confine my lists to Columns A-E in the Selector sheet but need the Equipment list below that to shift up or down adapting to the Contents when they Spill in or out.

I attached my work for reference

Again thanks for the help

[JohnTopley]

Can you please update your profile as you are obviously not using Excel 2003!

[Radical_Magic63]

Done

Sorry about that

[Fluff13]

Is this what you want

=LET(f,SORT(FILTER(EquipmentData,(EquipmentData[Equipment]=A2)+(EquipmentData[Equipment]=G2),"")),IF(f="","",f))

[Radical_Magic63]

Sorry but thats not it. If you look at my example you will see two sets of searches. 1 in column A-E te other in G-K. I want all my searches confined to A-E. The problem is that I dont know how many rows each list will generate and was wondering if the rows can be self shifting up or down depending on the length of the list.

[Fluff13]

If you mean you want the data validation in G2 to go underneath the first filter formula & move up/down, then that is not possible.
If that is not what you mean, can you please post another workbook that shows what you want.

[Radical_Magic63]

Thats exactly what I was looking for.

Thanks anyways.

[Fluff13]

Glad to help & thanks for the feedback