Dependent Validation Lists Not Cooperating.

[leaning]

Hello! I have these formmulas for Named Ranges

Sheet "Lists"

Please Login or Register  to view this content.

Sheet

Sheet "Commercial Review"

Please Login or Register  to view this content.

It is using the format from here: http://www.contextures.com/xldataval13.html.

The example had the independent-dependent in two columns, vice two rows like I have so maybe that is where the problem is.

Any help appreciated in making this work!

Respectfully,

Lost

[JBeaucaire]

All your named ranges needing tweaking:

Please Login or Register  to view this content.

[leaning]

Sir,

I took your example from your site, moved the cells to where I need them, and applied the fornulas from this post.


Independent is working. Dependent is not and just shows the error message.

??

Thanks for helping!

Respectfully,

Lost

[Haseeb Avarakkan]

Hello leaning,

I am not sure is this do you need. Try this in List Source in Sheet1 C5,

=OFFSET(INDEX(LISTS!$S:$S,MATCH($C$4,LISTS!$R:$R,0)),0,0,COUNTIF(LISTS!$R:$R,$C$4))

[JBeaucaire]

You have to click on cell C5 in SHEET1 before you edit the formula for the named range MyPlants. Thats's a realative formula that you want looking at the cell ABOVE it. So I while C5 is selected, edit the formula to:

=IF(TRIM(Sheet1!$C4)="", Message, INDEX(Plants, MATCH(Sheet1!$C4, Customers, 0)) : INDEX(Plants, MATCH(Sheet1!$C4 & "zzz", Customers)))

It started working for me after that.


You also need to fix the ErrorCheck formula the same way. While C5 is selected, edit the named range ErrorCheck to:

=Sheet1!$C4=INDEX(Customers, MATCH(Sheet1!$C5, Plants, 0))

After that, your conditional formatting in C5 will hide your selection if you go back and change C4 to an inappropriate match.

[leaning]

JBeaucaire,

Thanks! It works wonderfully now.

(I always have trouble with Insert Names. It seems Excel tries to "help" me by wanting to create the name based on the cell the cursor is in, rather than the cells I want the name to apply to....Grrr...)

Final version attached. Mad props, sir, and have a great day!

Respectfully,

Lost

[AppSupportKarl]

Leaning,

I need this exact same fucntionality for a spreadsheet of mine, but i do not understand where you are applying the code

Please Login or Register  to view this content.

I understand this part:

Please Login or Register  to view this content.

as it a simple list of clicking the cell/Cells to name, and changing the name of the range.

but the top three have me stumped, how do use a formula to define a range?

[JBeaucaire]

insert > name > define

[leaning]

AppSupportKarl,

1) The name CustomersStart is not needed for the spreadsheet and can be safely deleted.

2. CustList, etc are Names. Like JBeaucaire said, you go to Insert> Name> Define. At the top is where the name goes (CustList) and then where it says "Refers to" is where the formula goes(=OFFSET...), etc. You don't need the colon (. Names make it easy to refer to a cell, group of cells, or even to apply a formula to cells.

HTH!

Lost

[JBeaucaire]

Leaning, Once you get used to named ranges, wait until you delve into named formulas....now that's an even funner exercise!

[AppSupportKarl]

Thanks very much, the pair of you!

[JBeaucaire]

Sir,

If you get a chance, can you look at the attachment to Post #6 of this thread:

http://www.excelforum.com/excel-gene...operating.html

Look at the Named Ranges on Sheet1. Now make a copy of Sheet1 and look at its named ranges.

The two realative formulas from your Post#5 have created a sheet-level MyPlants and ErrorCheck named range that is different from the workbook-level MyPlants and ErrorCheck. This is causing us some crazy problems.

Is there a way to change the formulas (perhaps without the realative aspect) to avoid this sheet-and-workbook-named-range-copies problem?

I appreciate your help!

Respectfully,

Lost

Not that I know of, nor should you try to do that. Named formulas that refer to cell ranges are sheet-dependent. If you copy the sheet then Excel is doing the exact right thing, giving you a localized version of the named formula that will work on the new sheet. It's behaving correctly.

[leaning]

Sir,

Better example.
1. I attached the same attachment from Post #6, but added a MiddleSheet.
2. For our application, we will never make a copy of the Cover sheet or the Lists sheet.
3. We will make many copies of the MiddleSheet.
4. Notice also that the ErrorCheck and MyPlants formula are only applicable to the CoverSheet. (It's the only sheet we use those dropdowns on.)
5. Click on the MiddleSheet and then look at the NR (Insert>Name>Define in XL 2003).
6. Notice that ErrorCheck and Myplant both refer to the Cover sheet (good) and they are workbook-level (not so good).
7. Now comes the fun part.
8. Make a copy of MiddleSheet (MiddleSheet(2)).
9. Look at the names for MiddleSheet(2).
10. Errorcheck and Myplants now have become sheet-level to MiddleSheet(2) (bad) (It's this behavior I am trying to stop.)
12. If you delete those two lower-level names, the correct upper level names are there.
13. And all this is going on for the MiddleSheet (and copies) when the formula and dropdown is only applicable to the Cover sheet.

Do you know a fix for this? I was thinking that every time we make a copy of Middlesheet (using a copy-sheet button tied to code), it also needs to have code to delete any sheet-level Myplants and Errorchecks that get created.) Or maybe there is some syntax that can be applied to the formulas (apostrophes, exclamation points, anything)

??

Respectfully,

Lost