Need dynamic list to populate in another sheet & carry data in destination sheet with it when changed

[jpalarch]

I have 3 separate worksheets. Sheet 1 has a column with a list of numbers & text that will constantly be changing. That column also includes blanks & duplicates. In Sheet 2, I am clearing out all the blanks & duplicates using an "IF, ROWS, INDEX, MATCH" formula. In Sheet 3 in column A, I am using an "IFERROR, OFFSET, COUNTA" formula to re-populate the list from Sheet 2. I then am using the column B as a drop down list to give a status to each item in column A.

The problem I'm having is when a change is made to the "master" list in Sheet 1, column A in sheet 3 updates correctly, but column B obviously will not follow with it. Is there any way to achieve this???

Please help...

 

[scott_n_phnx]

Try using named ranges for your "master" list. To set up a dynamic named range, you can use something like this <b>=OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A),1)</b>. Then, for your drop down, set it to use your named range. This way, when a change is made to the master list, the drop box will change accordingly.

 

[jpalarch]

Thanks - I actually am using this alraedy to populate the list itself. Maybe I wasn't explaing it correctly. Let me try again:

I basically have 3 worksheets in a workbook. the first spreadsheet is a free form "Bill of Material" in which users input part numbers into a designated column. This column can be ever growing and dynamic with duplicate and blanks.

In the second worksheet, I have a INDEX/MATCH type formula that re-populates the list, but eliminates any blank or duplicate entries.

In the 3rd worksheet, I have that list without the blanks and duplicates being re-populated using an OFFSET/COUNTA formula into column A. This works completely fine.

The problem that I run into is that in the 3rd worksheet, I am also applying a status in column B to each part number ("complete" or "not complete"). As soon as a user updates the bill of material in the first worksheet, column B in the 3rd worksheet no longer applies to column A because the values in column B are not linked.

 

[Andrew Poulsom]

There's some VBA code in Post #4 here that you may be able to adapt:

http://www.mrexcel.com/forum/excel-...ould-not-disappear-after-refreshing-file.html