Data validation list from table headers

[keelaboosa]

Is it possible to select a subset of table headers to use as a data validation list in another cell?

I've got the following table named "Notes":

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]NOTES: UNLESS OTHERWISE SPECIFIED[/TD]
[TD="align: center"]GENERAL ARRANGEMENT[/TD]
[TD="align: center"]INSTALLATION[/TD]
[TD="align: center"]ASSEMBLY[/TD]
[TD="align: center"]CABLE ASSEMBLY[/TD]
[TD="align: center"]FABRICATION[/TD]
[TD="align: center"]ALTERED ITEM[/TD]
[TD="align: center"]SCHEMATIC[/TD]
[TD="align: center"]PIPING DIAGRAM[/TD]
[TD="align: center"]VENDOR ITEM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]INTERPRET DRAWING PER ASME Y14.1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BREAK SHARP EDGES .005 MAX[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]

I would like to use all except the first header as a data validation list. I've tried

=OFFSET(NOTES[#Headers],0,1,1,COUNTA(NOTES[#Headers])-1)

which evaluates to

={"GENERAL ARRANGEMENT","INSTALLATION","ASSEMBLY","CABLE ASSEMBLY","FABRICATION","ALTERED ITEM","SCHEMATIC","PIPING DIAGRAM","VENDOR ITEM"}

but it fails when I try to use the formula as the data validation list.

Is there a way to do this without duplicating the header row as data in another table?

 

[markmzz]

Hi!

Try this:

1) Create the name myHeader with the formula below

=INDEX(NOTES[#Headers],2):INDEX(NOTES[#Headers],COLUMNS(NOTES))

2) After that, use the name myHeader in Data Validation list.


Markmzz

 

[keelaboosa]

Thank you Markmzz! Your suggestion works great. I tried setting my formula to a name and using that for the data validation and that works as well.

 

[markmzz]

Thank you Markmzz! Your suggestion works great. I tried setting my formula to a name and using that for the data validation and that works as well.

You're welcome and thank you for the feedback.

And I'm glad to help.

Markmzz