Dependent Lists with Index function

**penguintar** · 03-27-2011, 03:06 AM

Hi,
I posted a thread last week asking for advice on how to create a complex dependent list. I was given advice to look at the Contextures article on creating dependent lists with Index. I followed the instructions in this article on creating a dynamic list and it appeared to work in a new spreadsheet however I need it to work in my existing spreadsheet (attached) for Columns C,D and F.

How do I change the references detailed in the article to work in my Costings! input sheet for cells C6,D6 and F6? My validation lists are in Validations! worksheet.

The existing references detailed in the tutorial are as follows:
Master - =Lists!$A$2:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A))
ValData - =Lists!$A$2:INDEX(Lists!$1:$100,100,COUNTA(Lists!$1:$1))
Counter - =COUNTA(INDEX(ValData,,MATCH('Data Entry'!A2,Lists!$1:$1,0)))
UseList - =INDEX(ValData,1,MATCH('Data Entry'!A2,Lists!$1:$1,0)): INDEX(ValData,Counter,MATCH('Data Entry'!A2,Lists!$1:$1,0))

Any help would be greatly appreciated as I am a noob with excel.

**Marcol** · 03-27-2011, 01:27 PM

Try this amended attachment

I have had to change some of your field values to avoid duplicate names, it can be done as you had them, but it becomes complicated and difficult to maintain.

You can't have certain characters in named ranges. "." (dot) is one of these Illegal characters. However I can see it being useful to display them in your industry so I have written some of the validation to allow their use (dot only).

In the UK we frequently use "ERW", "CDERW", etc for "E. R. W", "C. D. E. R. W.", etc, it would be easier if your notation could be amended to suit.

As a matter of interest what does "S. A. W." stand for? I haven't come across that one before.

I couldn't follow more of your sheet without spending a fair bit of time, so I have left it more or less as was.

Hope this helps

**penguintar** · 03-28-2011, 12:28 AM

Marcol,

Many thanks for your help! I see you used the offset and indirect functions to solve the problem. I didn't realise that you could use these functions in this scenario. Great tip!

Just for your information S.A.W. stands for Submerged arc welded. It is basically a more robust version of an ERW type weld that is welded bidirectionally instead of unidirectionally as in the ERW type weld, so thus produces a more robust pipe used mainly for structural applications in the offshore oil and gas industry.

Thanks again for your help and time!

**Marcol** · 03-28-2011, 04:28 AM

Happy to have helped.

Don't know why I got it into my head you were in the USA, I even wondered why an American was using metric units!

Thanks for the info on the weld, I hadn't come across that one, or at least not knowingly so.