Listing a set of files from a database

**mcooper1975** · 10-28-2008, 11:59 AM

Hey everyone,

I have one that could be difficult. I have a database with names and corresponding numbers. I am trying to develop a report in which one could choose a name from a list (for example, using validation) and the report pulls out all the corresponding numbers for that name. Not the amount of numbers but the actual list of those that are linked to that name. Can that be done?

I am attaching an examplem file.

Thanks!

**Cheeky Charlie** · 10-28-2008, 01:20 PM

Pivot table!

HTH

**mcooper1975** · 10-28-2008, 01:51 PM

Thanks! Is there any other way? I used iserror/offset/match formula. Used iserror to give back a "blank" value if tghe formula didn't find a match. Now it will give me a list of data for any given value. However, depending on the value selected and its presence in the list, the retrieved list can be many spaces below. Is there a way to bring the list up?

Check the file

**Cheeky Charlie** · 10-30-2008, 08:00 AM

Is there any other way?

Why? What does the pivot table not do?

PS can't open your attmt.

**royUK** · 10-30-2008, 08:08 AM

using formulas will be slower than a PivotTable, using ISERROR will make it even slower.

**mcooper1975** · 10-31-2008, 11:05 AM

Can you open this one?

The reasons I am resisting use of pivot tables are, first because pivot tables make files weight more. Second because the users of this report will be other people that might not know how to manage/change pivot tables, therefore I am trying formulas to make it more user friendly.

**mcooper1975** · 10-31-2008, 12:15 PM

Actually at this point what would solve the problem would be a way to list all the values in the list that are not null. This would bring the list up.

**ratcat** · 11-02-2008, 06:08 AM

G'day All,

Matt, if you really want a formula to retrieve the current information and bring the list up to the top.

Try this in cell C8

Please Login or Register  to view this content.

After pasting in the formula press F2 then press Ctrl Shift Enter to confirm the array formula.

Then you can copy drag down to your heart's content.

HTH

**mcooper1975** · 11-11-2008, 01:27 AM

Hey thanks Ratcat,

It looks right, though when I used it, it returned a #NUM! error..

Mike

**ratcat** · 11-11-2008, 05:35 AM

G'day Matt,

Just found out that Excel versions older that 2007 can not handle 'named ranges' in that array formula. That's the reason for the #NUM! value.

So, for an example convert your formula to this

Please Login or Register  to view this content.

Remember after editing the formula press Ctrl Shift Enter to confirm the array formula.

If your still having problems, let us know and I'll upload an example.

Cheers