Auto Populate Distinct List From One To Another Table

Sep 12, 2012

I have two tables on two different worksheets. I would like to automatically populate a distinct list into column A in table 2 from the data in column A in table 1.

Table 2 must auto populate from table 2 every time a new row is added into table 1 - without any extra user interaction e.g. pressing a button etc. No offense intended to my better half but if she has to do anything more than enter a new row in table 1 she'll likely manage to screw it up after a week

I kind of managed to get it working using the below formula but both sets of data must be in tables so I can't copy the formula down the column without creating redundant table rows.

=INDEX(Classes!$A$2:$A$999, MATCH(0, COUNTIF($A$1:A1, Classes!$A$2:$A$9), 0))

Example

Sheet 1 = "Classes"
Column A = "Student Names"

John
Mike
Paul
Andrew
Paul
Paul
Mike

Sheet 2 = "Total Due"
Column A = "Student Names"

Paul
Mike

View 3 Replies


ADVERTISEMENT

Auto-populate Combobox - Extracts Unique Distinct Values From Column

Jan 11, 2013

The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.

Sub FilterUniqueData()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)
On Error Resume Next
With Worksheets("Sheet1")

[Code]....

The vba code below copies the selected value to cell C5 whenever the combobox is selected

Copy the code into a standard module.Assign this macro to the combobox

Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value)
End With
End Sub

Vba code belows instanly refresh the combobox when values are added/edited or removed from column A,

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$A:$A")) Is Nothing Then
Call FilterUniqueData
End If
End Sub

My problem is this:

Everything will be working fine if the all combobox and data can be found in 1 sheet, but how could i make some tweaks if the combo box is located in sheet 2 provided your data to be populated is in sheet 1, i tried everything but the combobox doesnt update,

View 1 Replies View Related

Formula To Auto-populate Data In A Table From Another Table?

May 13, 2013

I have a table below that looks like this:

LOC
# of days

DTX
3

RTC
3

PHP
12

IOP
12

The # of days column will be a manual input. Then I have a larger table that will take those values and convert them to look like this: (Obviously the dollar values are pulled from somewhere else)

1
DTX
$ 1,292.00

2
DTX
$ 1,292.00

3
DTX
$ 1,292.00

[code]....

I've been thinking of trying to use a macro but not sure that is the best way. Using a button or something doesn't seem very elegant either.

View 7 Replies View Related

Auto-Populate Cells In A Table

Aug 27, 2009

I have my projection formulas that use the date and age to estimate production on each farm. Basically the age is calculated first and then the production rate is estimated. I have been trying to figure out if, instead of having a formula in each cell, can I use VBA to populate the ages and then the production.

Here is an example:

8/22/2009

FarmAge8/22/09Age8/29/09Age9/5/09Age9/12/09Age9/19/09Age9/26/09Age10/3/09Farm 1
34181351813617937179381793917940177Farm 2
39223402204122042220432204422045218
All the functions I have created work, I just can't have the worksheet auto-calculate due to the total number of cells with functions. Calculating the worksheet takes approx 1 min...

I should be able to adjust any code to the ranges needed, but this example shows the basic layout.

View 17 Replies View Related

Auto-populate Userform From Course List

Apr 10, 2007

Some Background info:
This is my first post! I am a total novice when it comes to VBA in Excel (but I'm a fast learner!), so please bear with me if my question is either obvious or easy (or if this is a question that's been asked 14980213 times already). I have some programming experience, but all in C or C++, not in VBA--this makes the project challenging. I also have to hand this off in a few months and trust that it'll never break, ever--more challenging.

I'm trying to make a Participant Tracking System for some workshops we offer. The intent is to make a userform so that an administrator can input all the information for the participant in question. One important question on the form is which course the participant intends to take; the snag is that the current list may change over time.

The Spreadsheet is set up with Course headings starting at R8C8, and continuing across for all 16 courses we currently offer. The data for the participant is entered into Rows 1-7, and the date they completed the course in the appropriate column for that course. There are some formulas in Rows 1-7, the important one here counts up the number of classes currently offered.

So, below is the code i'm trying to use, at least to start. I am making a combobox that has all of the available classes.

Private Sub UserForm_Initialize()

Dim ClassList() As String

'R5C6 contains a formula that calculates the number of classes offered. The value is currently 16
Redim ClassList(R5C6)

iCount = 0
y = 8

Do While iCount > R5C6
'R8C8 is where the class list begins.
'It continues horizontally along the rows for the 16 titles offered.
Range(R8Cy).Select
ClassList(iCount) = ActiveCell
y = y + 1
iCount = iCount + 1
Loop

With ClassListBox
.Clear
.List = ClassList
.ListIndex = -1
End With

End Sub

I used to have a line that replaced the following chunk.

Range(R8Cy).Select
ClassList(iCount) = ActiveCell

It read

ClassList(iCount) = R8Cy

and did nothing for me at all.

When I initialize the userform, I get a blank combobox. It doesn't do what I want it to do, but that's probably because I'm telling it to do something weird.

View 9 Replies View Related

Auto Populate Cells From Dropdown List

Dec 16, 2013

I am looking to auto generate a roster/schedule. I have a spreadsheet with shifts for each weekday that I need to populate every week with names to pick from a drop down list. the drop down list is made using data validation and looks at a dynamic named range to allow me to select only the people who are available to work a certain shift.

Is there a way to do this using vba?

View 3 Replies View Related

Auto-Populate Data Depending On Selection From Dropdown List

Jan 21, 2013

How to populate MTD data linked to the other spreadsheet file (monthly data) to the table on the left, depending on the month selected month from the dropdown list.

Attached files for reference :
Dropdown Month.xlsx‎
MONTHLY DATA.xlsx

View 3 Replies View Related

Create Dropdown List Using Data From Different Worksheet And Auto-populate Remaining?

Sep 11, 2013

I have two worksheet. One worksheet is a form where in the Field Door No. should be a dropdown list. The list will be retrieve from worksheet Source. Now upon selecting a specific Door No. it should also autopopulate the respective Type, Brand, Serial No., and Type.

First thing i need to know is what should i do on Cell B1 worksheet Form for it to be a drop down list retrieving the Column A on worksheet Source.

Second, upon selecting for example ATC0009 on the drop-down, it should auto-populate like these

Door No. ATC0009
Type AT Crane
Brand A
Serial 43679
Type Crane

View 14 Replies View Related

Auto-Populate Column Data From Source Sheet After Selecting From Dropdown List

Jan 11, 2013

I'm trying to make a spreadsheet that can be used to easily build a collective list of steps, for a user to read and follow line-by-line.

I want a source sheet of "steps" that I can change over time, and the resulting tabs that reference the source sheet get updated/populated automatically.

I've pieced together some VBA code from other sources, which kind of does what I want it to:

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Target.Column = 1 Then
Dim SourceSheet As Worksheet
Set SourceSheet = ActiveWorkbook.Sheets("Steps")
Dim TargetSheet As Worksheet
Set TargetSheet = Target.Worksheet
Dim c As Integer
Dim Source As Range

[Code]...

But there are some problems:

1) The data auto-populates into a row. It would read better if each step was in the same column, meaning rows would need to be automatically added upon selecting something from the drop-down list. The number of rows that need to be added vary based on the number of steps in the source sheet, for the selection made from the drop-down list.

2) If you make a change to the source sheet, my goal is to have the other sheets referencing the source sheet's lists of steps update automatically, so you only need to update the steps in one spot and everything you've built from them gets updated instantly. Currently, you must select a different choice from the drop-down list, and then change it back, before it populates the "new" steps from the source sheet.

This is my first time using VBA.

What I have so far is attached: testAutoPopulate.xlsm

View 2 Replies View Related

Returning Distinct Values From A Table?

Jul 30, 2014

I have a set of cells and i want it to return all of the distinct or unique values within that matrix. for example, lets say this table charts how many sales an employee makes in a week.

sun mon tues weds thurs fri sat
john doe 1 0 3 2 3 0 0
jane doe 0 8 0 1 0 7 0
james doe 3 3 2 3 2 1 1

the desired output would be: 0,1,2,3,7,8

I don't want to be copying all of the cells or doing a lot of manipulation - i know i can copy, sort, and get unique values that way - i want a formula where as i plug in values, this field is updated to include all of the distinct or unique values.

View 2 Replies View Related

Distinct List Of ID Numbers From Several Sheets

Jul 22, 2014

I have several sheets in one workbook. For ease, the sheets are labeled Customer1, Customer2, Customer3 etc. Within these sheets are 5 columns of data, with unique headings that are consistent for all Sheets.

In Column A of each Customer sheet, is the Product ID number. I would like a distinct list of all Product ID numbers in the workbook, across all Sheets. (The amount of data in each sheet may increase or decrease)

View 7 Replies View Related

Auto Populate Field With Date When Other Field Is Selected From Validation List

Jul 4, 2013

In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.

Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.

Currently Col K has a validation list which comprises dates from 1st of each month from July to June.

I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.

The information is currently part of a Table in Excel 2007 that has about 500 records.

IdeasList

*
J
K

2
Status
Month Completed

3
In Progress
*

4
Complete
Jul-13

5
*
*

6
*
*

Data Validation in Spreadsheet

Cell
Allow
Datas
Input 1
Input 2

J4
List
*
=ValidationList_Status
*

K4
List
*
=ValidationList_Months
*

View 2 Replies View Related

Create Distinct Unique List For 2 Columns

Aug 6, 2014

So far I created code that creates a Distinct Unique item's list for one column. It works as it should but I want to modify the code to create a Distinct Unique item's list for two columns that are related to another i.e. a person's name to their license #.

Here is the code below that works for one column only:

[Code] ......

View 9 Replies View Related

Create Distinct List And Sort Alphabetically?

Jul 16, 2013

I am trying to create an alphabetised unique list for a report from an Excel master file and have searched the internet for solutions which all only seem to half work for me. I've copied a couple of array formulae below to try what I'm after and what the results are. 'RefSource' is the name I've given to the column of data I'm trying to sort.

{=INDEX(RefSource, MATCH(0, COUNTIF($A$2:A2, RefSource),0))}

This one works when I copy the formula down and gives me the unique list but doesn't sort the it into alphabetical order.

{=INDEX(RefSource, MATCH(MIN(IF(COUNTIF($P$1:P1, List)=0, 1, MAX((COUNTIF(RefSource, "

View 1 Replies View Related

VB Code To List Distinct Values + Associates

Apr 7, 2008

I have a workbook with two worksheets:

Input
Entitlment

On workbook called "Input" range A2 downwards are a series of staff names, non unique

When I select the worksheet called "Entitlement" I would like on this sheet range A2 downwards to list staff names in alphabetical order but only unique occurances of a persons name.

eg if on sheet "Input" cell A2="darrenS", A3="darrenS", A4="EdwardG", A5="EdwardH"

upon selecting "Entitlement" sheet
A2 would show "darrenS"
A3 would show "EdwardG"
A4 would show "EdwardH"

I user will then have to enter a number relating to number of accounts in ColumnB

should a name already have an existing number next to it then when the worksheet "Entitlment" is selected again the number will reamin associated to the name. When sorting occurs !

View 9 Replies View Related

Get (tabs) Of Action Log To Auto-populate And Auto-delete In Master Log

Jul 29, 2014

I need the tabs of a project action log to auto-populate and auto-delete in a master log. (So when something is added or deleted in a tab it is added or deleted on the master) I use excel a little bit for work and personal finance purposes but I have zero experience with macros or VBA.

View 1 Replies View Related

Extract Unique Distinct List From Two Columns With Array Formula And Sort From A To Z

May 30, 2014

I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:

List1
List2

Final List

Audi
Ford

[Code] ....

So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.

{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}

View 2 Replies View Related

Auto Populate Calendar Days Based On Month / Year And Auto Insert Work Based On Dates / Name

Jul 31, 2013

I am trying to auto generate a calendar based on two drop down menus - Month and Year.

Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.

August PM Schedule Demo.xlsx

View 2 Replies View Related

Auto Populate Tab

Jul 9, 2009

Is it possible to create a macro which adds a new sheet and names the tab from a cell value somewhere?

View 5 Replies View Related

Populate Cell Using Two Results From Same Table And Referring To Another Table?

May 23, 2013

I have a workbook with two worksheets,

sheet 1 contains student data, name dob, actual age, raw score and an empty column standardized score

sheet 2 contains a conversion table, using the actual age from sheet one you locate the age in the top row of table in sheet 2, and using the raw score from sheet 1 you locate the matching raw score from the first column of the table in sheet 2, going down and acrosss until you meet this gives you a standardised score

i want the standardized column in sheet one to fill by using a formula which looks at the table in sheet two locates the two values and returns the result.

View 5 Replies View Related

How To Auto Populate Data

Jun 26, 2013

I have 5 columns: A, B, C, D & E of which I have drop downs for: Yes, No, Maybe, Not Sure for A, B, C, & D. I need column E to offer a hyperlinked mailto:ask@bob.com when A, B, C have Yes, Maybe or Not Sure selected. If Yes, Maybe or Not Sure is selected for D, I need to have a hyperlink mailto:ask2@bob2.com. When no is selected for any of them, I need to have it say None.

View 9 Replies View Related

Sumproduct: Auto Populate

Oct 29, 2009

The first tab I have a summary sheet which I want to auto populate with a sumproduct function, the second tab is where the dates and values are I wish to sum the value if its returned in January. I had a go to try sum the entire range see below but its not working

=SUMPRODUCT(--('Alex Guest - Scotland'!$G$19:$G$1048576>=1/1/2009),--('Alex Guest - Scotland'!$G$19:$G$1048576<=3/1/2009),--('Alex Guest - Scotland'!$H$19:$H$1048576))

The ultimate goal I am trying to achieve is if there is a date in January in the date returned column and the php column does not contain a value I want it to sum in the summary sheet in January. Hopefully if anyone can advise that would be great then I can look at the formula and try workout to sum only if there is a value in the php column in January

View 3 Replies View Related

Auto Populate Address

Dec 20, 2009

on Sheet 2, I have a very long list of names and addresses. What I am trying to do, is to have the correct address auto populate in Cell B253, depending on which name I type into Cell B252.

The formula has to be able to determine which name is typed in cell B252, i.e., John A Doe, and then auto populate his address in cell B253.

View 9 Replies View Related

Auto Populate Cells

Jan 2, 2010

I have a workbook with three sheets:

'Activity' 'Expenses' and 'Income'

On the 'Activity' sheet I:
select the date,
select the description, and
select the category (dependent on the description using indirect)
and enter a value. ($ dollar amount)

How do I make the entered values appear automatically :

- on the correct Sheet for the Description - 'EXPENSES' or 'INCOME'

- in the correct columns (matching) the category

- and in the correct row (matching) the date,

- "add to" - if a value already exists in the cell (more than one transaction on a date)

after they have been hand entered on the 'Activity' sheet?

View 7 Replies View Related

How To Auto Populate A Formula

Aug 3, 2007

My macro runs, and it inserts a cell, but in order to record data on a summary page, I need a formula to auto populate, and I've tried having the macro copy/paste and I've tried ActiveCell.Formula...and nothing seems to be working. The formula needs to go into Cell B3.

View 9 Replies View Related

Auto-populate Corresponding Cells With Each

Dec 10, 2009

I have a database in the form of a spreadsheet with the following column headings:
A=DATE (3 letter abbreviation for the month...jan, feb, mar, etc)
B=Client
C=City
D=State
E=Department
F=Contract number

Is there a formula that I can enter in another worksheet (which is being used as a report), that will "grab" the monthly data for all entries for a given month (i.e. "mar"), and auto-populate corresponding cells with each?

View 9 Replies View Related

VBA Auto Populate Cell Through Lookup

Mar 25, 2014

I would like to enter a name in E11, and then have G11 populate the name of the company that person belongs to, from a different sheet.

If the person is new, the company name entered into G11 should create a new column on the Companies sheet.

I've attached a dummy sheet which should make it more clear.

DummyCompanyPopulate.xlsx

View 9 Replies View Related

Auto Populate The Number Of Units

Apr 3, 2009

i have been trying to write an Excel sheet in 2007
that can be given a random amount of (money) and show
me the best way to spend it.

Weapons:
Name - Power - Cost
1 - 10 - 20
2 - 20 - 40
3 - 40 - 80
4 - 80 - 160
5 - 160 - 320
6 - 320 - 640

So i have 40K to spend, i want to eneter 40000 into a box and it
will auto populate the number of units i can buy, always setting the weapon
#6 as the priority.

View 13 Replies View Related

VBA Code To Auto Populate Different Cells

Apr 30, 2009

I am trying to add some code that will auto populate cells based on entries and list selections. In the attached spreadsheet, when information is added to cells in column 'A', the date should auto populate the cells in column 'C'. When a status of "Complete" is selected from the dropdown box in cell 'B', the date should auto populate in the cell in column "D".

The code partially works in that it will auto populate columns "C" and "D", but the code errors out every time and I have to cancel the error to continue to the next entry.

View 2 Replies View Related

Should Change Event Be Used To Auto Populate?

May 22, 2012

I am populating column D with

=IF($C2="","",VLOOKUP($C2,Name_ID,2,FALSE))

Based on selection in column C populated using a drop down list from a range titled PracticeID. Other named ranges, all on sheet titled Validation, are PracticeName and Name_ID (a 2 column range made up of PracticeName and PracticeID). This works great the first time a selection is made in column C. But, of course, if the user needs to change the selection in column C, the value in column D does not change. Would the cell change event be the appropriate place to place this action?

View 1 Replies View Related







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