Data Validation and VLookUp issues

[WaterRob]

I have worksheet with a data validation list.  This list is dynamic and is working fine:

=OFFSET('Sheet List'!$BL$1,1,0,MAX('Sheet List'!$BK:$BK),1)

Once you select an item from the list above I would like to create another validation list in the cell below that is also dynamic based on the selection above.  The data for this page is based on information supplied 30 different worksheets.  I cannot seem to get it to work.

The location of both data validation lists are on one worksheet and the validation list on another.

Alternatively I have tried using VLOOKUP in the second cell based on the selection of data validation list, however, depending on the data selection and the column of the data is  in I run into issues.  When the data validation selection changes the column lookup also needs to change.

[#2]

@WaterRob See: https://www.contextures.com/exceldatavaldependindextablesindirect.html

There are several methods detailed there, so pick which one works best for you.

HTH

[#3]

@WaterRob 

To obtain help easier, please attach your sample Excel file. 

[#4]

Thank you for your response! That link did the trick! Now need to have the Indirect Function not include the blank spaces in then end.

[#5]

Thank you for your reply, I was able to figure out using Indirect Function. Now just need to exclude the blanks at the end of the Validation List.

[#6]

Being volatile, INDIRECT is indubitably NOT the best function for you to use. Though I must congratulate you for seeing some light in the link provided to you, such light is insufficient to guide you through your desired outcome, unless you attach your sample file!