How do I build a dynamic list?

I want to make it easier for my salesfoce to build a Bill Of Material. I have 
a sheet that the users will not be modifing which contains a full table from 
an SQL database on an MSSQL server. This is my master list of items available 
to be put into the BOM. I want to take the second sheet and put in a drop 
down list describing the product classes. My user will select the type of 
product and this will then need to search the other sheet and populate the 
list. For example, the user picks a product class of assembly. All assemblies 
are listed with a part number starting with 'AS'. So I want the list to 
populate with all of the AS part numbers from the other sheet. I would also 
like the part number list to show the description of the part chosen. Most 
people don't remember the parts by the number but by the name. So after they 
select the assembly attribute the list created for them to select the part 
from should show them the name of the assembly not the part number. 

Could one of you please point me in the correct direction of how to do this? 
I was thinking that I should place a VBA button on the sheet. After the 
product class has been selected then that button could trigger the creation 
of the list. I don't know how to create that list in the VBA and have it 
display on the sheet as a drop down. Any and all help would be appreciated.

L.E.White
0
11/18/2004 3:04:02 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
371 Views

Similar Articles

[PageSpeed] 55

You can probably learn most/all of what you need here. If not, post back
      http://www.contextures.com/tiptech.html

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"LEWhite" <lwhite@appliedcomposites.com.XYZ.com> wrote in message
news:B1A62751-F915-433C-B22A-C0E942F8AB64@microsoft.com...
> I want to make it easier for my salesfoce to build a Bill Of Material. I
have
> a sheet that the users will not be modifing which contains a full table
from
> an SQL database on an MSSQL server. This is my master list of items
available
> to be put into the BOM. I want to take the second sheet and put in a drop
> down list describing the product classes. My user will select the type of
> product and this will then need to search the other sheet and populate the
> list. For example, the user picks a product class of assembly. All
assemblies
> are listed with a part number starting with 'AS'. So I want the list to
> populate with all of the AS part numbers from the other sheet. I would
also
> like the part number list to show the description of the part chosen. Most
> people don't remember the parts by the number but by the name. So after
they
> select the assembly attribute the list created for them to select the part
> from should show them the name of the assembly not the part number.
>
> Could one of you please point me in the correct direction of how to do
this?
> I was thinking that I should place a VBA button on the sheet. After the
> product class has been selected then that button could trigger the
creation
> of the list. I don't know how to create that list in the VBA and have it
> display on the sheet as a drop down. Any and all help would be
appreciated.
>
> L.E.White


0
Don
11/18/2004 3:08:32 PM
Thank you for the link. I am reading the site but have not been able to find 
what I am looking for. In all honesty some of this has me more confused than 
I thought I was before. Could you please provide a little more information on 
how you would go about this? Is there a better way to do this than the VBA 
button I am thinking of?

LEWhite


"Don Guillett" wrote:

> You can probably learn most/all of what you need here. If not, post back
>       http://www.contextures.com/tiptech.html
> 
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com

0
11/18/2004 7:07:31 PM
I'm not sure how the SQL stuff fits in, but Debra Dalgleish has some notes how
to use Data|Validation and dependent lists
at:

http://www.contextures.com/xlDataVal02.html

But I think there could be some (a lot of) manual effort to make those lists and
range names.

LEWhite wrote:
> 
> I want to make it easier for my salesfoce to build a Bill Of Material. I have
> a sheet that the users will not be modifing which contains a full table from
> an SQL database on an MSSQL server. This is my master list of items available
> to be put into the BOM. I want to take the second sheet and put in a drop
> down list describing the product classes. My user will select the type of
> product and this will then need to search the other sheet and populate the
> list. For example, the user picks a product class of assembly. All assemblies
> are listed with a part number starting with 'AS'. So I want the list to
> populate with all of the AS part numbers from the other sheet. I would also
> like the part number list to show the description of the part chosen. Most
> people don't remember the parts by the number but by the name. So after they
> select the assembly attribute the list created for them to select the part
> from should show them the name of the assembly not the part number.
> 
> Could one of you please point me in the correct direction of how to do this?
> I was thinking that I should place a VBA button on the sheet. After the
> product class has been selected then that button could trigger the creation
> of the list. I don't know how to create that list in the VBA and have it
> display on the sheet as a drop down. Any and all help would be appreciated.
> 
> L.E.White

-- 

Dave Peterson
0
ec357201 (5290)
11/19/2004 1:44:43 AM
All that the SQL really does is build my database for me. 
It is already imported as a seperate sheet. When it 
imports it has not been sorted by the part number so I 
can always put that in there if it will make things 
easier. I understand building a named list through data 
validation. The problem I am having is that I don't seem 
to be able to alter the list automatically. Once I select 
a product class on my quote I want the contents of the 
list to change. I need a formual to say something like...

 IF (product class=1) THEN list is all in range 
A1:A10,000 with part number starting with 'AS', ELSE list 
is all part numbers starting with 'AA'

So I need to find all of the enteries that fit the 
product class and build a list out of them. That list 
will then be what I want to link to the descriptions. If 
I need to be using named lists I run into the problem 
that the items in the list change regularly. This list 
will need to be able to update based on finding all of 
the matching items. I may need to search the imported 
database each time but I don't know how to write that 
formula. So far I have not been able to write a formula 
into a list at all.

LEWhite 


>-----Original Message-----
>I'm not sure how the SQL stuff fits in, but Debra 
Dalgleish has some notes how
>to use Data|Validation and dependent lists
>at:
>
>http://www.contextures.com/xlDataVal02.html
>
>But I think there could be some (a lot of) manual effort 
to make those lists and
>range names.
>

0
anonymous (74722)
11/19/2004 1:32:06 PM
I put two comboboxes from the Control Toolbox toolbar (not the Forms toolbar) on
a Sheet1.  I put a bunch of test data in sheet2 (A1:A500).

Then rightclick on the worksheet tab for Sheet1 and selected View Code.  Paste
this into the code window:

Option Explicit
Private Sub ComboBox1_Change()

    Dim myRng As Range
    Dim myCell As Range
    Dim myPfx As String
    
    With Worksheets("sheet2")
        Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    Me.ComboBox2.Clear
    
    If Me.ComboBox1.ListIndex < 0 Then
        'do nothing
    Else
        Select Case Me.ComboBox1.ListIndex
            Case Is = 0 'All Parts
                myPfx = "*"
            Case Is = 1 'Assembly
                myPfx = "as*"
            Case Is = 2 'pcb
                myPfx = "fb*"
            Case Is = 3 'component
                myPfx = "d*"
            Case Is = 4 'documentation
                myPfx = "sp*"
            Case Else
                myPfx = "*"  'just in case
        End Select
    End If
    
    For Each myCell In myRng.Cells
        If LCase(myCell.Value) Like LCase(myPfx) Then
            Me.ComboBox2.AddItem myCell.Value
        End If
    Next myCell
        
End Sub

Then copy this to a General module (Insert|Module from the menubar):

Option Explicit
Public BlkProc As Boolean
Sub Auto_open()
    BlkProc = True
    With Worksheets("sheet1").ComboBox1
        .Clear
        .Style = fmStyleDropDownList
        .AddItem "All Parts"
        .AddItem "Assembly"
        .AddItem "PCB"
        .AddItem "Component"
        .AddItem "Documentation"
    End With
    Worksheets("sheet1").ComboBox2.Clear
    BlkProc = False
End Sub

When you open the workbook, the auto_open routine will run and populate
combobox1 with options.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


anonymous@discussions.microsoft.com wrote:
> 
> All that the SQL really does is build my database for me.
> It is already imported as a seperate sheet. When it
> imports it has not been sorted by the part number so I
> can always put that in there if it will make things
> easier. I understand building a named list through data
> validation. The problem I am having is that I don't seem
> to be able to alter the list automatically. Once I select
> a product class on my quote I want the contents of the
> list to change. I need a formual to say something like...
> 
>  IF (product class=1) THEN list is all in range
> A1:A10,000 with part number starting with 'AS', ELSE list
> is all part numbers starting with 'AA'
> 
> So I need to find all of the enteries that fit the
> product class and build a list out of them. That list
> will then be what I want to link to the descriptions. If
> I need to be using named lists I run into the problem
> that the items in the list change regularly. This list
> will need to be able to update based on finding all of
> the matching items. I may need to search the imported
> database each time but I don't know how to write that
> formula. So far I have not been able to write a formula
> into a list at all.
> 
> LEWhite
> 
> >-----Original Message-----
> >I'm not sure how the SQL stuff fits in, but Debra
> Dalgleish has some notes how
> >to use Data|Validation and dependent lists
> >at:
> >
> >http://www.contextures.com/xlDataVal02.html
> >
> >But I think there could be some (a lot of) manual effort
> to make those lists and
> >range names.
> >

-- 

Dave Peterson
0
ec357201 (5290)
11/19/2004 9:23:41 PM
I wanted to say thank you. That code does solve my problem and with a little 
work it will finish my spread sheet. 

I really appreciate the time that both of you took in responding to my 
request. The links have also provided me with a lot of information that I can 
use to improve what I am already doing.

With Best Regards
LEWhite
0
11/24/2004 1:23:03 PM
Reply:

Similar Artilces:

Listing in previous records in Form by vertical scrolling
Please help, I would like to set up a form the way that if I enter the record I would like to see that record and access should open a new line where I can add a new record. I would like to scroll vertically in the old record to edit them eventually. I know that there can be a Record selection but I don't want that. Thank you for help. I cannot find it myself. On Wed, 28 Nov 2007 07:40:01 -0800, Jan <Jan@discussions.microsoft.com> wrote: >Please help, > >I would like to set up a form the way that if I enter the record I would >like to see that record and access s...

building a worksheet row by row
I hope somebody can assist. My worksheet calculates rows of data, which I wish to slide into another worksheet and secure; then change the original data, slide those results under the first. Keep repeating. Is there a way to do this without manually pasting values each time? David Dave "Sliding" rows from one sheet to another would involve the use of VBA. See Ron de Bruin's site for moving/copying rows to next available empty rows. http://www.rondebruin.nl/copy1.htm Gord Dibben Excel MVP On Thu, 4 Nov 2004 14:04:03 -0800, "DaveButcher" <DaveButcher@discussio...

master list for auto complete data?
Is it possible to have a master list, in my case it's of names, in a workbook that excel will use for auto complete data for that workbook? Thanks Hi Tangy Go to "Tools>Auto Correct Options", select the "Auto Correct" tab and enter the abbreviated items and full names for those items you want Excel to replace. Ensure the relevant boxes are ticked. -- XL2002 Regards William willwest22@yahoo.com "Tangy" <t a n g y@rogers.com> wrote in message news:_OqdnbKPa7IShELcRVn-jQ@rogers.com... | Is it possible to have a master list, in my case it'...

Can I Build an Access Statement (in 2007) as a String and Execute
Hi, I know that I can build a SQL statement within a string and then execute it like the following: strSQL = "UPDATE tblAuditRpt " _ & "SET tblAuditRpt.AuditRptYesCount = " & lngInitialEvalCount & " " _ & "WHERE tblAuditRpt.AuditRptID=25;" CurrentDb.Execute strSQL, dbFailOnError Can I do the same with an Access statement itself? For example, How could I execute the following statement if I built it in a string? lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetai...

Merging Dynamics.
hi, My question is related to merging dynamics of two different companies, Currently both companies are located at different geographical locations, they need to merg these databases,like ONE DYNAMICS and Multiple Companies, but the problem is that one have MC enabled the other one there is no MC, Please suggest any solution if someone have any idea. Sajid Do both Company databases have the exact same GL structure. That is the exact same segment structure and the exat same numbering system within the segments. "mbs" wrote: > hi, > > My question is related to mer...

Building Assembly
I'm trying to build an assembly that creates a directory that is named after an attribute in opportunities. this is what I have, and it is not compiling into a dll, any advice? public string CreateDirectory(string root,string folderName) { } Luke Davis, MCSE: Security DEM Networks - Senior Systems Architect 7225 N First, Suite 105 Fresno, CA 93720 Office: 1 (559) 439-1000 Fax: 1 (866) 640-2041 www.demnetworks.com ...

SUM determined by drop-down list
HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for &...

activity list freezes
In the past few days our users have been reporting that their activity list does not load. Everything else seems to be fine, and when I look at the SQL Server activity monitor, I see a suspended process with open transactions that involve an update or insert on the activity table. If I manually kill this process, the activity list loads fine. I ran the SQL profiler and was able to link the problem to one specific user who was trying to assign an activity to another user. She would get an error, and then everyone's activity lists would hang. She said she's not doing anything dif...

Dynamics GP 10
We are getting ready to upgrade our Production server to GP 10 in a couple weeks. Some people that were at Convergence this year heard that there is a way(SQL Script) to transfer the new security that we have set up in version 10 from our Dev server to our Prod server. This would obviously save the need to re-create all the new security roles and access. I have read post that mention some tools you can purchase from vendors but we were curious about an SQL script. Has anyone used such a script to perform this task? Thanks very much Scott T On 7 May, 14:49, Scott Trenholm <bcsa...@ho...

Script error found during opening Dynamics
I just upgrade to the latest Canada Payroll tax update, when one of the user who sign on to Dynamics after upgrade, she got the "Internet Explorer Script Error" window pop up and saying "An error has occurred in the script on this page" Line: 335 Char: 6 Error: The data is invalid Code: 0 URL: file:///C:/Documents%20and%20Settings/UserA/Local%20Settings/Temp/tmpAD1.tmp I tried to clear all the temp file in this folder but still no luck to get rid of this message during loading Dynamics. I asked the user to click on "Yes" button to continue "D...

Building Kits
I am having trouble using the build kits tab. I went through and made sure all the components were available to build the kits. I click the build tab and the program prompts that there is enough inventory to build 2 kits. I enter 2 and then hit the build tab and then ok. When I go to the properties screen the inventory qty is still 0. I copied the kit and gave it another name and did the same process and was able to build the kits the first and only time. Any suggestions? known bug, apply the latest hotfix and you will be good to go. "akpetshop" <akpetshop@discussions.mic...

Dynamics CRM 4 without Exchange but with Lotus Domino
Hi everyone, I'd like to know if in Dynamics CRM v4 you need exchange to route the incoming mail to the queues, because I have heard that in version 4 you don't need to have exchange server. We have Lotus Domino as email server and we'd like to get the incoming mail via E-mail Router Configuration Mnager in Dynamics CRM v4. I don't know if these steps are correct: 1. Create two profiles (one for incoming mails and one for outgoing mails) 2. Create a new deployment to connect with dynamics CRM server. 3. Load Data. 4. Modify the incoming or outgoing profile for a user or que...

Extract data from list -exluding blank cells
Tried the C.Pearson site.... Couldn't get that formula to work. =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)- COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL( (IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))), ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))) Need to sort order of results from another formula into a list without blank cells. List #1 List #2 cherries cherries plums plums apples apples oranges oranges grapes grapes lemons lemons Perhaps a simpler non-array alt...

Macro to build table
I have 6 questions that may be answered yes or no, and for the sake of keeping everything in integer form, let's assume "1" for yes and "0" for no. Depending on how a customer answers all 6 questions, they will receive a predetermined score. I am attempting to develop the answer key, so-to-speak, to refer the reps to once they are finished asking the customer these questions. I was hoping for the code that would build a macro to create this tabe for me - a table that would lay out all of the 6 question-answer combinations, like the following: Questi...

Sum workdays by month from List of Start/End dates
Hi there, I have a list of start and end dates that mark when holidays were begun and ended. Some holidays were begun at the end of one month and bridged onto the following month (ie: 26/01/09 to 06/02/09). An example list in cells (A1:B6): Start Date End Date 05/01/09 06/01/09 14/01/09 15/01/09 26/01/09 06/02/09 18/02/09 19/02/09 09/03/09 13/03/09 I need to find out the following using a FORMULA preferably in one column rather than spread out over several (please NO MACRO): I need to have a list of months showing how many days were taken in each month (say la...

Insert Doctype Node Dynamically
I am receiving xml documents from a customer without a reference to a doctype. I know what the Doctype DTD should be need to insert the declaration as follows <?xml version="1.0" encoding="UTF-8"?> <!-- start of add validation tag --> <!DOCTYPE D1XML SYSTEM "http://url/myDTD.DTD"> <!-- end of add validation tag --> <XML.....> Using one of the provided framework classes such as XmlValidatingReader, etc.... is there a method to dynamically add a doctype reference? Or do I have to find the starting node and perform an insert to the do...

No NCI on Hardware Compat. List, but RMS has NCI OPOS drivers.
There are no NCI scales listed on the Hardware Compatibility list, but built into RMS are OPOS drivers for NCI scales. Any idea or experience with getting an NCI scale to work with RMS? Any help will be appreciated. ---------------- 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. ht...

Distribution Lists #10
I have a number of Distribution lists set up in Active Directory containing AD users. However, I have been asked to add a couple of external users to one of these lists. Added the person as a "Contact" and added them to the list with their @yahoo.co.uk address. However, when an email is sent to that list the sender and the postmaster get an error saying: The following recipient(s) could not be reached: CN="Person's Name",OU=Contacts,OU=Distribution, \\ OU=Groups,DC="DOMAIN",DC="SLD",DC="TLD" on 20/04/2005 10:26 The e-mail address co...

All address lists
Hi, I have a exchange 2003 setup. What i am tryng to do is run 2 companies on the same server. All is fine appart from the "all address lists" address book entries. I have managed to stop each company seeing into the others address book but they can still see the address book listed at the top level. Does anyone know if there is anyway to hide this? Thanks Kevin ...

Dynamic Range
I have a dynamic range which i use Range("A3").End(xlDown).Offset(-1, 0).Select to find the last cell with data in. Then i use ActiveCell.Resize(1, 12).Select to select across to column L What i can't seem to do is select up from ActiveCell in this case A363 (this is dynamic) to L363 to A3:L3 (this range is then required to be copied) Any help would be appreciated Thanks in advance Arnie How about Range(ActiveCell, ActiveCell.End(xlUp)).Resize(,12) -- __________________________________ HTH Bob "Arnie" <Arnie@discussions.microsoft.com> wrote in mes...

Find Distribution Lists that a user is the owner
Hello, Is there a quick way to list out all the Distribution Lists that a particular user is the owner? When we have users leave the company, we constantly get requests to transfer ownership. Thanks, Sun Sun <Sun@discussions.microsoft.com> wrote: >Is there a quick way to list out all the Distribution Lists that a >particular user is the owner? When we have users leave the company, we >constantly get requests to transfer ownership. Not without writing a small script to query the AD. And, if the user account is deleted from the AD before you ask the question, you wo...

Dynamic Step Chart using range names
Let's create a step chart to illustrate Fed Funds Rates Data at http://www.federalreserve.gov/fomc/fundsrate.htm I create a new book and save it as "Step.xls" (a short name is good idea... see at III) | A B --|----------- ---- 1| Date Fed 2|03-Jan-2000 5.50 3|02-Feb-2000 5.75 4|21-Mar-2000 6.00 5|16-May-2000 6.50 6|03-Jan-2001 6.00 7|31-Jan-2001 5.50 8|15-Feb-2001 5.50 9| II. I have to define Range Names with Insert,Name,Define... 1. Date: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) => I start f...

Drop Down List Questions
Hi, I have a couple of problems with drop down lists I was hoping to get some help on: 1) The drop down lists appear with the list displayed in the middle of the list instead on the first item, can that be changed so the list appears at the top of the list instead of in the middle? 2) Two of the columns are an item name (A) followed by the item number (B). Is it possible to have the item number in column B enter automaticly based on the name I select in column A? Thanks in advance, Tagg Hi for the second question you may use VLOOKUP. See: http://www.mvps.org/dmcritchie/excel/vlooku...

Entourage Build Information
Processor: Power PC Is there a matrix of Entourage versions and their respective build numbers anyone can provide? This information would be of great use in troubleshooting client/server issues. Thanks in advance, Chris I haven't checked specifically for Entourage, but Wikipedia usually has such data published - you might check there. HTH |:>) Bob Jones [MVP] Office:Mac On 11/13/08 3:24 PM, in article 59b61e39.-1@webcrossing.caR9absDaxw, "chrisxmartinez@officeformac.com" <chrisxmartinez@officeformac.com> wrote: > Processor: Power PC > > Is there a ma...

Error when building payroll build
One of my users is continuously getting an error when clicking OK on Calculate Payroll Checks. The error is - The checks cannot be calculated. Errors were found when building the checks. All other users can build the exact same build with no issues. What could be the issue here? Jack-One thing you may want to check is compare the users security. One user may/may not have rights to benefits/deductions/batches, etc. "Jack Tundra" wrote: > One of my users is continuously getting an error when clicking OK on > Calculate Payroll Checks. The error is - The checks cannot b...