Referencing one list from another...

It's a couple of tools working together.

Named references, found under Formulas->Name Manager defines a range with word. You could highlight D1:D50 on page, and in the upper left corner type bob in the "name field" and you could then forever call D1:D50 with the word bob. If D1:D50 was all 2's, then SUM(bob) would be 100.

namedrange.png

The lists in H3:H4, J3:J6, and L3:L5 are named ranges, named after their header; Produce, Fruit, Vegetable.

The cells in A:A are using Data Validation, found in Data->Data Validation, with the List option, and the named ranges for the source. In this case, the lists in A are sourced to =Product.

The validation in column B uses INDIRECT(whatever is in corresponding column A) to pull the word and convert the string to an indirect reference so that Excel evaluates it as more than just a word. It then recognizes Fruit or Vegetable as one of the named ranges, and displays its corresponding list.

INDIRECT() took me a while to get around, but it has some great uses. If E10 simply had H1:H5 in it and you put SUM(E10) you would it would get a zero because it's evaluating the cell as text. But SUM(INDIRECT(E10)) would look at the contents of E10 and re-evaluate its contents as an actual range.

Note that all items in the drop down list in column A are in the list called Produce. Both items in the Produce list have their own lists with the names that are in the Produce list namely Fruit and Vegetable.

If FRUIT is chosen in the drop down list in column A then the drop down list in column B draws its contents from the FRUIT list.

If VEGETABLE is chosen in the drop down list in column A then the drop down list in column B draws its contents from the VEGETABLE list.

Click in column A then click on the Data tab, Data Validation and see how the list was assigned to the drop down.

Do the same for column B.

If you have 10 items in the MASTER and each of those items has 6 options, you will have 10 lists to be created 1 for each item in the master list named according to the item in the master list.

This change will allow you to use entries with spaces.

The use of tables is an easy way to have dynamic ranges. To add items to each table, click in the last cell of the table and hit the TAB key to create a new line in the table. Enter your data and that is automatically entered in the drop down list.

A look at the workbook would help. There are quite a few variables in the moving of tables and drop down lists. All of the names associated with the tables may have reference errors caused by the move....have to see to make sure.

Here is a version that I think works properly as far as my testing showed. I changed the first drop down to be Paints and Stains. From there the second drop down is dependent upon the choice of the first drop down and it gives the type of Paint or Stain. I added a third drop down that is dependent upon the second drop down and gives the details of the Paint or Stain type choice.

I also removed the spaces from the tables as they do little other than make the lists longer than they should be. In addition, the spaces at the bottom of the tables didn't agree with the named ranges so they had to be brought into agreement.

I also deleted all names in the Name Manager that no longer were valid.

I hope that this arrangement works for you.

Oops, I forgot the requirement to move the drop down lists. This has been corrected:

I'm still having issues with the reverse engineering. I can't get Buttercreme's dependants to work.

What role does OFFSET(FINISHES_CALC!$I$6,0,0,COUNTA(FINISHES_CALC!$I$6:$I$50),1) play? I am unable to find its reference.

When attempting to use the named ranges on the first sheet, many of them do not appear to be available. Merlot shows up just fine, but Barn_Red doesn't appear in the function list when typing it.

Edit: I figured out what was creating issues with both example. Most of names are defined in the Name Manager as only being scoped to the 2nd tab. Each of them justs needs adjusted to apply to the whole workbook.

Are these problems with the workbook that I uploaded or are they problems with another workbook? I've gotten a bit lost in the conversation.

Can you post what you are having problems with?

This should work:

About the question about "OFFSET(FINISHES_CALC!$I$6,0,0,COUNTA(FINISHES_CALC!$I$6:$I$50),1)" This is used to create a dynamic list. It in effect defines a range by starting in $I$6 and the offset is zero rows, zero columns but the height is the count of cells that are not empty inside the range $I$6 to $I$50. That means that whatever the COUNTA value is, is the number of rows in the range. The 1 at the end is the width of the range in columns.

OFFSET returns a value from a known cell that is a certain number of rows and columns away. As an experiment, enter a number in A10 on a worksheet that you can experiment on. Now, in another cell (anywhere) enter this formula =OFFSET(A1,9,0,1,1) The value that you entered in A10 should be returned. What the formula says is something like this. Starting in A1 count down 9 rows, stay in the same column and return the value in the range 1 row high and 1 column wide. This formula can be shortened down to =OFFSET(A1,9,0) as the height and width of the reference are not relevant in this case.

Now, with the same value in A10 enter this formula in another cell =OFFSET(B20,-10,-1) This means to start at cell B20, go up 10 rows and to the left 1 column.

=OFFSET($I$6,0,0,COUNTA($I$6:$I$50),1) This form of the OFFSET is used to create dynamic ranges. That is, ranges of varying length which is useful with creating drop down lists and you don't want a big space at the bottom of the list or you want the drop down list to expand when new items are added to it without having to change the cell references to the range in the Name Manager. This formula says to start at $I$6 (a fixed cell),don't move any rows or columns, (the two zeros) but make the range the size of the count of cells that are not empty and keep the range 1 column wide. The range specified in the COUNTA part puts a limit on the amount of data that can be entered in the list without having to re-define the list range. If there was going to be more than 50 items the 50 could be any number that would be sufficient to satisfy you.

OFFSET has been eliminated from the worksheet by the use of the tables. (as far as I can remember) When using tables, the OFFSET command becomes rather unnecessary. The original file posted in this thread, is an example file that I uploaded in another thread. The OFFSET may have been in that file, I don't know for sure.

I did discover that there were several names in the Name Manager that were carry-overs that didn't belong, from other projects that I worked on. This may have been the source. How those names got into this project, I don't know....I deleted them when I went through the names in the Name Manager.

Good, you're getting the "hang" of it