Show information from database in a list

[JamesT1]

Good Morning

I have a database which shows a list of names and training documents each person requires. I am looking for a way where I can select a name and it will automatically show me the documents that person requires in a list

the attachment shows a small example of what I am looking for

List Training Docs.xls

Cheers

Jim

[Charlie_Howell]

Is this what you are looking for?

[Marcol]

Here's another way using one helper column (AH) and an array formula in Column AI.
I hve added two dynamic named ranges, they are linked, so you can "grow" your table.

Hope this helps

[Charlie_Howell]

that is a fabulous example of great array knowledge Marcol, Bravo! You have used all the functions that I am weak in. I can get to the solution most times, however, I use more Real estate to accomplish my goal, I'm saving this sheet to my collection for later studies.

[JamesT1]

Guys

Both of these will work for me,, in your debt forever,,, many thanks for your help....

Jim

[Charlie_Howell]

Its important to always clean up after oneself,so, I did some cleaning up of my own.

[martindwilson]

non array method still uses a helper row tho, probably a tad more efficient and easier to reproduce!

[Marcol]

@ martindwilson nice one!
Probably a combination of your workbook and this will be best

@ JamesT1
Try this

I have changed the layout of your sheet to make it easier to maintain

In Row 1
in D1

Please Login or Register  to view this content.

Drag Right for 80 columns

This will now be used to dynamically resize the width of your table

In the names manager
"Names" remains unchanged
Refers to:=

Please Login or Register  to view this content.

"ReqdDocs" becomes
Refers to:=

Please Login or Register  to view this content.

Match refers to the length (Last Row) of the named list "Names" this keeps both lists the same length.
The Max function (Table Width) now makes the width of the table dynamic, delete from the end of row 1 and the table width will decrease, drag it further and the width will grow.

You could of course just put a value in a fixed cell and achieve the same, I just thought column numbers might be handy.

The Helper column formula becomes (Now Column A)
In A6

Please Login or Register  to view this content.

This is the same as before but nested in an IF statement to avoid the #REF! error if the formula is dragged past the length that coresponds to the width of the main table.

I have changed your conditional formatting to use a formula

Please Login or Register  to view this content.

and added

Please Login or Register  to view this content.

Both apply to

Please Login or Register  to view this content.

That will allow your table to format as it grows in length up to 1000 rows, and the width up to 200 columns.

Hope this helps.

[martindwilson]

blimey id just use my method and change the range sizes it already caters for c.200 rows

[Marcol]

@ martindwilson
When I mentioned

.....1000 rows, and the width up to 200 columns....

I meant the C/F, the rest is now wholly dynamic, including the drop-down list.

And, hey I like your method, I'll definitely look at that approach in the future.

Cheers
Alistair

[martindwilson]

hey i didnt even notice the cf part lol

[JamesT1]

Hi Guys

Many thanks for all you help,,, all these solutions will do what I need to do....

Marcol, thanks for explaining how and what you changed,,, helps me understand it a lot easier,,,,

many thanks guys



Jim

[Charlie_Howell]

this has been one of the best threads ive been involved with...thanks.