How to generate dynamic, cascade drop down with special characters in the data values

Thank you so much for quick response. It works perfectly for one cell . i.e C12.
How do I make this dynamic? what changes should I make for the above formula so that what ever is selected from B12 to B16 should dynamically show the corresponding instrument in C12 to C16.

Also how do I create formula for RNA details as it has multiple levels?

Thanks again

You're welcome. Glad to help!

For the other cells in tange C12:C16, I'm afraid you'll have to create named ranges, such as the one I created.
For the next cell, the named range for data validation would be e.g., Instrument2, with this formula:
=OFFSET(PCRInstrument!$B$2,MATCH(Form!$B$13,PCRInstrument!$A$3:$A$16,0),0,COUNTIF(PCRInstrument!$A$3:$A$16,Form!$B$13),1)
Note that I change the cell reference only (Form!$B$12 to Form!$B$13), marked in red above.
Next down would be Form!$B$14, and so on.
I know that this is too laborious, so I'm trying to find an easier workaround.

One that might work would be to use only one named range e.g., the existing "Instrument", with the same formula, but not locking the row on the reference to Form!$B12 (without the second $), so it would be like this:

Formula:

Please Login or Register  to view this content.

If you use this, follow these steps and you may be fine:
1. Do data validation as described in my first post
2. Still on that cell C12, click Name manager in the Formulas ribbon
3. Select the named range Instrument
4. Check the formula in the Refers to box below. It MUST read
=OFFSET(PCRInstrument!$B$2,MATCH(Form!$B$12,PCRInstrument!$A$3:$A$16,0),0,COUNTIF(PCRInstrument!$A$3:$A$16,Form!$B$12),1)
5. Copy cell down, just as you would copy a formula down. It will replicate the data validation parameters in those cells down to C16.
6. Click on cell C13 and repeat steps 2, 3 & 4 above. In this case, the Form!$B$12 parts MUST read Form!$B$13.
At that point, you should be all set.
I must confess I never used this, so PLEASE DO run some test and make sure it will not fail down the road.

If I come up with something better, I'll get back here.
Also, I'm working on the RNA section. The attached file is an unfinished job just to get you started.

In the meantime, I hope some forum expert will come up with a better solution.

Take care.

Second solution for instruments list worked by removing the $ sign to locked the cell.

Also working on RNAdetails as it has big lists.

Thank you so much for your time and hard work

You're welcome. Glad it worked.
Thanks for the rep points added.

Not sure if I can consider this to completely solved. I tried to create list using Name Manager but some how the range for previous Name list are automatically changing when I create new list.
as per the rules of this forum I cannot ask the same question again so please advice.


Thank you so much,

I decided to do this with formulas and static named ranges instead of dynamic.
That way, I can create as many named ranges as I want at a time by, for exmple, selecting a range with 8 rows and 9 columns, with a formula-built name on left column from which the ranges will be named. Then on the Formulas ribbon I click on Create from selection, and mark Left column.

Also, in each data tab I made helper columns to populate with data from selections in the form tab.

Please run some test and hopefully it'll work better this time.

The RNA tab has more detail, so maybe you'll need to copy from there to complete the other tabs.

You`re welcome. Glad to help.

The first formula you mentioned =MID(@CELL("filename",A1),FIND("]",@CELL("filename",A1))+1,255)
All it does is to return the sheet name. I use it in a far out column and it can be hidden.
It's a little trick used, e.g., so formulas using the current sheet name will automatically be updated when copied to the next sheet.
I wrapped it in IFERROR because not all sheets names include space, so when one does, the space will be replaced by an underline character (_), so as not to mess with named ranges.

The second formula mentioned, =$W$1&$B$2&ROW(X1)
It concatenates:
1. Sheet name from cell w1
2. column heading from B2
3. The number 1, from the ROW function, so it will be 2, 3, 4, etc when I copy down.

That helps automation when creating meaningful named ranges, but not all headings in your sheets are consistent, so some adjusting was required.

Also, in the Form sheet, not all columns headings are consistent. So I had to make some adjustment in the formulas with INDIRECT function for the data validation formulas.

The formulas in the helper columns from clolumn H are array formulas, so when you edit, make sure to confirm with Ctrl+Shift+Enter instead of just Enter.

In case you have more questions, feel free to ask.

Take care.