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.

NOTE	ASSY	WELD	SHTMTL
INTERPRET DRAWING PER ASME Y14.5	Y	Y	Y
WELD PER ASW D1.1	N	Y	N
BAG AND TAG COMPONENTS	N	Y	Y
ASSEMBLE PER INSTRUCTION XXXX	Y	N	N

<tbody>
</tbody>

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 would then have a cell with data validation to select the drawing type

does dropdown in cell give 3 items (Assembly, Welding, and Sheet Metal)?

From the value in that cell, I want a table

where does the table go?

with the appropriate note for the drawing type

what is the appropriate note?

Please show how you want the output to appear
thanks

 

[keelaboosa]

Re: Standard notes wizard help

A hidden sheet would have the notes table, and a visible sheet would display the generated notes. Cell A1 would have data validation for drawing type. Appropriate notes would display from A3 and fill down:

	A
1	ASSY
2
3	INTERPRET DRAWING PER ASME Y14.5
4	ASSEMBLE PER XXXX

<tbody>
</tbody>

or

	A
1	WELD
2
3	INTERPRET DRAWING PER ASME Y14.5
4	WELD PER AWS D1.1
5	BAG AND TAG COMPONENTS

<tbody>
</tbody>

or

	A
1	SHTMTL
2
3	INTERPRET DRAWING PER ASME Y14.5
4	BAG AND TAG COMPONENTS

<tbody>
</tbody>

 

[Yongle]

Re: Standard notes wizard help

Here is one way using HLookUp
- values in A3 , A4 and A5 change to table values when A1 changes


Hidden Sheet
Range A1:C5 in HiddenSheet named as myTable (Named Range) looks like this (Put a space in the empty cells to avoid HLookUp returning 0)

ASSY	WELD	SHTMTL
INTERPRET DRAWING PER ASME Y14.5	INTERPRET DRAWING PER ASME Y14.5	INTERPRET DRAWING PER ASME Y14.5
	WELD PER ASW D1.1
	BAG AND TAG COMPONENTS	BAG AND TAG COMPONENTS
ASSEMBLE PER INSTRUCTION XXXX

<tbody>
</tbody>

Other Sheet
In A2
Data Vaildation source
WELD,ASSY,SHTMTL


In A3
=HLOOKUP($A$1,myTable,2,0)
In A4
=HLOOKUP($A$1,myTable,3,0)
In A5
=HLOOKUP($A$1,myTable,4,0)
In A6
=HLOOKUP($A$1,myTable,5,0)

 

[keelaboosa]

Re: Standard notes wizard help

I could do something similar with slicers,



but the goal is to have a continuous single column table with notes appropriate only for the selected drawing type. Your solution requires a column for every drawing type (there are more than three in reality), and puts a blank line if the note doesn't belong with that drawing type.

I was thinking something along the lines of an array formula that returns the note whose value is "Y" in the column with the header matching the selected drawing type. Just can't figure it out!

 

[Yongle]

Re: Standard notes wizard help

Does it really matter how many columns are used on a hidden sheet if the solution is simple? - they do not cost anything and will not be seen

Will provide an alternative tomorrow, if none has appeared overnight

 

[keelaboosa]

Re: Standard notes wizard help

Looking back, I see what you were after. The notes master table should have each discrete note written only once to avoid typing or copy-paste errors and instead have a simple Y or N to indicate if it's appropriate for the drawing type.

 

[Yongle]

Re: Standard notes wizard help

I think you probably need an array formula to do what you want in the way you want and that is not one of my strengths! I withdraw from this thread.

 

[Yongle]

Re: Standard notes wizard help

This tested me to my limit!

Data Validation list in A1
Assy,Weld,ShtMtl

Array formula in A3
- confirm with {CTRL}{SHIFT{ENTER} and copy down
=IFERROR(INDEX(Hidden!$A$2:$A$5,SMALL(IF("Y"=OFFSET(Hidden!$B$2:$B$5,,MATCH($A$1,Hidden!$B$1:$D$1,0)-1),ROW(Hidden!$A$2:$B$5)-ROW(Hidden!$B$2)+1),ROW()-2)),"")

Table
Your original table (post1) placed in cells A1 to D5 in sheet named Hidden

Critical
- Data Valuation list must EXACTLY match header values
- and are used in this bit of the formula MATCH($A$1,Hidden!$B$1:$D$1,0)-1
- The sequence of table headers is critical
- Assy returns 0, Weld returns 1, ShtMtl returns 2 which is then used in OFFSET
- to add another drawing type adopt the same logic

 

[keelaboosa]

Re: Standard notes wizard help

Thanks for the reply, Yongle. I couldn't get it to work, but honestly I think this is better suited to VBA anyway.