Auto-Populating Multiple Values Based On Two Criteria Based In Comboboxes

Aug 7, 2013

I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.

View 2 Replies


Auto Populating Columns In One Sheet From Another Based On Criteria

Jul 28, 2009

I've been trying for some time now to figure out how to have one sheet automatically fill in another based on a larger data sheet. I'm trying to find a way to take a monthly calendar which contains a row for each employee and column for each day and use that to create smaller lists on another sheet. Basically if someone puts in for a day off, or has a medical appointment etc, that person has a coded single letter for that day for tracking and planning purposes. I want to have a second sheet that references those codes and makes individual lists (the following people are on vacation, these people have a medical appointment etc.)

View 3 Replies View Related

Auto-populating A Table Based On Ranked Values?

Jan 21, 2014

I am looking to create a table with ranked projects from a different tab that is able to pull data from the same tab and change based on value updates. It is difficult to explain, so I have attached the file below.

What I am looking to do is pull the ranked projects from tab #7 - Scoring to tab #5 - Final list. Each project has a unique ID#, but a lot of the columns I need populated are not unique. I tried using a combination of arrays, but it always returned a #value into the cells.

P.s: In tab #5 - Data sheet, the formulas in I5 and P5 work, but are pretty ridiculous, and I'm pretty sure there must be an easier way. I was looking up values from the 8-budget tab to try and determine which group column H values were from. The only complication I was having was that a project can be allocated to either a functional unit, or group so I need to have the "group" total as independent, not just as a sum of the functional unit totals. Like I said, these formulas work,

View 4 Replies View Related

Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?

Feb 24, 2014

Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.

In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.



The attached example file may be a better alternative to view this problem.

View 2 Replies View Related

Auto-Populating Based On Dropdown Selection

Dec 4, 2013

I am trying to create a sheet for identifying work hazards based on the job assigned. So what I have set up is a drop down list to select the job type. Once this job type is selected I want the pre written hazards to autopopulate in the hazard box. I would also like the required PPE to appear in the PPE box for the job selected.

I have 6 different choices in the drop down list. For each choice I have the hazards and PPE in a separate sheets in the worksheet.

Is there a way to get this information to autopopulate?

View 1 Replies View Related

Auto Populating Based On Specific Data

Jan 7, 2014

I have a unique task i am trying to achieve (when i say unique i mean i am out of my depth). I am trying to take specific data from a couple of different sheets to populate other sheets whether it be copying the text of fill cell (color)

Excel Automation Test.pdf

I have Attached a PDF detailing.

View 2 Replies View Related

Add Multiple Values Based On Criteria?

Jul 16, 2014

Add Multiple Values Based on Criteria

Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.

Here is the sample worksheet (Please click).

View 7 Replies View Related

Excel 2007 :: Populating Data Based On Two Criteria?

Mar 28, 2014

I'm trying to populate a table based on two different criteria in a another table. On is based on a date and the other the number of a unit. I'm trying to get the data in sheets Week 1 and Week 2 into the format in sheet Example. Is there a way to create a series of functions, filters, etc that I could use to create a macro to do multiple sheets or at least a whole sheet at once? I've been trying to think through it with my limited knowledge of filters and functions

Here is the file with an example and I'm using Excel 2007: Example.xls

View 2 Replies View Related

Excel 2010 :: Auto-populating Calendar Based On Conditions From Data Table On Another Sheet?

Apr 26, 2014

I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.

On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.

On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".

View 2 Replies View Related

Multiple Options Cell Values Based On Multiple Criteria Variable Inputs?

Mar 18, 2013

I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.

I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).

The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.

As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.

The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R

You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.

This is the area where my skill at using scripting dictionaries fails.

The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words

if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")

So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)

Here is my code so far.

Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")


View 1 Replies View Related

Searching For Values Based On Multiple Criteria

Jul 20, 2009

I have a problem with excel that I have been trying to solve for the past 5 days! I have 2 tables (one with 500 rows, the other with 15000) that contain information about pipes.

The smaller table, Drift List, is to be used as a reference to fill up the bigger table.
Here is how Drift List looks like:

Size NominalWeight WallThickness APIDriftDiameter AlternateDriftDiam.
5.5 14.00 0.244 4.877 7.625
5.8 14.88 0.253 4.915 ----

The other table, Pipe Database, Looks like:

Size NominalWeight WallThickness DriftSize DriftType
5.5 14.00 0.244

I have to fill up Drift Size using APIDriftDiameter from the first table, BUT if AlternateDriftDiameter is available, I have to use that instead.

DRIFT TYPE column has to indicate either API or Alternate (Depending on which one I have used).

Here are the two files:

View 9 Replies View Related

Values In Column Based On Multiple Criteria

Dec 2, 2006

Option Explicit
Dim lastrow As Long, t As Long
Sub Method()
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
If Cells(t, 8).Value <> "" Then
If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _
6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20
End If
Next t
End Sub

Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.

View 5 Replies View Related

Combining Some Of Rows Values Based On Multiple Criteria (2)

Apr 18, 2014

I have a table based on transport numbers who sometimes have doubles. Example:

Number Postcode Value1 Value 2...
106200 8500 10
106200 8500 5
106200 8600 6
106201 5500 4
106202 4000 1
106202 4000 1

So it works as following:

A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine

So shortly:
Transportnumber double/triple...+ Postcode double/triple...= combine these rows.

if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.

So example of end result for 106200 would be
106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to)
106200 8600 value ( this was unique so it stays unique)

View 4 Replies View Related

Counting Unique / Different Values Based On Multiple Criteria?

Jan 21, 2014

I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).

Clinic Title
Start Date


formula that will automatically give me that answer?

View 2 Replies View Related

Formula To Sum Values From Column Based On Multiple Criteria

Nov 29, 2013

I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.

What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.

Sample Workbook.xlsx‎

View 12 Replies View Related

Finding Top 2 Values Per Group Based On Multiple Criteria

May 21, 2007

I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.

As an Example, here's what I'm trying to do:

Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33

View 9 Replies View Related

Populating One Sheet Based On Values Of Another

Jun 22, 2012

I have a workbook with two sheets. Sheet 1 has raw data that contains contacts with multiple statuses. I need to pull out only the contacts that have a positive status and put them into Sheet 2.

I would like there to be no spaces between the contacts in Sheet 2. I'd also need all of the data pertaining to each positive contact (so not just their name, but all of the data in the row that relates to the contact).

View 3 Replies View Related

Array Formula To Return Multiple Values Based On Various Criteria?

Dec 19, 2013

I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.

View 4 Replies View Related

Return Multiple Values Across Columns And Rows Based On Criteria

Jun 20, 2008

I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.


Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50

View 9 Replies View Related

Formula/Function To Return Multiple Values Based On Criteria

Aug 25, 2006

I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?

View 2 Replies View Related

Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432

View 3 Replies View Related

Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

View 1 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-enter Data Based On Criteria

Jul 24, 2008

Here's a sample of a completed table I would like to have.

Sheet2 *ABCDEFGHIJKL68*# ofSTART*wk #wk #wk #wk #wk #wk #wk #wk #69ACCTWEEKSWKRATE1234567870110181 $ * * * *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 71110242 $ * * * *10.00 * $ *10.00 $ *10.00 $ *10.00 $ *10.00 ***72120143 $ * * * *10.00 ** $ *10.00 $ *10.00 $ *10.00 $ *10.00 **73120272 $ * * * *10.00 * $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 74120381 $ * * * *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 75130151 $ * * * *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 *** Excel tables to the web >> Excel Jeanie HTML 4

If given columns B and C, is there a VBA code that will look to those columns and auto-enter the payments across the week matrix based on that criteria?

View 19 Replies View Related

Populating One Worksheet With Data From Other Worksheet Based On Criteria?

May 22, 2013

I have two sheets in one workbook; one has a list of names in column A (136 of them) and another is a master list with all the names of people who work for the company (over 2000). Sheet 1 and Sheet 2 respectively. I need to populate columns B-E in Sheet 1 with the data in columns B-E in Sheet 2, but only for the names that match in column A. I've tried applying filters but can't get to the data I need for some reason. The names are formatted the same way in both sheets (Last, First). In Sheet 2 the names are links but in Sheet 1 they aren't; not sure if that's important.

View 7 Replies View Related

VBA Code To Auto Populate Fax Template Based Criteria

Aug 14, 2014

I am after a VBA code that I can use to populate a fax template based on criteria's

Basically I have records which are being logged throughout the day via a User form and sometimes there may/maynot be a charge. the user decides at the time. The column is named "to be charged" and is filled with either Yes/ No.

I need to be able to select the date to print or Just the same day date and print all faxes' that have "Yes" for charge This way all the information for each fax is populated and the user can just print.

and if possible mark a Colum non the master sheet as printed with a X or something

View 14 Replies View Related

VBA Auto Filter To Select Field Based On Certain Criteria?

Feb 5, 2014

I'm currently using the following:

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

However, i'd like it select the field ie 2-17 based on a year in a cell on another sheet "x" in cell E3.

At the moment i have a row of numbers 1-17 above the corresponding field cell so for example the number 15 is attached to the year 2013 (although this is dynamic).

I just cant work out how to get the autofilter to select the number that matches the cell in "x" E3 with the same value in T2:AJ2 on filter sheet and then filter the column that matches.

View 3 Replies View Related

Filling Comboboxes Based On Value Of One Combobox?

Jun 30, 2014

I have 2 combo boxes dependent on one combo box. When I select an item from the dropdown list in ComboBox1, I want the corresponding data attached to ComboBox1 to be available in ComboBox 2 and ComboBox3.

ComboBox1 ComboBox2 ComboBox3
John Male English

Data for ComboBox1 are in column A2 (Names), data for ComboBox2 are in column B2 (Gender) and data for ComboBox3 are in column C2 (Language spoken).

I chanced upon a code written by royUK from this Excel Forum, which works well for 2 ComboBoxes. This is the code:

Option Explicit
Dim rSource As Range
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


View 2 Replies View Related

Fill ComboBoxes Based On Part Of Their Name

Mar 12, 2008

Load Comboboxes With Variant Range using ListFillRange

I'm trying to implement this, but am getting a 400 error when this code runs....

View 7 Replies View Related

VBA Countif To Fill Textbox Based On Two Comboboxes

Jan 30, 2014

I need to get TextBox1 to fill with a number.

Basically, I need the textbox to look at spreadsheet 1 and count the number of times a name occurs in a column given a certain week.

ComboBox3 has the name in it,ComboBox4 has week number

So when I press CommandButton1, the script will look at the week, find all instances in that week, rows, look at the name, then count the number of times the name comes up that week. and return that number to TextBox1.

View 1 Replies View Related

Copyrights 2005-15, All rights reserved