I have a spreadsheet that is drawing from an Access database.

The user has a main choice, then for some of the main choices there are sub
selections. If there are only a couple, I can write an if to select the
different ranges on the Data Validation dialog box. But I'd like more
indpendence. When the database is tied I download tables. So if the main
choice has a sub selection, I have the letters that are the suffixes to the
range names with the various sub menus.

Here's an example of what I'd like to do:

MenuSuffix="WC"
=IF(MenuSuffix="",Range("A1"),"Index" & MenuSuffix)

Just how do I turn "Index" & MenuSuffix into the range value?

Don <www.donwiss.com (e-mail link at home page bottom).

Debra Dalgleish shows how to create dependent lists:
http://contextures.com/xlDataVal02.html

Don Wiss wrote:

I have a spreadsheet that is drawing from an Access database.

The user has a main choice, then for some of the main choices there are sub
selections. If there are only a couple, I can write an if to select the
different ranges on the Data Validation dialog box. But I'd like more
indpendence. When the database is tied I download tables. So if the main
choice has a sub selection, I have the letters that are the suffixes to the
range names with the various sub menus.

Here's an example of what I'd like to do:

MenuSuffix="WC"
=IF(MenuSuffix="",Range("A1"),"Index" & MenuSuffix)

Just how do I turn "Index" & MenuSuffix into the range value?

Don <www.donwiss.com (e-mail link at home page bottom).

--

Dave Peterson

On Sat, 25 Nov 2006, Dave Peterson wrote:

Don Wiss wrote:
Just how do I turn "Index" & MenuSuffix into the range value?

Debra Dalgleish shows how to create dependent lists:
http://contextures.com/xlDataVal02.html

Thanks Dave. I had once seen that page before. I found the process
confusing. But with a simple improvement to the tables backing these lists,
I came up with:

On my main input I have a column with this copied down:
=IF(V12="",A$3,INDIRECT(V12))

Then on my comparison sheet with two input cells I found I could use names:
=IF(SubMenu1="",A2,INDIRECT(SubMenu1))
=IF(SubMenu2="",A2,INDIRECT(SubMenu2))

Don <www.donwiss.com (e-mail link at home page bottom).