Error in cascading (dependent) Excel drop down list

Hey folks,
We have a working excel workbook were we have cascading (dependent) Excel drop down list. We select a shaker, to which we again select a fitting screen (screen name are different depending on supplier).
To this we have added new shaker/screens before with no issues. Now however, when we added an 8th item, it comes up in the main drop down list, but the second is dead. There is no reaction when selecting the cell, all other selections work as before.

Under data validation/list we have the formula "=OFFSET(INDIRECT(SUBSTITUTE($C28;" ";""));0;0;COUNTA(INDIRECT(SUBSTITUTE($C28;" ";"")&"Col"));1)", when just opening it and closing a message appears stating "the Source currently evaluates to an error. Do you wan to continue". So obviously something is not right, just can't figure out what...

The tab with issue is "Screen request", choosing "Derrick Dual Pool 600_Shaker" in cell C28 I can't get part number selections in cell D28. Choosing other shaker type works fine. Se tabs "Screen" and "ScreenPivot" for drop down selections. (this in an old file and has had multiple owners over the years...)

Thanks in advance.
Tigergutt

Please attached samples file. Check yellow banner at the top of page.

Hey Kokosek,
I know that is best and i normally do, but it's a company file so I was a little reluctant. I got the go-ahead so will attach and add some new comments to original post.

You've replaced space with "" so range for Derrick Dual Pool 600_Shaker have to be named DerrickDualPool600_Shaker.

Check attached file.

My goodness, so it was just the name in "name manager" that was wrong?

Thank you so much for this, much appreciated and lesson learned!

You welcome.
I am not sure that creating ranges for all possible products is a good idea.
Maybe you should consider something like:

Formula:

Please Login or Register  to view this content.

for data validation in range D28:D44?
I've put 50 as example it can be also counted dynamically (as below)
EDIT: (a bit monster but works):

Please Login or Register  to view this content.

Thanks again.
Yes I think this first formula makes more sense and should make it easier to add new shakers down the line. The last one looks a bit too complex for us I'm afraid

(I tied to duplicate what you had done in original sheet, but I can't make it work so I didn't get it 100% sadly, but I thank you for uploading the file so we can continue using this going forward )

You can apply first formula into your original sheet because headers for columns in Sheetpivot has been changed a bit. Take a look and find out difference with headers name between your original and attached file.
First suggested formula indeed is easy but it create range with 50 rows even for list which contains much less rows. This 'monster' generates range with checking (COUNTA) how many rows in needed.