Want use results of Drop down list to look up a value

I am using Excel 2002 and I have a multisheet workbook and on the first sheet 
I have created a series of 6 drop down lists pulling from predefined named 
ranges. I  also have limited subsequent choices based on what is selected in 
the other lists. The end result is the user will be selecting parameters that 
identify a product and now I am stuck with an easy way to look up the 
associated part number based on the parameters they selected. Essentially, 
once they have chosen the parameters in the list, I would like to create a 
macro that uses those values to locate the associated part number.

I would like to know the best way to set up the database such that the macro 
will use parameter A, B, C, D, E, and F to then go look up the part number 
that satisfies those 6 parameters. Then, I would also like to know the best 
way to write a macro to do so.

Thank you.
0
Utf
2/24/2010 4:26:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
737 Views

Similar Articles

[PageSpeed] 2

Assuming your table is in rows 1 to 1000, a rough macro would look like 
this:

Sub FindMe()
A = Parameter1 'Define these properly
B = Parameter2
C = Parameter3
D = Parameter4
E = Parameter5

For i = 1 To 1000
If Cells(i, "A") = A And _
Cells(i, "B") = B And _
Cells(i, "C") = C And _
Cells(i, "D") = D And _
Cells(i, "E") = E Then

'Where do you want output?
Range("G1") = Cells(i, "F").Value
Exit For
End If
Next
End Sub


-- 
Best Regards,

Luke M
"Rob Van Pelt" <Rob Van Pelt@discussions.microsoft.com> wrote in message 
news:DB2BB3AB-3F3F-4A87-8F7E-26573EDE56C9@microsoft.com...
>I am using Excel 2002 and I have a multisheet workbook and on the first 
>sheet
> I have created a series of 6 drop down lists pulling from predefined named
> ranges. I  also have limited subsequent choices based on what is selected 
> in
> the other lists. The end result is the user will be selecting parameters 
> that
> identify a product and now I am stuck with an easy way to look up the
> associated part number based on the parameters they selected. Essentially,
> once they have chosen the parameters in the list, I would like to create a
> macro that uses those values to locate the associated part number.
>
> I would like to know the best way to set up the database such that the 
> macro
> will use parameter A, B, C, D, E, and F to then go look up the part number
> that satisfies those 6 parameters. Then, I would also like to know the 
> best
> way to write a macro to do so.
>
> Thank you. 


0
Luke
2/24/2010 4:43:14 PM
I think I understand some of what you are describing but I let me clarify. If 
I understand what you are saying, I would use column F to define the part 
number associated with the combination of parameters in columns A thru E. So 
if each parameter had 5 possible values, I would need to use rows 1 to 3125 
to cover every possible combination, correct? (5 to the 5th power). Rows 1 
thru 625 would all have identical A parameters, rows 1 thru 125 would also 
all have identical B parameters, rows 1 thru 25 would also all have identical 
D parameters, and rows 1 thru 5 would be each unique E parameter and then 
that pattern would repeat 5 more times, correct?

I think that helps, just a lot of data entry to get it set up.

Thanks!

"Luke M" wrote:

> Assuming your table is in rows 1 to 1000, a rough macro would look like 
> this:
> 
> Sub FindMe()
> A = Parameter1 'Define these properly
> B = Parameter2
> C = Parameter3
> D = Parameter4
> E = Parameter5
> 
> For i = 1 To 1000
> If Cells(i, "A") = A And _
> Cells(i, "B") = B And _
> Cells(i, "C") = C And _
> Cells(i, "D") = D And _
> Cells(i, "E") = E Then
> 
> 'Where do you want output?
> Range("G1") = Cells(i, "F").Value
> Exit For
> End If
> Next
> End Sub
> 
> 
> -- 
> Best Regards,
> 
> Luke M
> "Rob Van Pelt" <Rob Van Pelt@discussions.microsoft.com> wrote in message 
> news:DB2BB3AB-3F3F-4A87-8F7E-26573EDE56C9@microsoft.com...
> >I am using Excel 2002 and I have a multisheet workbook and on the first 
> >sheet
> > I have created a series of 6 drop down lists pulling from predefined named
> > ranges. I  also have limited subsequent choices based on what is selected 
> > in
> > the other lists. The end result is the user will be selecting parameters 
> > that
> > identify a product and now I am stuck with an easy way to look up the
> > associated part number based on the parameters they selected. Essentially,
> > once they have chosen the parameters in the list, I would like to create a
> > macro that uses those values to locate the associated part number.
> >
> > I would like to know the best way to set up the database such that the 
> > macro
> > will use parameter A, B, C, D, E, and F to then go look up the part number
> > that satisfies those 6 parameters. Then, I would also like to know the 
> > best
> > way to write a macro to do so.
> >
> > Thank you. 
> 
> 
> .
> 
0
Utf
2/24/2010 6:00:01 PM
I have done as Luke M suggested but and have assigned the macro to a Command 
Button. I am not getting the value to display in the target cell. Any 
suggestion about that?

0
Utf
2/24/2010 10:00:03 PM
Reply:

Similar Artilces:

best use for x,z and zz report
now we are using z report for end of day and zz report for end of month (x report just to get hard copy of inday sales) is thier a better or more usefull way to use this three reports? best reqards Hi Bacha11....you describe what most RMS users (I believe) do with the X, Z and ZZ reports - if you have multiple shifts (example a morning shift and a afternoon shift) on your registers you could use the X and Z reports for shift reporting and the ZZ for end of day (it will include both Z reports from both shifts) - other than that I think you're good to go.... "BACHA11" wrote:...

Can indirect() work using closed external files??
Workbook A has a cell that gets data from workbook B as an external link. It does this using the indirect function because it needs to concatenate the path and filename from other cells. Problem is that if workbook B is closed, the cell in workbook A shows #REF!. So can indirect work using closed external files or must the external files be opened? Is there a clever way of making that work? Thanks! I know they must be open and I have not found a way to make them work yet. Anyone? Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can fin...

Best way to get a pick-list on a form?
Is there a safe and effective way to get a pick list of values for a Standard Outlook form? Maybe a Macro button? What I want is to limit and auto-enter some properties, without having to re-do an entire form. Simple, or no? -- dBaser from back in the day ... It might be simple, but it's hard to say without knowing exactly what you mean by "standard Outlook form" and what business goals you're trying to accomplish, in what version of Outlook. Please provide more details. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Ju...

how to use a negative number
I have a client that gave me a deposit for a service. I was paid this deposi last year. I now have made a invoice for this service with Money Home and Business 2007. How can I show that I have already received $200 towards this service? Money will not let me use a negative number So how can I do this? Can't say for sure as I've never used the * Business * editions, but you may have to use an income category to categorize the amount. IIRC, it has been reported that the * Business * editions don't allow calling income expense and vice versa. Sadly, next to no users of * B...

Am I supposed to see the OUTPUT parameter's value in SQL Mgmt Stud
Hi, I have a stored procedure with an OUTPUT parameter. When I execute the procedure in SQL Server Mgmt Studio, am I supposed to see the OUTPUT parameter's value? I'm troubleshooting an issue and at this point I'm not sure if it's the application code or the stored procedure. When I execute the stored procedure, it does what it's supposed to do i.e. insert a new record, but I do not see the value of the output param -- unlike when I return values through a SELECT statement. Is this normal or am I supposed to see the value returned by the output param? -...

Send on Behalf of Distribution List #2
We are currently using Exchange 2003 in a W2k3 Active Directory environment. I would like to be able to allow a person to send an e-mail on behalf of a Distribution List. How do I do that? I do not see a Send On Behalf of property in the DL. The closest thing I've found is the Send As property in the Security tab, but that doesn't seem to work. Any ideas? As far as I know, this is not possible. You can only do a send on behalf of another Mailbox. If you have Send As rights you can put the DL in the From box, but it will not display the "on behalf of". "MMC [MCP]...

Control Toolbox: How do I use the "Combo Box?"
Hi, I need a drop down box in a particular cell, getting it there is easy, putting what I want in the drop down box/list is not! How do I get a "List" in the the combo box to read: 1, and then under that number I want 2, all the way up to 5. I want it to work so that I can see the drop down box and when it is selected I can see the numbers 1 thru 5 following each other in order, below each other. I have tried "Edit" under "Combo Box Object", that doesn't work! If I double click the "Combo Box" in the cell, I go into a "Microsoft Visual Basic&qu...

How do I add items to a custom pick list?
I added a custom field to CRM and specified a datatype of "PickList." This gives me a drop down box with a single item of value "default". How do I add other items to the list? NEVERMIND I found it. Under "properties" in custom design you hardcode the list. "Dave F" <dave@nospam.com> wrote in message news:uRBOlwAcEHA.2544@TK2MSFTNGP10.phx.gbl... > I added a custom field to CRM and specified a datatype of "PickList." > > This gives me a drop down box with a single item of value "default". > > How do I add...

How to Set Remember List Sorts in HQ Manager
How do you tell HQ Manager to remember List Sorts? I can't find any where to set HQ manager to remember Lists Sorts. To remember how you searched for a customer or Inventory Item. I know it is possible in SO Manager is not available in HQ? Thanks Just bumping this thread to try to get a Microsoft MSFT Monitor to answer it. -- HQ 2.0 / RMS 2.0 4 Lanes, 4 Back Office Server 2003 SP2 "DDowningMO" wrote: > How do you tell HQ Manager to remember List Sorts? > > I can't find any where to set HQ manager to remember Lists Sorts. To > remember how you searche...

Using Onenote with LiveScribe
Does anyone have any experience using LiveScribe with Onenote, or any other electronic note capturing device? I've used the Adapx capturx pen and even wrote a powertoy for a behavior it has that I did not like: http://blogs.msdn.com/johnguin/archive/2009/03/09/a-powertoy-to-make-using-adapx-pens-for-onenote-a-little-easier-for-me.aspx This is not an endoresement, just a note that I have used it. I don't think LiveScribe has direct support for OneNote yet but check their support pages before taking my word for it. What was your specific question? -- Thanks, John ...

Automate Update Query Parameters using Macros
Hi I am currently trying to automate a number of databases so that they can be run 'at the push of a button'. The first of these databases contains my master tables which are raw downloads from and antiquated mainframe system. The data comes down in text file format which is then imported into Access Tables. I then run a number of update queries against the tables to convert dates and codes etc into meaningful data. Previously I have run these update queries manually but now intend to use a number of macros to be able to run them at the push of a button. I am mainly using the OpenQu...

Access 2007 Run-time Error 2467 when accessing a listbox value on a sub form
I have a database that has been in production on Access 2003 for quite some time and has run without errors. A user's machine was recently upgraded to Access 2007 and now the database throughs a Run-time Error 2467 "The Expression you entered referes to an object that is closed or does not exist" on the following line that references a listbox value on a sub form. If Nz(Me.sfrm_Consultant_Contract_Request.Form.Cmb_Consultant.Value) = "" Then .. .. .. I changed the code to If Nz(Forms! frm_Main.sfrm_Consultant_Contract_Request.Form.Cmb_Consultant.Value) = ""...

How do I move what's Tasks in Archive Folders to the To Do List?
I have migrated data from an old Sharp organiser to Outlook, but unfortunately what was the To Do database on the organiser has gone into Outlook's Tasks in Archives Folders. I would like to move all those Tasks to Outlook's To Do List. How do I do that? tasks and to do lists are the same thing, it sounds like you just have to move them from archive folder tasks to the tasks under your mailbox. "CharlesB" wrote: > I have migrated data from an old Sharp organiser to Outlook, but > unfortunately what was the To Do database on the organiser has gone in...

Invalid Argument Creating New RUS or Address list
Invalid argument Facility: win32 ID no: 80070057 Exchange System Manager When I select new --> RUS I get this error is ESM. anyone have this issue? ...

subtotals using COUNT
Hi all, Newbie here I want to add subtotals to my spreadsheet. For example, I have a companyID, Employee ID, VacationDaysLeft, VacationDaysUsed. I'm trying to insert subtotals (grouping by company ID). I want to know how many employees used more than 10 days of vacation, and how many emplyees have more that 12 days of vacation left (this is an example only) If I try to use the subtotals feature in excel, it will break it down by companies, and it will generate subtotals for each company using SUBTOTAL(3,..) - where 3 is the id for COUNTA. CountA is not good enough. What I would need that t...

Sending Emails to users using templates?
Is it possible to have a Email sent based from a Template to be sent to an internal user within the company using the MSCRM system? It appears that Emails from Templates can only be sent to the external Contact/Account in question and not a internal user. Is this correct? Furthermore, if you cannot use templates - is it possible to include fields from the MSCRM object in question within the Subject/Contents of the 'vanilla' email produced by the Workflow Manager; or is this fixed to the static Subject/Contents defined by the workflow. Our problem stems from wanting an email...

Using the "IF" Statement
Hello, I've been driving myself just a bit nuts trying to resolve the following excel problem: If the numeric value in a given cell falls within a predefined range (500 to 1000), I want the value to be multiplied by a constant value (0.05). If the criteria is not met, I want the cell identified as the recipient of this multiplication to reflect a value of 0.00. Could someone please show me what this formula should look like? Certainly would appreciate it. --- Message posted from http://www.ExcelForum.com/ Hi Dave! Is this what you need? =IF(AND(A1>=500,A1<=1000),A1*0.05,0) ...

Date sold filter value
OK so I give up. What's the Filter Value supposed to be when I run a Daily Sales by Register Report? It defaults to 0. It doesn't accept a date - it wants a numerical value. J hi Jennifer use the <Today> instead of 0. that's all never give up we all are here for yous to help "Jennifer" wrote: > OK so I give up. What's the Filter Value supposed to be when I run a > Daily Sales by Register Report? It defaults to 0. It doesn't accept a > date - it wants a numerical value. > J > Akber Alwani wrote: > hi Jennifer > use the <T...

Consolidate not updating properly using vba
Hello, I create a code (below) using the macro recorder to refresh the data connection and consolidate the sales figures. My problem is that the code only seems to refresh the data connection, but the data is not consolidating correctly. Now if I go to Data -> Consolidate manually it works fine. How can I have my code work correctly that will consolidate properly. I made sure that I clear the contents before doing the consolidation. Can anyone help me. Thank you in advance. Cheers 'Refresh sales connection ActiveWorkbook.Connections("Query from SalesByDepartment")...

How to use XY Chart Labeler
As some of you have recommended here, I downloaded and installed the XY Chart labeler from AppsPro (http://www.appspro.com/). There does not seem to be a manual for it, so how do I get started using it? Thanks. -- ================================================= Do you like wine? Do you live in South Florida? Visit the MIAMI WINE TASTERS group at http://groups.yahoo.com/group/miamiWINE ================================================= To install the add-in, double-click on the XYChartLabeler.exe that you downloaded. Then, in Excel, choose Tools>Add-ins If XY Chart Labeler isn'...

Pivot table drops columns on refresh
Hello, I have a spreadsheet with appx 100 columns and 500 rows, that we use as a source for 15 odd pivot tables. When we refresh, about 30-40% of the time, it drops several columns. 20 of our columns are dates for the next 20 weeks of the year. When it drops them from the table, it also drops them from any subsequent calculated fields, forcing me to recreate the sheet multiple times. Strangely, it drops the first and last weeks of the months, but not the middle ones. Does anyone know of a way to get Excel to keep the tables from dropping fields? Thanks, jason This is a known pr...

Emailing from a Distribution List
I am trying to send an email using a distribution list. I don't want the email recipients to see the email addresses of all others who received this email. I do however want the email to be addressed to the person I am sending it to. If I use the BCC feature which is a good feature for hiding everyone's email addresses, the email iin the "To" box is addressed to myself not the person I am sending it to. To me this is weird. I think what I am looking for is a "blind To" feature not a "blind cc". Does anyone have any ideas as to how I c...

Using Ranges
Hi, I did an insert --> name --> define: a range that would automatically update: RangeName = "Sheet2!$C$4:$Q$"&COUNT(Sheet2!$E$4000)+3 So I want to write a macro that uses the range. x = Range("RangeName").Value gives the following error: Method Range of Object Global Failed when i set a range in the worksheet using the upper left hand box the macro works. Do you know how to fix this problem. Thanks That won't work - it's just an invalid string value, not a range. You need to use a named range whose definition actually returns a range, alon...

Automatic removal of data labels with 0 value in a chart
Hi, I have some charts that I want to remove only the data labels which value = 0. I know how to do this manually,I am only interested in finding out how to display data labels in a chart whose values are equal to 0. If the only way to do this is by using BVA code, would you please send me some code. Is there a toggle swith in the Excel Options just like the one that currently exists not to display 0 in a work sheet? I have about 100 columns in seven different charts that I need to edit manually every week and it is very time consuming. Thanks. You can use a simple custom num...

how to import item list into rms pos?
Hi, i have large list of items, how could i import them into RMS? any tools? -Cheng ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=49285b9f-5601-48f0-a664-6928de770384&...