Yet another question on dependent validation dropdowns

[andreas_udby]

I've tried a number of different methods and still can't get this to work. I have the following in a sheet called "Tables":

[b]10	9	8	7	6	5	4	3	2	1	0[/b]
10	9	8	7	6	5	4	3	2	1	0
9	8	7	6	5	4	3	2	1	0	
8	7	6	5	4	3	2	1	0	-1	
7	6	5	4	3	2	1	0	-1		
6	5	4	3	2	1	0	-1	-2		
5	4	3	2	1	0	-1	-2			
4	3	2	1	0	-1	-2	-3			
3	2	1	0	-1	-2	-3				
2	1	0	-1	-2	-3	-4				
1	0	-1	-2	-3	-4					
0	-1	-2	-3	-4	-5					
-1	-2	-3	-4	-5						
-2	-3	-4	-5	-6						
-3	-4	-5	-6							
-4	-5	-6	-7							
-5	-6	-7								
-6	-7	-8								
-7	-8									
-8	-9									
-9										
-10

(A jpg is attached showing it a little more clearly).

On another sheet, titled "Design", I have two cells that I want to be in-cell drop-downs. The first, "Range", should let the user choose a range between 1 and 10, which would then limit the second text box ("Level") to numbers from the number selected to the negative of that number.

So, for example, the user chooses "4" in the Range cell; the Level cell would then give them the option of 4, 3, 2, 1, 0, -1, -2, -3, -4.

However, having tried the OFFSET and INDEX methods of dependent validation described elsewhere on the Intarweb, I still can't seem to make this work. Can someone point me in a different direction on this?

Thanks!
Andreas

 

[Kiwiman]

Howzit

Here's my effort. Using indirect and named ranges - all named the same except the number relating to the original selection.

 

[andreas_udby]

Wow, that's exactly what I was trying to do! And it looks so simple, as things often do. I must not have a correct understanding of the INDIRECT formula. Back to the books...

Thanks, Kiwiman!

 

[RayH]

This type of thing is what I'm after for a project of mine.
The problem is that I cannot figure out how to replicate it.
How did you do it?
I can see the named ranges in the Selection sheet but I cannot find how the dropdown is populated.
A step by step guide would be very useful (and not just for me either).
Can you help?

Note:I'm using Excel 2007 ( but have access to 2003) so don't worry about the version.

 

[RayH]

I have found it. Sorry, I panicked!

In 2003, select the cell then Data, Validation.
That was a great solution btw.