dependent drop down boxes and appearing data

[vindicta]

Hey, I need some spreadsheet on neooffice please:

I have 2 sheets, one sheet goes like this (albeit with a lot more data):


COL A COL B COL C
Data A Resources X Resources Y
Lvl 1 100 100
Lvl 2 200 200

Data B Resources X Resources Y
Lvl 1 100 100
Lvl 2 300 300


On the second sheet, I would like to make a series of drop down boxes in column A for Data A and B. I would also like to make a dependent series of drop down boxes in column B for the 'Lvls' of the Data. After choosing both sets of drop down boxes, I would finally like to see their relevant data appear on the same row, e.g.

COL A COL B COL C COL D
Data Lvl Resource X Resource Y
Data A 1 100 100
Data B 2 300 300


Okay, I figured out the first part; I know how to make drop down boxes for column A. However, I'm still trying to figure out how to create a dependent drop down box for column B. I've searched various articles on the internet, which involves the use of the INDIRECT function, but I always get Err508.

Also I haven't worked out how to get resource X and Y to appear once the drop down boxes in columns A and B are obtained.

I'm asking for help on creating a dependent drop down box for column B, and whether it be possible to make the relevant data appear once the drop down boxes have been chosen. I don't know which functions to use for the latter.

The reason I like to do this on a spreadsheet is to make arithmetic calculations using the data, and to send the file as a spreadsheet to folks who may not have a database program.

Many thanks.

[pluby]

FYI. I have moved this topic to the NeoOffice Support forum where it will likely have a better chance of being seen by other NeoOffice users as posting a support question in the Random Whatnot forum decreases the number of fellow NeoOffice users that will read your post.

Patrick

[shades]

Howdy. Not sure, if this is what you want. But I rearranged the data a little (added two columns, one for reference, one for separation of data).

There are two worksheets:

DataWS has the basic data (I changed the numbers so you can see how it works). I also named two ranges (but you only need one for this). The key range is MyData and refers to cells A2:E5. Notice that Column A has a formula to concatenate columns B and C. I also included a separate column for Data. This makes data more understandable and usable for formulas.

So in cell A2 is the formula:

=B2&C2

Copy down to A5

SumWS includes the drop downs and the lookup formulas.

A2:A5 include the same concatenate formulas from DataWS

B2:B5 has formulas based on lookup, using Validity:

Click in B2, then on the menu bar, go to Data > Validity, in the resulting window in the Allow drop down it should be list. Then below you list the options DataA and DataB, one for each line.

Copy B2 to cells B3:B5. You can add to the list at a later time.

Click in cell C2 and follow the same process, only this time in the list, put 1 and 2 (on separate lines)..

Then click in cell D2 and put in this formula:

=VLOOKUP($A2;MyData;4)

Copy down to D5 and over to E5.

This is the basic approach. If you can set up dynamic named ranges, then it can expand as you add more data.[/u][/b]