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":

	A	B	C	D	E	F	G	H	I	J
1	NOTES: UNLESS OTHERWISE SPECIFIED	GENERAL ARRANGEMENT	INSTALLATION	ASSEMBLY	CABLE ASSEMBLY	FABRICATION	ALTERED ITEM	SCHEMATIC	PIPING DIAGRAM	VENDOR ITEM
2	INTERPRET DRAWING PER ASME Y14.1	X	X	X	X	X	X	X	X	X
3	BREAK SHARP EDGES .005 MAX					X	X			X

<tbody>
</tbody>

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