Standard notes wizard

keelaboosa

New Member
Joined
Apr 3, 2018
Messages
35
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.
NOTEASSYWELDSHTMTL
INTERPRET DRAWING PER ASME Y14.5YYY
WELD PER ASW D1.1NYN
BAG AND TAG COMPONENTSNYY
ASSEMBLE PER INSTRUCTION XXXXYNN

<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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

keelaboosa

New Member
Joined
Apr 3, 2018
Messages
35
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
1ASSY
2
3INTERPRET DRAWING PER ASME Y14.5
4ASSEMBLE PER XXXX

<tbody>
</tbody>

or

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

<tbody>
</tbody>

or

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

<tbody>
</tbody>
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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)
ASSYWELDSHTMTL
INTERPRET DRAWING PER ASME Y14.5INTERPRET DRAWING PER ASME Y14.5INTERPRET DRAWING PER ASME Y14.5
WELD PER ASW D1.1
BAG AND TAG COMPONENTSBAG 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)
 
Last edited:

keelaboosa

New Member
Joined
Apr 3, 2018
Messages
35

ADVERTISEMENT

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!
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 3, 2018
Messages
35

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

keelaboosa

New Member
Joined
Apr 3, 2018
Messages
35
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.
 

Forum statistics

Threads
1,136,854
Messages
5,678,141
Members
419,746
Latest member
tysonboy82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top