Excel Vlookup Named Range

[monagan]

If anyone can point me to a good thread that I haven't foudn yet, or give me some kind of direction on this problem.

I have a parts table (excel)
Parts Number Description weight
0123l5 something 5
235j7 something else 6
075dfg and another 257

What I want is based on the an input from a user, have excel pull up all the parts numbers.

For instance if a user picks 4" Fan in a drop down box
I want the named range "FourInchFan" to appear in a table on the same worksheet.

I hope my description was well enough.

"And on the 8th Day, He recorded a macro

 

[SkipVought]

monagan,

Man, you have made an easy problem HARD!

You have had to create all these named ranges, right?

Don't you have all your data in ONE TABLE? You should!

If all your data were in one table, a simple QUERY, via Data/Get External Data/New Database Query -- Excel files -- your workbook name -- your table or the sheet name...

So with a Dropdown box and one or more queries, you can do EXACTLY what you want. I do this OFTEN, very sucessfully!

PS: I love your tag line ;-)

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[monagan]

hey skip, I'm not sure what you're referring to.

I'm not working in access, only in Excel.

I have a table, but I want to hide it to clean things up.

So I just need the range of part numbers that refer to a specific item to show up.

"And on the 8th Day, He recorded a macro

 

[SkipVought]

I'm referring to Excel.

You have a table of data, either in that workbook or another workbook. You can QUERY that table based on a selection that a user makes, for instance, from a dropdown box. It works pretty neat.

I can explain how to do it.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[monagan]

that'd be great thanks so much


"And on the 8th Day, He recorded a macro

 

[SkipVought]

Let me see a sample of your source data, just headings and a few typical rows.

Is you source data alone on a separate sheet? It should be.

What value is the user providing and how is it being provided?

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[monagan]

PART NUM DESCRIPTION WEIGHT
2600-24 UPPER FRAME WLDMT. 26
2631-24 STRINGER ANGLE 95 1/8" 9
2633-24 STRINGER ANGLE 72 1/4" 0
2705-24 AIRSHAFT FRAME WLDMT. 20
2600-48 FIXED TOP FRAME WLDMT. 20


This is my table, three columns.

It is on the same sheet as where the user enters the data, but isn't a problem to move to another worksheet.

The user has a simple data validation list of 24-180.

Those are sizes of different fans

So if they pick 24, everything (which is in order) that relates to that will be posted underneath the drop down in three columns

"And on the 8th Day, He recorded a macro

 

[SkipVought]

"The user has a simple data validation list of 24-180."

What does that mean? A list of numbers from 24 to 180?

So if the user picks 24, or 25, how does that relate to the table that you just posted?

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[monagan]

it's not conesecutive numbers. It jumps a bit, like 24,30,36,42,48,134,160.

so if the user picks 24. using the table I just posted, They would see the first 5 (the part numbers that end in -24) and if the picked 48, they would see the last part number (the one that ends in -48)


So if they chose 108, they would see this

2600-108
2601-108
2602L-108
2602R-108
2604-108
2632-108(1)
2632-108(2)
2632-108(3)
2632-108(4)
2628-108
2631-108(1)
2631-108(2)
2631-108(3)
2631-108(4)
2631E -108(1)
2631E -108(2)


"And on the 8th Day, He recorded a macro

 

[SkipVought]

First, you'll have to add a column to your table for the lookup value, 24, 48, etc like this
[tt]
Key PART NUM DESCRIPTION WEIGHT
24 2600-24  UPPER FRAME WLDMT. 26
24 2631-24  STRINGER ANGLE 95 1/8" 9
24 2633-24  STRINGER ANGLE 72 1/4" 0
24 2705-24  AIRSHAFT FRAME WLDMT.  20
48 2600-48  FIXED TOP FRAME WLDMT. 20
[/tt]
Then on the sheet that is the user interface, the user selection needs to end up in a cell, lets name it UserSelection.

Select the cell where you want to query results (the list the user seeas after the selection)

Data/Get External Data/New Database Query -- Excel Files -- Your workbook -- the sheet name where your source data is -- [next],[next],[next], select Edit Quer..., [finish]

Now you should be in the MS Query Grid, and you should see the table data listed. Delete any columns you do not want to display. ADD A CRITERIA for the key value. In the VALUE bos enter [What Value] -- ADD IT. When prompted enter a valid value.

File/return data to Excel.

In the window, select the Parameters button. Activate the he botton option, put your cursor in the box and then select the cell on the sheet containing the value the user selects. ALSO, check the box, Refresh automatically when cell value changes. -- OK

Change the cell value and the query fires!

VOLA!



Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[monagan]

I recieve an error that my workbbok doesn't contain any tables


"And on the 8th Day, He recorded a macro

 

[SkipVought]

What is the full path and workbook name?

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[monagan]

c:/documents and Settings/Jon Monagan/My Documents/RidgeWeight.xls

"And on the 8th Day, He recorded a macro

 

[methoni]

How about using the data filter option. Just setup the "data", "filter", "autofilter" option on and when someone wants to know all the data on the 24's, click on the down arrow of the "key" column and pick the entry you are inquiring about. This will hide all other entries and display only what you are looking for.

 

[monagan]

I dont' know if this would make a difference, but my method of getting to the query part is different than yours.

I have to go to data/import external data/new database query.

Also, when you say table, do you mean that exactly.

My table is just something that looks like a table on the spreadsheet.

I saw that there is a table selection on the data menu, but I wasn't sure how to work that because it asks for an input row and column.



"And on the 8th Day, He recorded a macro

 

[SkipVought]

methoni's suggestion is valid. I've been doing a selection from a selection.

Just filter on your key value, 24, 48 in the new column.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[monagan]

I'm confused on the filter, how do I do that?


"And on the 8th Day, He recorded a macro

 

[monagan]

can I get the arrow to coinside with the other drop down box?

"And on the 8th Day, He recorded a macro

 

[SkipVought]

Data/Filter/Autofilter

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[monagan]

using the auto filter and my key field, is there a way i can have some items show up no matter what?

"And on the 8th Day, He recorded a macro