• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

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
0
Bright01
Asked:
Bright01
  • 4
  • 3
  • 3
2 Solutions
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now