VB Required for Dynamic List

[bc4240]

Hopefully someone can lend a hand. I do not know anything abt VB other then how to copy and paste it in so be gentle.

I have a table in a workbook with names in the left column and top row. The intersecting data fill the rows under and to the right of these named areas. I need to keep this table in its own workbook, because many sheets in other workbooks will access its info. I have linked this workbook information to a sheet in each of my accessing workbook. I call this sheet "DataLink" and it is a sheet that uses an If(Original Data Ref Cell="","",Original Data Ref Cell). This works well at bring the data into my active workbook.

My active sheet has 2 drop down lists. One for the top row of names on the DataLink Sheet, and one for the left column. I have used the Offset Function refering to cells with the countif and count functions in my Data Validation setup to make the addition of the names in the DataLink Sheet Dynamic. This also is working well.

In my Taget cell (CeIl where I will get my answers) I use the Indirect(row) indirect(column) to retrieve my intersecting data relate the the row and column. This works well as long as I establish my defined names of each column and row with Ctrl-Shift-F3 and choose (Top and Left)

Now my problem. Although I can dynamically add the names to my drop down list I cannot retrieve the data from new entries on my DataLink Sheet(remember coming into active workbook from outside source) unless each time I return to my DataLink Sheet,and highlight the entire range of cells and do a new Ctrl-Shift-F3, which then ask me if I want to replace each name one by one. I have some 700 names defined. And this is disconcerting to say the least. Is there a way to add code to automatically add the contents of a cell as a defined name. This way as my information from my original data workbook enters the DataLink Sheet in each of my Active workbooks, new names that appear in the left column and top row will be defined as have intersecting data under them.


Sorry for the length, just trying the get all the details in.

Thanks in advance
bc

 

[bc4240]

Looks Like I've stumpped you guys. If someone is thinking about this throw me a bone to chew on to give me some hope that I havn't crash the machine.

Thanks

bc

 

[bc4240]

crashed - sorry I'm not good with "ing", "ly" or "ed" added to words as you can tell from my bone headed display from both request.

 

[mgirvin]

Dear bc4240,

Try this:

1) Data in first workbook

2) Second workbook: Create formula like:

=if([DATA]Sheet1!A1="",""[DATA]Sheet1!A1) to retrieve data


3) Second workbook: Create formula in a cell to "count rows with data" like:

=SUMPRODUCT(--(A:A<>""))


4) Second workbook: Create formula in a cell to "count columns with data" like:

=SUMPRODUCT(--(1:1<>""))


5) Second workbook: Create Named Formula for 1st column of lookup values:

=OFFSET($A$1,1,0,CellWithNumberOfRows-1,1), name it "ColumnA"


6) Second workbook: Create Named Formula for 1st row of lookup values:

=OFFSET($A$1,0,1,1,CellWithNumberOfColumns-1), name it "FirstRow01"


7) Second workbook: Create Named Formula for table with lookup values:

=OFFSET($B$2,0,0,CellWithNumberOfRows-1,CellWithNumberOfColumns-1), name it "Table"


8) Second workbook: add data validation based on the name "ColumnA", I used cell O26 (used in next formula)

9) Second workbook: add data validation based on the name "FirstRow01", I used cell P26 (used in next formula)

10) Second workbook: create INDEX / MATCH formula for two-way lookup like:

=IF(ISNA(INDEX(Table,MATCH(O26,ColumnA,0),MATCH(P26,FirstRow01,0))),"",INDEX(Table,MATCH(O26,ColumnA,0),MATCH(P26,FirstRow01,0)))


11) Be careful of where you put your formulas in second workbook (because of expandable ranges)

 

[bc4240]

Thanks for taking a look. I'll look it over and give it a try. I'll let you know how it goes

 

[bc4240]

So it worked for you? and it automatically expanded the data field and the new data could be match/indexed. I'm having trouble. I keep getting #VALUE# error for ColumnA, Table and FirstRow01 so then when I try to do cell validation using the names ColumnA, Table and FirstRow01 I get #VALUE# in the drop list. I must be missing something. I'll have to try more tomorrow. I see where your trying to go with this though. I'm off somewhere.

Thanks for the help

Bc

 

[mgirvin]

I got it to work. There are a lot of steps with tons of room for error. Send me an e-mail to mgirvin@highline.edu and I'll send my example.

 

[bc4240]

Mike just wanted to say thanks for the help. You answer got me thinking and I got it to work with something similar to what you described. Thanks again

 

[mgirvin]

Send me your workbook so I can take a look: mgirvin@highline.edu

 

[bc4240]

I took the Index Match Match for the two way lookup and applied that to the cell looking for the information coming from my two drop down boxes. It worked that way. I was trying to find the intersector using a pair of =Indirect(Defined Name) to retrieve the data. So I punted the Indirect function and return to the faithful Index/Match. Thanks for the idea.