Standard notes wizard

[keelaboosa]

I would like to set up a Standard Notes wizard for our drafters.

For my example, we make three different types of drawings: Assembly, Welding, and Sheet Metal. I would set up a table with notes in the rows and drawing types in the headers.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NOTE[/TD]
[TD]ASSY[/TD]
[TD]WELD[/TD]
[TD]SHTMTL[/TD]
[/TR]
[TR]
[TD]INTERPRET DRAWING PER ASME Y14.5[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]WELD PER ASW D1.1[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]BAG AND TAG COMPONENTS[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]ASSEMBLE PER INSTRUCTION XXXX[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

I would then have a cell with data validation to select the drawing type. From the value in that cell, I want a table with the appropriate note for the drawing type.

How would I go about this one?

 

[Yongle]

Re: Standard notes wizard help

I think this is better suited to VBA anyway.

Here is code - deliberately wordy to make it easy to follow (away from PC for a few days)

Code is triggered when value in Cell A1 changes

Code goes in sheet module of Results sheet (right-click on sheet tab \ View Code \ paste in window on right)

Private Sub Worksheet_Change(ByVal Target As Range)
    Const shName = "[COLOR=#ff0000]Hidden"[/COLOR]                                 '[COLOR=#ff0000]AMEND SHEET NAME[/COLOR]
    Dim ws As Worksheet
    Dim myTable As Range                                    'lookup range
    Dim ColumnNo As Long, RowNo As Long                     'in table of values
    Dim myCount As Long                                     'from 3 to 6 ie cells A3:A6

If Target.Address(0, 0) = "A1" Then
    On Error GoTo EventEnable
    Application.EnableEvents = False
    Me.Range("A3:A6").ClearContents                         'clear old values
    Set ws = Sheets(shName)
    Set myTable = ws.Range("A1").CurrentRegion              'contiguous range starts at cell A1
    With WorksheetFunction
        ColumnNo = .Match(Target, myTable.Rows(1), 0)       'look for match for A1 value
    End With
    myCount = 2
    For RowNo = 2 To myTable.Rows.Count
        If myTable(RowNo, ColumnNo) = "Y" Then
            myCount = myCount + 1                           'increases by 1 when "Y" is found
            Me.Cells(myCount, "A") = myTable(RowNo, "A")    'place value for next "Y" in A3:A6
        End If
    Next RowNo
    
End If
EventEnable:
Application.EnableEvents = True
End Sub

Sub ee()
'(attached to a shortcut) this can be very helpful if Events are inadvertently disabled
Application.EnableEvents = True
End Sub

Sheet "Hidden" looks like this - cells A1:D5

<tbody>
[TD="class: xl63"]NOTE[/TD]
[TD="class: xl63, align: center"] ASSY
[/TD]
[TD="class: xl63, width: 80, align: center"] WELD
[/TD]
[TD="class: xl63, width: 99, align: center"] SHTMTL
[/TD]

[TD="class: xl63, width: 356"]INTERPRET DRAWING PER ASME Y14.5[/TD]
[TD="class: xl63, width: 66, align: center"]Y[/TD]
[TD="class: xl63, width: 80, align: center"]Y[/TD]
[TD="class: xl63, width: 99, align: center"]Y[/TD]

[TD="class: xl63, width: 356"]WELD PER ASW D1.1[/TD]
[TD="class: xl63, width: 66, align: center"]N[/TD]
[TD="class: xl63, width: 80, align: center"]Y[/TD]
[TD="class: xl63, width: 99, align: center"]N[/TD]

[TD="class: xl63, width: 356"]BAG AND TAG COMPONENTS[/TD]
[TD="class: xl63, width: 66, align: center"]N[/TD]
[TD="class: xl63, width: 80, align: center"]Y[/TD]
[TD="class: xl63, width: 99, align: center"]Y[/TD]

[TD="class: xl63, width: 356"]ASSEMBLE PER INSTRUCTION XXXX[/TD]
[TD="class: xl63, width: 66, align: center"]Y[/TD]
[TD="class: xl63, width: 80, align: center"]N[/TD]
[TD="class: xl63, width: 99, align: center"]N[/TD]

</tbody>

Results sheet columnA

<tbody>
[TD="class: xl65"] Weld [/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"]INTERPRET DRAWING PER ASME Y14.5[/TD]

[TD="class: xl66"]WELD PER ASW D1.1[/TD]

[TD="class: xl66"]BAG AND TAG COMPONENTS[/TD]

[TD="class: xl66"][/TD]

</tbody>