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.

Good luck!
Lookup--display-and-format-from-.xls
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DonkeyOteCommented:
Is a PT viable ?

In the attached the PT is sourced via a Dynamic Named Range (_PTData) such that as new values are added refreshing the PT should encapsulate all data as at that point in time.

Retail would be represented by the Page Filter of the PT - you could move this to a Row Field if preferred.

Not perfect by any means but any entirely formula driven approach will be cumbersome (IMO).

Q-25882910-Bright01.xls
0
Bright01Author Commented:
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
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello again Bright,

attached an approach with a few helper columns, which are hidden.

The only requirement is that the database sheet is sorted by column A as primary and column F as secondary sort criterion, and that Imperatives have a 0 in column F.

I renamed your Finance category to "Fun"  for testing purposes and created a few dummy entries.

cheers, teylyn

Copy-of-Lookup--display-and-form.xls
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

DonkeyOteCommented:
The issue you will face using formulae is one of efficiency.

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...)

How many transactions / rows do you have ?
0
DonkeyOteCommented:
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:
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

Copy-of-Lookup--display-and-form.xls
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Bright,

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.

cheers, teylyn
Copy-of-Copy-of-Lookup--display-.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Fast and "Eligant"  -- It can't get much better then that!
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Thanks for the grade, Bright.

>>  And may I say your art work on your site is "astounding"!

How did you manage to find the site? I don't think I have a link to it on EE.
0
Bright01Author Commented:
You sent me a link about two weeks ago.  Wonderful work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.