Series Of Dependent Comboboxes In Userform That Show Unique Values Of Dynamic Tables

Mar 23, 2014

This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input.

The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.

Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.

View 3 Replies


ADVERTISEMENT

Dynamic 3 Deep Nested ComboBoxes On A UserForm

Jun 29, 2006

I have three ComboBoxes. I need the choice of the first ComboBox to detirmine what is shown in the second and the choice in the second to detirmine what is shown in the third. The lists will be growing as users add items.

View 2 Replies View Related

Unique Values In Pivot Tables

Jul 19, 2005

I have a list of purchase order line items. Each Purchase order could have multiple line items, so the list could have many occurances of the same purchase order number. I have designed a pivot table which summaries line items by ordering area (one of the fields in the list).

This works fine but what I would also like to check is the number of purchase orders by area. How can I acheive this? I would prefer not to use any helper columns but will adapt my application if this is needed.

View 8 Replies View Related

Excel 2010 :: Dynamic Changing Of Series Collection Values?

Jul 6, 2014

I still get the 'Object doesn't support this property or method. Error code 438' error. I am using Excel 2010

VB:
j = 3
Do Until j = 6
ActiveWorkbook.Sheets(k).Activate
Set chartX = ActiveSheet.ChartObjects("X")

[Code]....

The macro is supposed to dynamicaly adjust series range for 3 charts for x,y,z, values in each of sheets. Charts are a line type.

View 3 Replies View Related

Fetch Unique Values - Dynamic

Nov 5, 2009

I am trying to find unique values on one page and copy/paste them on another page (all within one workbook). I tried to use advanced filter, but ran on some problems: i) it does not copy values on to the new page ii) it is not dynamic.

my information is entered on a daily basis, which is why I need the filter (or code) to be dynamic. The information, however, is not stored, but rather generates a daily invoice, this is why I want find only unique values to appear on a new page (invoice).

View 10 Replies View Related

Show Chart Data Series Labels On All Series. 2007

May 31, 2008

I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.

I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?

View 4 Replies View Related

Show Unique Values From Two Or More Columns

Dec 21, 2008

For input I have 2 columns:
A---B
1---5
3---4
-----
2----
I need for output column C:
C
1
2
3
4
5
It's possible to do that using one or more function ?

View 2 Replies View Related

Dynamic Filtering Comboboxes?

Nov 28, 2012

I am having some difficulty trying to implement the following. Lets say I have a reference list

X 1
X 2
X 3
Y 4
Z 5
Z 6

I want to have two drop downs on my worksheet. One that filters by x,y,z and then based on the option i select there only give me the corresponding values in the second drop down.

Eg if i select X in my drop down I only want 1,2,3 as options in my second drop down. Or if I select Y only the value 4.

View 2 Replies View Related

Count Unique Values - Show Each Line Which Includes Other Data?

Mar 6, 2014

I need to count the # of unique employee #s based on the criteria in column B. If the value in column B=0 then I do not want to count the employee #.

I realize a pivot table would be much easier but I need to show each line which includes other data.

View 6 Replies View Related

Two Comboboxes On A Userform

Jan 20, 2009

I have two comboboxes on a userform, they both get there list from the same formula. What I am trying to do is have the second combobox have it's selection preset based on the selection in combobox 1.

ie
Combobox1 = 6:00 AM
when you click on the dropdown for combobox2 i would like 6:00 AM to be the first selection possible, but I dont want it displayed in the box unless it is selected.

Dim timdat1(1 To 85)
For i = 1 To 85
timdat1(i) = Format(TimeSerial(5, (i + 1) * 15, 0) - Int(TimeSerial(5, (i + 1) * 15, 0)), "h:mm AM/PM")
Next i
combobox1.List = timdat1
combobox2.List = timdat1
i am at a loss for where to go from here

View 9 Replies View Related

Excel 2007 :: How To Get Dropdown Box Results To Show Unique Values And Non-Blank Entries

Sep 27, 2012

I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?

View 4 Replies View Related

Add Comboboxes On Userform At Run Time

May 7, 2009

I have a userform that has a text box. If user puts a number in it and click on proceed the userform must expand and display that many comboboxes. for e.g. if user inputs 8 and then click on proceed then there should be 8 comboboxes on the form. Is it possible to do?

View 4 Replies View Related

Multiple Comboboxes In A Userform

Feb 17, 2007

I have a worksheet "Master Log" with a UserForm "UserForm2" set up that has 4 comboboxes and 2 text entries. I already have the filtered unique values for each combobox sent to columns "O" thru "R" and they are dynamic ranges. I have the code to populate one of the comboboxes in the userform but cant figure out how to modify the code to have all four populated without getting ambiguous entry errors. And also, do I have to initiate the form for each combobox? Below is the code Im using for a single combobox.

Private Sub UserForm_Initialize()
Dim MyUniqueList As Variant, i As Long
With Me.ListBox1
.Clear ' clear the listbox content
MyUniqueList = UniqueItemList(Range("o4:o100"), True)
For i = 1 To UBound(MyUniqueList)
.AddItem MyUniqueList(i)
Next i
.ListIndex = 0 ' select the first item
End With
End Sub


Private Function UniqueItemList(InputRange As Range, _
HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count > 0 Then
Redim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _
Application.WorksheetFunction.Transpose(UniqueItemList)
End If
End If
On Error Goto 0
End Function

View 2 Replies View Related

Populate Userform Combobox With Unique Values From A Range

Jul 26, 2012

I found this thread which deal with populating unique values in my listbox.

Here is the thread link. [URL] ........

What these line of code mean (the ones in red).

Code:
Private Sub UserForm_Initialize()

Dim v, e
With Sheets("maintenance").Range("c2:c500")
v = .Value
End With
With CreateObject("scripting.dictionary")

[Code] ......

Note : I simple tried to check the value of v by giving msgbox (v), just below the line v = .Value.

But I got run-time error 13 : Type mismatch error.

View 5 Replies View Related

How To Empty All Comboboxes And Textboxes In A Userform

May 3, 2009

I have a userform with many comboboxes and textboxes and I am using the following code to empty those controls:

View 9 Replies View Related

Userform To Use 2 Comboboxes To Hide Sheets

Jun 6, 2013

Let's say I have Sheet1-Sheet6. I also have combobox1 (with item 1, 2, & 3 as the list items) & combobox 2 (with items 1 & 2 as the list items).

If combobox1 = 1 & combobox2 = 1 then hide Sheets 2,3,4,5,6
if combobox1 = 1 & combobox2 = 2 then hide Sheets 1,2,4,5,6
If combobox1 = 1 & combobox2 = 3 then hide sheets 1,2,3,4,6
If combobox1 = 2 & combobox2 = 1 then hide sheets 1,3,4,5,6
If combobox1 = 2 & combobox2 = 2 then hide sheets 1,2,3,5,6
If combobox1 = 2 & combobox2 = 3 then hide sheets 1,2,3,4,5

I would like to also make both of these combo boxes required fields and to default text to say 'Select One...'

View 1 Replies View Related

Unusual Crashing Due To Userform Or Comboboxes

Jun 4, 2008

The spreadsheet is essentially a VBA GUI that validates information entered before writing it to a sheet in the workbook. There is a button on the first sheet that opens the GUI, and when the workbook is first used the GUI opens and runs fine. After adding a few rows using the GUI, saving the workbook, and then reopening it, attempting to open the GUI by clicking on the button will cause Excel to crash (and no errors are given). To compound the problem, it is not possible to find the issue by using the debugger, as the GUI runs fine as soon as VisualBasic is opened. I've tried to narrow it down by using MsgBoxes to find the approximate location where the form crashes, and it seems to happen when the .ListIndex property of a ComboBox is set inside the UserForm_Initialise method. I've played around for days trying to narrow it down further, removing .ListIndex statements as much as possible without breaking the entire thing.

View 2 Replies View Related

Ok Button To Control Comboboxes In Userform And Sheet

Feb 20, 2012

I Have a sheet with 4 activex comboboxes and 3 text boxes. If the right item is selected in the second combo box a user form opens up. That has 4 text boxes. It has a command button titled ok that takes the information from the 4 text boxes and puts them in a sheet called data.

Code:
Private Sub cmbOK_Click()
With Worksheets("Data").Range("A1")
.Offset(1, 8).Value = Me.txtFirm.Value

[Code]....

I need a way for the above code to run when the ok button on the user form is clicked.

View 7 Replies View Related

Returning Totals In A Series Of Tables

Dec 29, 2008

I have a data dump which is a series of tables, the layout and headers of the tables are fairly fixed however the number of rows in each table is constantly changing. What i would like to do is return the table header, and the average turnaround for each table, preferably without amending the tables. Attached is a sample, these tables marked task C1 task C2 etc are the table headers and in the cell next to Average turnaround is the value i would like to return. I would also like to return the bottom value of each table in column C so at the end it should look like:

Task C1 434 2.52
Task C2 16 3.00
Task C3 10 2.20
Task C4 329 3.24

I do not want to amend or adjust the table as this sheet continues on for 3000 more tables, which is the reason i am looking for a VB lookup type function.

Sheet Attached..

View 9 Replies View Related

Dynamic/Dependent Lisbox.

Nov 10, 2009

I would like To display product information in a multi column listbox. Column 1 is a product category and Column 2 is the product description. Now for the dynamic part. I would like to have a combobox loaded with non duplicated values of column 1 of the listbox. When "~Caulking~" is selected from the combobox. The listbox is to display only the "~Caulking~"products and their description. And having a value of "~All Items~" placed into the combobox will display All products in the listbox.

The sheet that will contain the raw data source can/will have items added to inceacing the row count. Also the sheet will be left in an unknown sort by column state. I thought of taking the raw data source and copying it to a new sheet and splitting the data into various columns, Eg 1 column for "~Caulking~" another for "~All Items~"... and then removing the sheet on userfrom completion. Or possably using collections or scripting dictonary or anythink that works well.

View 3 Replies View Related

Listbox With Unique And Dependent Items - VBA

Sep 25, 2013

I have a Listbox1 in which I was able to populate it with unique items (from column B in Programs worksheet) using a collection (that was a good achievement for me!)

Now, I want to populate a the Listbox2 with items (not repeated) from column C, BUT only if the correspondent value on column B is equal to listbox1.value.

[Code] .....

View 4 Replies View Related

Create Dependent Dropdown With Dynamic List?

Apr 1, 2014

I need to create a 2-stage dependent drop down list.

Stage 1:

In cell G7 (on a tab called 'Data'), I have created a drop down list based on a range name called 'Region' .

Stage 2:

In cell G8, I need to create a drop down list which is dependent on the value chosen in G7.

Problem is, I will need to create a dynamic range name here which needs to vlookup or match the value from G7 in a dynamic list of data (the data is on different tab called 'RCA Data' and is spread over columns A:E with row A for headings - the data will go down an undetermined number of rows)

Every time the value in G7 matches a value in the 'RCA data' (the potential matches are stored in column

D) I need the corresponding cell value in column C to be added to the dynamic list. This list then needs to be available to choose from a drop down list in G8.

View 1 Replies View Related

Multiple Column Dynamic Dependent List

Jun 18, 2014

I am attempting to create a form in excel with dynamic data validation fields. What has me stumped is that I want to create multiple complex dependencies (for instance, if you select something from a drop-down list in the first field, that then gives you a specific range in the second field. Then based on what you placed in the second field it gives you a specific range in the third field.). What I can't seem to work out is the formula for making this work.

Is this impossible? Also sometimes the third field my be blank.

Here is an example of what I am trying to accomplish

Assets 2.PNG

View 1 Replies View Related

Create Dynamic Dependent Dropdown Menus

Mar 11, 2013

I need the drop down menu to update when the user adds a new entry. For example, if the user decides to add companies to the financial table, no matter how many new rows they add, our drop down menu at the top will update to include the new the entries. I want this to be the case in every table. How do I do this?

View 5 Replies View Related

Populate 5 Comboboxes With Values 1 - 10

Mar 3, 2014

Within a userform, I want to populate five combo boxes and with the values of 1 - 10. I looked through several excel websites and found several solutions. However, I could not figure out how to adopt their solutions to my specific case.

My naming convention for the combo boxes are: cmboPeriod1, cmboPeriod2, ... , cmboPeriod5.

[Code].........

I know my with statement is incorrect. I know the compiler will automatically think 'cmboPeriod' is a variable. I just don't know how to get it 'cmboPeriod & j' to do what I really intend for it to do.

View 4 Replies View Related

Dependent Data Validation With Dynamic Named Ranges?

Aug 7, 2014

I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.

See attached.

The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.

Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!

View 4 Replies View Related

Show List Dependent On A Cell Content?

Jul 31, 2014

I'm trying to create a form where you can select names from a list dependant on what depot has been selected

On Sheet 1 along the first row are names of depots, under each of these are a list of names (number of name varies)

On Sheet 2, C2 is a data validation list, with source being =Sheet1!A4:E4

Is it possible to make C3 show a list of the names that's dependant on what's selected in C2

View 2 Replies View Related

Dependent Dropdown Lists Based On Dynamic Data Range?

Apr 19, 2013

I have data that resembles (I have also attached an example workbook as well if that is easier)

Column A.................Column B................Column C..................Column D.............Column E................Column F
Year.......................Quarter...................Month..................Product-Code...........[data A]................[data B]
2013.........................Q1.........................Jan........................SD-21...................13%......................0.05%
2013.........................Q2..........................Apr........................NV-12...................7%.......................6%

etc

I am hoping to have, on another sheet, a series of dependent drop boxes- Year, Quarter, Month, SKU- which after selecting then displays 'dataA' & 'dataB'.

So you could select- List 1: 2013 ----> List 2: Q1 ----> List 3: Jan ----> SD-21.................and then 13% and 0.05% are displayed.

The data will be continuously added to and so needs to be based on a dynamic data range.

I know that a pivot table is the perfect way to do this, however my bosses have requested that I do not use that format so it is easy for all staff to access.

So I think I need to construct some sort of dashboard sheet, I have experimented with OFFSET from other posts, but so far have had no joy.

View 14 Replies View Related

Generating List Of Combinations With Both Unique / Independent And Dependent Input

Mar 26, 2012

I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.

The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->

Joe A   1001
Joe A   1002
Joe A   1003
Jill B     1001
Jill B     1002
Jill B     1003

To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.

My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?

Code:
Sub AccessList()
Sheets("requests").Select
Dim rng As Range, c As Range
Dim rng1 As Range, c1 As Range
Dim rng2 As Range, c2 As Range

[Code]....

View 1 Replies View Related

Dynamic Series For A Graph

Jun 30, 2014

I have a graph that auto updates but sometimes there is only 1 series and sometimes there is 2,3,4, or 5 series. Is there a way to have the graph update accordingly? and only have have the series that are valid be in the key on the graph. Currently the graph keeps all the series in the key even when there isn't any data for it.

Please see attachment. Use sheet 2 for the graph. and use the drop down menu to select catagory. this ia very quick mock up of the concept.

Graphseries.xlsx

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved