Data Validation =INDIRECT function

Resolved · Medium Priority · Version 2010

Fri 8 Dec 2017, 14:54 Delegate Michael said...

Michael has attended:
Excel Advanced course

Data Validation =INDIRECT function

Hi

I am trying to create dropdown boxes where the options given in one column affect the available dropdown options in the next column.

I have all the options in one sheet and have managed to pull through the data in the first column to the dropdown using the =INDIRECT(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-2)) function however when trying to pull through the data for the next column using the =INDIRECT(IF(C16<>"-",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C16," ",""),"(",""),"&",""),"-",""),",",""),CONCATENATE(A16,"SEC"))) function, it does not pull though.

Is there a way I can send you the spreadsheet to have a look at?

I need it set so that it automatically adds dropdown options when I add them to the other sheet.

Thanks

Mike

Fri 8 Dec 2017, 15:34 Trainer Jens said...

RE: Data Validation =INDIRECT function

Hi Michael,

Thank you for the forum question.

Yes you can send the workbook to:

info@stl-training.co.uk

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 8 Dec 2017, 15:42 Trainer Jens said...

RE: Data Validation =INDIRECT function

Hi Michael,

I am not sure what you mean by "I need it set so that it automatically adds dropdown options when I add them to the other sheet"

Add what?

You can reuse your Datavalidation rules by copy the range where you have the rules and paste the rules using Paste Special.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 21 Dec 2017, 11:04 Trainer Jens said...

RE: Data Validation =INDIRECT function

Hi Michael,

I have sent you an email with attached example.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 28 Dec 2017: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Changing the Tab Colour of an Excel 2010 Worksheet

Did you know you could give the tabs in your worksheet different colours?

This is particularly useful when organizing all your worksheets relating to a particular period or year, for example.

Right click a tab
Select Tab Colour
And choose your favourite colour!

View all Excel hints and tips