In Excel: DB Lookup, Display and Format

I'm attempting to link a set of business Imparatives to a set of objectives by different industries and display it dynamically (in other words, different industries will have different numbers of Imparatives and different Imparatives will have different number of objectives). I have taken an example with two tabs; one representing the selection and display and the other representing a sample of the database ((e.g. retail industry)). I think this may be a "LookUp" command or doing something with tables (i.e. if "retail" then display ???). The tricky part is how to get it to dynamically project the results.

DonkeyOte,
Greetings! It is not a PT. It's designed for people who don't know Excel to use so I had avoided a PT. I had thought that there was a VLOOKUP command with a set of IF statements or some sort of way to dynamically choose parts of the DB table....so if Industry, then choose and display .....Imparative...... if Imparative, then choose and display....Objective...... That was the thought. I just don't know how to do it with which formula or perhaps a macro.
Bright01

Of paramount importance when working with large data sets is sorting.
Sorting data appropriate to requirements allows you to utilise binary search driven calculations which will be significantly more efficient than exact based lookup approaches.
(It would also be worth avoiding all Volatiles - OFFSET, INDIRECT etc etc...)

I note teylyn has posted another variation - little point in too many cooks esp. so I'll leave you in her capable hands.

0

Bright01Author Commented: 2010-04-15

DonkeyOte,
Thank you for the initial "stab at this"!
Teylyn,
Greetings from Beijing! And may I say your art work on your site is "astounding"!
I tried to use your spreadsheet..... it is very close to what I'm looking for. I added "Banking" and tried to replicate the results and got a lot of N/As. Then I unhid your columns and I guess I just need direction on how to add more rows as I build out 18 different industries. Keep in mind that there can be up to 6 Imparatives and up to 8 Objectives for each imparative for the selection.
Thank you,
Bright01

as I said above, your database sheet needs to be sorted. Click a cell in the data sheet, then Data - Sort. Specify that your data has headers, then specify "Industry" (column A) as the first sort criterion, and "order" (column F) as your second sort criterion. Set both sort criteria to "ascending".

Then click back into the Worksheet and see it all dissolves nicely.

There will always be a fair number of #N/A cells in the hidden columns. That's part of the design and nothing to worry about.

If need be, the sorting of the Database sheet could be arranged by a macro, so that if a user enters more industries and/or Imperatives or Objectives, they don't have to worry about how/which/what/when to sort.

But if you set up the Database sheet at headquarters and deliver the file to your users, you could just sort the sheet without a macro.

see attached. I did nothing but sort the Database sheet as described above.