# Allocate Values From A List Based On Total

Feb 5, 2014

I am trying to create a Excel file for my job. I work in a warehouse where I have many different orders all of which are made up of various different numbers of items and priorities. For example:

Order 1 = 45 items, Priority 1
Order 2 = 78 items, Priority 1
Order 3 = 48 items, Priority 2
Order 4 = 34 items, Priority 3

The total number of items is divided by the number of staff to give each staff member a total for the evening.

I plan to have a list of these orders in the excel file.

What I would then like to do is to have each staff member allocated orders so that the their total is reached. The sheet would automatically allocate orders to each person from the list of orders so that based on priority first then by the largest number of items to the smallest.

I think this should be a fairly simple sheet to create and I have a view of creating an application for it if I can get it working.

## Macro To Allocate Quantities Based On Sales

Jun 26, 2013

I want to allocate the Quantities which is available in DC based on Sales, Store OH and In Multiples of Pallet Rounding (PR).

For Eg. If I have 200 Qtys in DC, Excel should allocate these 200 Qty's in multiples of PR to highest selling stores and also consider the store On Hand.

Below is the Format

Article #
Store #
Region
PR
Model

[Code]...

## List Unique Entries And Total Cells Based On The Results

Jun 10, 2008

Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.

What I want to do is look down Sheet OEE V20:V500 and get two lots of information -

The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.

I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.

I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.

Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.

## Calculating A Total, Based On Values In Other Cells

May 6, 2009

Using Excel 2002. Here's my problem.

Column A contains the month (as text)

Column C contains an employee name.

Column O contains a reason for absence.

Column K is the number of hours of absence.

The employee's name may appear several times in the worksheet. What I want to do is count the number of hours per type of absence.

E.g. If A=MAY and C=BOB and O=SICK then total hours from all instance of K = X.

This will be used on a seperate worksheet where the name C will be referenced from a validation list.

## Formula For Total Cost Based On List Of Hours For Each Employee Times A Table Of Rates

Jun 10, 2014

Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.

Please see attached file : Formula.xlsxâ€Ž

## SumIf: Check The Total Budgeted Amount Based On The Values I Enter On The Categories Sheet

Dec 18, 2009

I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.

## Obtaining List Of Unique Values From List Based On Some Criteria?

May 28, 2014

The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?

Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String

[Code]....

## Producing Table Of Monthly Values Based On Monthly Growth Rate And Yearly Total

Mar 6, 2013

I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.

Link for excel file is here: [URL] ...........

## Drop Down List Which Returns Values Based On What Has Been Selected In The Previous Drop Down List In The Adjacent Cell

Mar 19, 2009

I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.

## Count Values In One List And Then Narrow / Combine Values Based On Criteria And Count Those

Jan 20, 2014

I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.

Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.

Book1.xlsx

## How To Return Corresponding Values Based On Drop-down List

Jun 3, 2014

I have created a drop down list of various material sizes what I am needing to do is to select a material size from the drop down list and a corresponding value is input into the cell. So on say sheet two (Data Sheet) I have Cell A1 25x25 with Cell B1 100, Cell A2 30x30 with Cell B2 120 and so on. On sheet 1 Cell A1 has my drop down list being the material size ie 25x25, so what I need to happen is if I select 25x25 then Cell B1 is 100 or if I select 30x30 then cell B1 is 120.

## How To Look Up Based On Two Values Generated From Drop Down List

May 10, 2009

Ok what I am trying to do is lookup a value in a table (kinda like one below but alot bigger). What I plan on doing is creating a drop down list for the rows that show "A, B, C etc) and then another drop down for (AA, BB, CC etc).

So lets say the 2 drop-down list are set to C and BB
I want to be able to fill another cell with the value -134

I would use If statements but like I said the table would be alot bigger than example. If possible I would rather not use VBA due to work security settings.

a b c
AA-110-110-129
BB-115-115-134
CC-117-133-136

## Call List Of Values Based On Selection

Oct 23, 2007

I have 2 Fields (Columns) and I have implemented Validation Lists in both these 2 columns.

I have around 10 values in Column A which are available in the List and have around 50 values in Column B in the List.

In other words, one of the five values or items can be selected for a single and unique value in Column A.

is it possible to call one of these 5 values in Column B depending upon the selection of the value in Column A ? Both the columns have validation lists.

## Dropdown List Range Based On Two Different Cell Values

Aug 2, 2012

I'm new to excel and was trying to make a workbook that will have a drop down list populate based on a user input in two separate cells. Basically, i have columns: name, pages, and books. I would like to have the drop down list match data by first finding the amount of books read, then finding the amount of pages read. Then any name that meets the criteria will be available in the drop down list. How can I do this?

## Filter Values Based On Master And Input List

Apr 11, 2013

I am trying to copy a spreadsheet i made in google docs in excel keeping the same functionality. Since the formulas are a bit different between the two, i can not simply copy the codes so i must start from scratch. The basics of my spreadsheet is that i have (3) columns: A master list, A filtered list, and an input list. I want the filtered list to change based on the values in the inputted list.

ColA = Input list
ColB = Filtered list
ColC = Master list

Items:
Orange
Red
Blue
Yellow
Green

ColA contains the colors i manually enter
ColB contains all the items in ColC that are NOT already listed in ColA (except for Red, i dont what that showing up)
ColC contains ALL the items above.

Two Examples of a functional worksheet:

ColA............ColB
Orange........Blue
Green..........Yellow
ColA........ColB
Orange....Blue
.............Yellow
.............Green

ColB is where the magic happens, it grows and shrinks based on the values in both the master list ColC and the input list ColA

## List Box That Separates Values Based On Simple Criteria

Mar 19, 2012

I have a list box that separates values based on simple criteria. I have these sections separated by

Code:

' Cat 1 items here

' Cat 2 items here

'etc

My question is can you add the "----Cat 1----" into the listbox without being able to select it? Or is there another way to separate listbox entries?

## Check Errors In Set Of Values Based On List Of Data?

Aug 23, 2013

Here below the case:

List1 in Sheet1:

TEST1
TEST2
TEST1
TEST3
TEST8

List2 in Sheet2:

TEST1
TEST2
TEST3
TEST4
TEST5

I would like to know if it is possible to have in a single cell the information about the presence of an error in the List1, because a cell of List1 got a value that is not in List2.

I mean all cells in List1 have to contains the same values of List2. It is like a data validation. But it would be nice to have a single cell with "There is an error in your range".

## How To Count The Number Of Unique Values In A List, Based On Another Value

Jul 27, 2009

I need to count the unique values in a list based on another value. i.e.

AB ___ Jones
AB ___ Smith
BC ___ Henry
AB ___ Jones
BC ___ Brown
BC ___ Henry
AB ___ Tomkins

For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:

=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))

but haven't been able to get it to work.

## Limit Data Entry Based On List Values

Dec 17, 2007

I need to create a Drop Down Menu that gets it's data from another group of cells. I have done this before but it has been sometime ago and now I can't remember.

## Create Dynamic Top-x List Based On Cumulative Values

Jan 18, 2008

My objective is to automate the creation of a dynamic top-5 list by summing a number related to multiple entries of an item (text string). Data are added monthly and while the textual strings (imported) are constant (text1 always reads as text1) the text strings themselves may vary from "text1" to "text2", etc. What I'm trying to achieve is finding the top-5 five items and copy/paste them to a range on the same worksheet. The range into which they are pasted are source cells for DSUM criteria. The frequency with which a text string appears is not critical, but the associated count is.

## Component From List A Is Added To One From List B And A Total In £'s

Dec 21, 2009

I have two lists of components, a component from List A is added to one from List B and a total in £'s needs to be shown, simple enough I hear you say HOWEVER the LENGTH of component B is variable.

For example

Input part # ABC in A1 and £50 is shown as a total in D1, then input part # 123 in B1 and the length of 100 in C1 and the total changes to £100. Then if you change the figure to 200 the total changes to £150

## Gathering Values Based Off Criteria And In Turn Forming List

May 5, 2014

ok lets say you have thousands of rows of raw data and you are running vlookups to pull specific data from the raw data to update your spreadsheet. Your raw data consist of some new ideas so when you vlook up, you get an error. In my case i get about 50 errors all spaced out amoung the thousands of rows or raw data. I would like to create a formula that would go and find the error and output a code/value that already exist in the raw data and output that in Cell D1 for example. The it will go and find the second error and output that into cell D2 and then the third error and output that into D3 and so on.....

The idea is that when its done a list of all the errors will have been created so then i can use other formulas to simple add the new info to the workbook. Instead of search through thousands of rows for the errow.

## Compare The Pts Values And Then List The Team Standings Based On Those Points

Sep 17, 2009

I have a list of nine teams from B13:B21 with Wins in column C, Losses in Column D, Ties in Column E. Pts are in Column F (all of these run row 13 down to 21). The team listed in B13 has a cell name of One in the Name Box, team in B14 has a cell name of Two, etc up to Nine.

The pts total for each team is also named, so F13 is called ptsone and has a current value of 8. F14 is called ptstwo and has a value of 10, etc.

I'm trying to do is in a different area of the spreadsheet put a formula that will compare the pts values and then list the team standings based on those points.

## Macro Exporting PDFs Based On Values In Validation List

Jan 31, 2014

I have the following macro that exports pdf's for every value in a validation list. Currently it's setup where I have included each value in the macro. This is fine with just a few values but I am about to add 900.

My question is, is there a way to write the macro so it exports a pdf for every value within a dropdown list until it hits a null value instead of what I have below so i don't have to repeat everything for every value in the list?

Here is what i currently have.

Sub Export_MarketSpecific()
'
' Export_MarketSpecific Macro
'
'
Sheets("MOA-Page 1").Visible = True
Sheets("MOA-Page 1").Select
Sheets("MOA-Page 2").Visible = True
Sheets("MOA-Page 1").Select
Range("D2").Value = Range(Range("D2").Validation.Formula1)(2).Value

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

## Allocate Look Up Value To Many Occurrences

Dec 28, 2007

My Vlookup result may relate to a number of occurances of the same source product code. I want to allocate the result so that the last occurance gets the balance. See attached file.

## Dropdown List Always Shows Unique Values From Column Based On Lookup Value

Jul 22, 2014

I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.

## Autofilter Based On List Of Values In Range (Include Not Exact Match)

Feb 13, 2014

I need an autofilter which filters rows based on a list of values (+50).

The problem is that I am looking for rows INCLUDING values from the list, not for exact match.

I am not able to make excel filter values including the values from the list... I am able just to filter values matching exactly values from the list.

[Code] .....

## How To Allocate Subroutine To New Button

Feb 26, 2014

I need to allocate an existing subroutine (its code below) to a new button I'd like to add to my sheet. Two questions arise: to use "form control" or "activeX"?, also should I redfine the first line of the code?

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

## Allocate A Formula Into A Variable

Jul 25, 2007

I allocate result of formula FORMULA R1C1 into the active cells,,,AND NOW I wan to allocate it into a variable x or y (I dont want that the result appears in the spreadsheet, just into a variable to work the code!)

## Allocate Rebates Formula

Mar 11, 2009

The number of transactions is scaled down to provide this example.

Rebate scheme (Cum Sales = Cumulative sales) based on the total turnover of a group of customers.

Cum Sales....Rate....Sales Band...Rebate
20,000........1.0%.......20,000........200
35,000........2.0%.......15,000........300
45,000........2.5%.........3,400.........85
...................Total......38,400.......585 average rebate 1.5%

I would like to correctly allocate the £585 rebate across the individual customers who have generated the turnover i.e. NOT by using the simple 1.5%.

Clearly the large sales value customers will have earned a higher % rebate than the lower sales value customers - but how to calculate?

Customer....Sales value...Rebate
A...................15,000
B.......................900
C....................7,000
D....................3,000
E.......................500
F.....................9,000
G.....................2,000
H.....................1,000
Total...............38,400.........585