Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Distribute Values To Cells Depending On The Input Value

I would like to distribute values in cells depending on the input value (excel file attached). This file is a test and basically i've been entering the data manualy. The format is flexible, so it can be reorganisaed.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Automatically Copying Rows From One Sheet To Another Depending On A Cells Input
i have a certain column that tells me if a client has withdrawn from the company, and the column just says "y" or "n" i want excel to automatically copy the whole row to another sheet if that cell is a "y" for yes the client withdrew...

View Replies!   View Related
Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View Replies!   View Related
Inserting A Numerical 1 Or 0 Into A Cell Depending On The Values Of Two Other Cells
I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells.

By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank.

I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0
If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0
If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0

View Replies!   View Related
Count Cells Depending Upon Multiple Cell Values
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83,

Order No OP Posted Set Allowed Set
30761157 00100.731.500

View Replies!   View Related
Cross Referencing Cells To Input Values
I am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.

View Replies!   View Related
Moving Data Depending On Input
I new for a forum I have a small problem I just stock and canīt see what to do. So i need to move data from 4 columns B,C,D,E to F,G,H,I depending on input in column J, like if in J2 i enter letted M (or any from column A) i need to move data from B12,C13,D13,E13 to F2,G2,H2,I2 and so on. I dont have experiens in programming in exel or using formulas.

View Replies!   View Related
Conditional Input In A Cell Based On Data In Two Different Cells And Other Values
I have lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming.

NameComment DOB
David 25 August 1988
David 13 September 1982
David 24 May 1990
William 24 March 1980
William 25 July 1987
William 13 August 1989
Rita 17 July 1990
Rita 24 April 1989
Rita 13 June 1988
The example file is attached

View Replies!   View Related
Userform's Listbox Values Depending On Values On Certain Matrix
I have problems with my userform's listboxes. I have two listboxes, and I want second listbox's values to be dependent on first listbox's values.

And even more complicated, I need second listbox's values to be dependent on values on certain matrix.

In that matrix, row headings are listbox1's values and column headings are listbox2's values. How ever there are blanc cells on that matrix aswell. So if there is a blanc cell(s) on a row which (heading) is selected at listbox1, then I don't want that column (heading) which intersects with the blanc cell to be included to my listbox2 values.

Finally I want to insert the selected values from listboxes and the value from the intersection of those listbox values (headings) on that matrix to worksheet.

I included an attachment, where you can see my point better. However, as you can see, now the listbox values are not dependent on that matrix. Otherwise it is working like I want it to work.

View Replies!   View Related
% Calculation With Values Depending On Size..
There is a data range in the E + G columns. The content of the E column can be 5 different items with variable GROSS weights in the G column:

2.....200 g....138......56.52...20........45 g
3.....100 g.....84....................35......100 g
4......45 g......52....................60......200 g
5.....500 g....253...................124.....500 g
7.....100 g.....90
8.....500 g....280
9......45 g......62

View Replies!   View Related
Sum Of Values Depending On Color
How can i do sum of the values which are in red color


View Replies!   View Related
Drop Down Lists - Depending On Other Cell Values
I have a workbook with several drop down boxes and formulas already set up and working. I want to improve it though. My question is.... is there a formula that will make the drop down list change based on a cell value....

For example: If B6 equals vegetables then C6 equals list (potato, carrot, pea, etc.) If B6 equals fruit then C6 equals list (apple, banana, grape)

View Replies!   View Related
Send Email Depending On Cell Values
Does anyone know the code to send an email to a set list of six recipients when cells in a certain column in a worksheet reach a certain value greater than another. Let me explain a little - The action to be performed is date-based.

For Example:

When the date cell L2 is 30 days later than the date in K2, send an email with subject containing data from cells B2 and C3.

However, this has to work for columns K and L in their entirety, as both columns contain a list of dates...

View Replies!   View Related
Getting Duplicated Values Depending On Rank Order
So, I have some names and values. Rank function give me order for those values.
Small function gives me ascending order. I want to get first n (let say 5) values back next to each other but can't use VLOOKUP function because sometimes I get duplicates (red numbers).

If there is more same numbersthat small function returns... It need to give me all of them, no matter 5 is limit. how to get back values of rank function that are duplicated. Book1.xls

View Replies!   View Related
Lookup Results Depending Upon Cell Values
Depending upon a figure entered e.g. = $ 895.00 and also Depending upon a state being entered e.g. = NSW. Then Excel will lookup a series of cells to produce the correct figure depending upon the correct data ciritera being met. I have a data table sitting behind the spreadsheet with all relevant values.

View Replies!   View Related
Change Color Of Cell Depending On Values In Another Range
I want to change the colour of a cell depending on its value, when compared to another row of data identiified by a value in another cell. To try an clarify:

b3 = 1 b5=4 Because b3 = 1 then compare cell b5 with the row g1 as b3=g1 then depending on its postion set a colour

g1= 1 g2:g5 = 2,3,4,5,6
h2= 2 h2:h5 = 7,8,9,10

View Replies!   View Related
Copy Rows To Other Sheets Depending On Values In Column
Iíve done a search of the forum for a macro and found some post that seems to be about what I want to do, but unfortunately my minimal vb experience prevents me from adapting them to my requirements.

IĎve got a workbook with three sheets; say Sheet A, Sheet B and Sheet C. I want the info in Sheet C copied to either Sheet A or B depending on the info in cells in Column A of Sheet C.

Sheet C contains customer info, there are about 9 column headings and up to 30 000 rows (Individual customers).

The cells in Column A will contain a number between 0 - 23. What I want the macro to do is, if a cell in Column A contains any of these numbers, 0, 1, 2, 6,7,8,9,10,17,19,20,21,22,23, I want that whole row copied to Sheet A and if it contains 3,4,5,11,12,13,14,15,16,18, I want it copied to Sheet B. The cells will only contain one number, never a combination.

View Replies!   View Related
Colouring Many Cells, Depending On The Value Of One
I'm trying to automatically shade a number of cells dependant on the value in a single cell. ie. If D2="quote" then I want A2:Q2 to be yellow. If D2="Design" then I want A2:Q2 to be green etc etc. I have about 6 options in all of what D2 could be.
I then want to repeat this for about 200 rows.

View Replies!   View Related
Seperate Cells Depending On Content
I have some workbooks that have some serials in A row and i need to seperate them depending on how many digits they have,

For example some have 13 digits,and some 8.I just want to seperate them into different rows.

Here is a sample

seperate sample.xls

View Replies!   View Related
Display R, A Or G Depending On Text In 2 Other Cells
I am trying to nest a formula with IF & AND to work out a RAG status. I have 2 questions per row (in Columns I:J) which will be answered either Yes or No. For each combination I need to give a RAG status in Column K Ė e.g. two Yes replies give Green, 2 No replies give no and one No out of 2 will give Amber

Below is hoped for response in Column K:

ColI---Col--- JCol K

View Replies!   View Related
To Change Color Of Cells Depending On Value
when I execute this code on the selected cells, it completely ignores my selection, and makes the entire worksheet black.

Sub ColorCells()

Dim cell As Range

For Each cell In Selection

If cell.Value = "U" Then
cells.Interior.Color = 3

View Replies!   View Related
Highlighting Cells Depending On A Value In Another Cell
Have a spreadsheet shows 4000 warehouse locations (location of products). Each cell is a location in the warehouse (ba050, ca809, etc, actual warehouse locations, not grid ref); some products have more than one location, the locations are always next to each other. I import a text file into the workbook which tells me which product has more than one location (2, 10, 50, etc), thatís all Iím interested in ( multiple locations).

What Iíve done so far is to use vlookup to look at locations in file and allocated the number (the number of locations that product has) next (next column) to the cell that correspond to that location. What I need is a macro or formula that will look at the number and than highlight that many cells up or down the column, according to the number in the cell to the right (5 in cell, highlight 5 cells, 50 highlights 50). The warehouse runs up one aisle and then down the next and so on. So I need to be able to highlight cells up one column and then down the next and then up, then down and so on.

Multiple locations change on a daily bases, so need a quick way of updating 4000 locations.

New to excel donít know if this is possible or not. Learning as I go.
Need some assistance.

Sample below.
Only interested if a number is returned, ď******Ē can be ignored as will hide all columns expect ones with locations. So:

BA020 returned value of 4, so would like to highlight (any colour) BA020, BA029, BA040, BA049.

BB780 returned value of 2, so would like to highlight BB780, BB789.


BA090 2 BA100 **** BB749 **** BB739 ****

BA079 **** BA089 **** BB760 **** BB750 ****

BA070 **** BA080 **** BB769 **** BB759 ****

BA059 **** BA069 **** BB780 2 BB770 ****

BA050 **** BA060 4 BB789 **** BB779 ****

BA039 **** BA049 **** BB800 **** BB790 ****

BA030 4 BA040 **** BB809 **** BB799 ****

BA019 **** BA029 **** BB820 **** BB810 ****

BA010 **** BA020 4 BB829 **** BB819 ****

View Replies!   View Related
Hyperlink To Other Sheets Depending On Cells
how I would do the following, as I have been having a play but not managed to get anything.

I have two cells, C10 and C11, they both contain numbers. I want to create a hyper link that goes to the sheet called: Semester C11 Year C10

e.g. if the cell values are 2 and 1 respectively, I want the link to go to the sheet called 'Semester 1 Year 2'

View Replies!   View Related
Count Cells Depending On Certain Dates
I'm trying to create a sickness record book for colleagues at work, so we can monitor their absense accoridingley. I am trying to create a spreadsheet that will do the following:

Look at the dates that are over 12 months and not count those in the total occurances countCheck the dates that someones works and compare this to the absense date and automatically calculate the total time off
Are these points possible or am I wishful thinking? Just I have 60 people in a team and its hard doing them all in one go, but would be good if there is a way of it automatically doing this.

View Replies!   View Related
How To Change A Cell Colour Depending An A Different Cells Value
How to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.

I have attached a spread sheet.

View Replies!   View Related
Listing Cells On Another Worksheet Depending On Cell Value
I have a list of names in column B and either a 1 or 0 in column A as below:

0 A B
1 1 Bob
2 0 Chan
3 0 Lucy
4 1 Billy

On another worksheet I want to be able to list only the names with a 1 in column A. This must be done in another worksheet so I have something like below.

0 A
1 Bob
2 Billy

View Replies!   View Related
Locking Cells Depending On What Is Entered In Another Cell
I'd like to lock some cells depending on what is put in a particular cell. As shown by the file attached, if the first column (Amendments) has a "no" in A4, I'd like to lock out cells B4:D4. (The sheet will be protected). If it's a "yes" then the cells are unlocked. I'd like to then apply this to all subsequent rows (i.e. lock B5:D5 if a "no" in A5 etc.)

View Replies!   View Related
Cut / Paste And Shift Cells Depending On Value In A:1
I am trying to make a macro to take a set of data and shift the data range down depending on the value in the A:1 cell. For example if the data in column A starts with 5, 6, 7, 8 and so on.... I would like to copy/ paste and shift the column down 5 cells so I can start A:1 at that the data can begin as 0,1,2,3,4,5.....etc.

View Replies!   View Related
Code To Move The Cells Right Depending On The Value On One Of The Cell
I have attached the sample file where I have noted what I want as a comment. I need to check for the value " Function Name: RF Pick" if this is there then the code should move 2 rows down check whether that row has the value " Each" if its there just move one row down and move the cells as I need in the output.

View Replies!   View Related
Distribute Macro To Others
I have created a Macro and would like to make it available to other members of my team. This Macro is to be used in various different workbooks - so it cannot be tied to a single specific workbook. I tried creating an Add-In, but I've learned that, even if I instruct my colleagues how to load the Add-In, they will not be able to see the Macro that is inside it. So what methods are recommended for distributing Macro's to other users?

View Replies!   View Related
Input/output Values Into/from An Array
I have a long list of values, a few thousand lines, that I need to input into an array so I can run a formula on the values in the array and then take those results and put them into another array which I can then call to output down a different column. Is there a faster way to accomplish this than the typical For Loop with the activecell.value and activecell.offset commands, that just takes forever.

View Replies!   View Related
Input Numeric Values Only On Textbox
who can restrict the input values to numeric values only? I have 2 textboxes where the user enters employee id and numeric choice value of 1,2 and 3...I need help in making the textboxes restricted to numeric values input only. How Preventing the users from entering alpha and symbols values.

View Replies!   View Related
Cells' Color Change Depending On Dates
I have column "C", starting from "C5" with dates till C200, but not all cells have dates some of them are also blank.

I want the cell to change color depending on the date.

If the written due date is in a month from today, I want it to turn yellow, and if the due date is in the past from today, I wanted to turn red. I want it to get updated every month.

View Replies!   View Related
VBA-change The Colour Of 4 Cells Depending On The One Cell
i need to change the colour of 4 cells depending on the one cell.

i have found some vba codes on the net but they are all based on numbers and i need it on text.

so if a1 ="alert" make A1 to A4 red

if a1="passed" make a1 to a4 green... and so on

i cant used conitional formatting as i need more than 3 colours

View Replies!   View Related
Auto Hide A Row Depending On The Data In Two Cells
is there a way to auto hide a row depending on the data in two cells. Basically if two specific cells are empty or return a zero or specific number i want the row to auto hide.

View Replies!   View Related
Cell Color Fill Depending On Condition Of 2 Other Cells
Afternoon everyone i am having abit of trouble working on an excel 2007 spreadsheet. In cell I1 i have a tab called Color. i want the cells below I1 to be filled with one of 3 colors green, yellow, or red depending on whats in cell F "Status" (closed or open - in progress) and cell G ECD for estimated completion date.

Green - i need it to fill green if status is closed. Yellow - need it to fill yellow if ECD is any date greater than today and if status is open. Red - need it to fill red if ECD is todays date or older and status is open.

View Replies!   View Related
Conditional Format (with Formulas) 10 Cells Depending On 1 Criteria
I want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?

View Replies!   View Related
Populate Cells/Table Depending On INDEX Result
I am trying to populate a table (a7:c27) in the attached sheet with data.

First, I must enter a number 1-16, which uses index function to return either "series" or "base"

If it is base, i want the number in the corresponding table filtered down - i.e. if 1 originally picked then h7 is (40) is entered into each month as base = constant.

If the result is series, then the data in i7:z7 is entered into the the 18 month table.

View Replies!   View Related
Input Box: Return 2 Values To 1 Cell
I am attempting to have a user enter a First Name into the first Input Box and a Last Name into the second Input Box.

Within the code, I would like both answers to be returned to cell A1.

For example: A1 would read as Doe, James or James Doe (any variation is fine, as long as its one value in A1).

Sub Name()
y = InputBox("Enter First Name", "Information")
If y = "" Then
MsgBox "You must enter", 16, "Message"
x = InputBox("Enter Last Name", "Information")
If x = "" Then
MsgBox "You must enter", 16, "Message"
Range("A1") = y & & x
End If
End If
End Sub

View Replies!   View Related
Input Boxes To Change Cell Values
I need some code to use text boxes to change the data in certain cells. Basically I want 3 text boxes to appear one after another. The first box should add text to cell T1, then 2nd to cell T2 and the 3rd will add text to cell T3. All these values should be added at numbers.

View Replies!   View Related
Changing Values In A Range Using Input Box
I have a spreadsheet that has multiples ranges. Some values are displayed as negative values. I would like to create a macro that allows me to first select the range/s, (maybe using a input box) I want to change and then display the negative values as positive values.

Would I need to create a separate macro to do the opposite (change the values that are positive to a negative value)?

The other macro Iím trying to create is a similar type. What I would like this macro to do is first select the range I want to change and then divide all the cells in the range by 100000

View Replies!   View Related
Macro To Lock Cells In Protected Sheet Depending On Criteria
I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.

What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?

Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.

What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.

What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.

Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?

PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.

View Replies!   View Related
Auto Fill Cells Depending On Drop Down List Selection
I am trying to make a calendar spreadsheet to enable me to track allowance payments i am due from my company.

This payment is worked out by three conditions - where i am working (7 regions), how many days of the week (4,5,6 or7) and how many hours (<7,8-9 or 10-12)

I have made drop down lists to select each of the values for the three conditions and want to show the total for them

i can total the entire spreadheet for the month up using fairly large "COUNTIF" statements, but i want to be able to show what the exact amount is in each cell not just a complete total.

i.e. i am away for 6 days i get £15 extra, if i am in Europe i get another £50 and if i work 12 hours i get £30.

I want to be able to calculate this total in each cell for that day.

I can send a copy of the spreadsheet i have got at the moment if that will make things clearer than what i have tried to explain.

View Replies!   View Related
SUM Spread / Distribute Formula?
I've enclosed a sample summary page to see how I'm going to use the formula.

View Replies!   View Related
Formula To Distribute A Value Along A Bell Curve.
I'm trying to find a formula that would spread out a value amongst multiple cells along a bell curve. Say I have $x,xxx. I want to spread that amount out in a certain proportion, regardless of how many cells I'm spreading it throughout.

Say I have a $10,000 project. I want to be able to spread it out like a bell curve in the cells I choose. For instance, if it was a five month project it would be distributed as

January - $1,000
February - $2,500
March - $3,000
April - $2,500
May - $1,000

If I added another month it would change to
January - $833.33
February - $1,666.66
March - $2,500.00
April - $2,500.00
May - $1,666.66
June - $833.33

I already thought of manually adding percentages to each cell, but the formula needs to be dynamic.

View Replies!   View Related
Evenly Distribute Hours Over Month
I am currently tracking our company's service tasks in Excel and would like a formula or method that will take the total hours a task has been approved for and equally distribute the hours into columns under each month.

Column A = Resource
Column B = Task Owner
Column C = Task Name
Column D = Task Description
Column E = Approved Hours
Column F = Start Date
Column G = End Date
Column H = Jan 07
Column I = Feb 07
Column J = March 07 etc. etc.

I basically want to take the approved hours (Column E) and have those hours automatically calculated and filled into the month columns (Column H forward), as per the start and end date fields (Column F & G) so the hours are evenly distributed over each month.

View Replies!   View Related
Distribute Data Columns To Rows
I have searched and have not been able to find exactly what I need. I currently do this manually and it is taking too much time.

I start with a file (sheet1) that has data in column A for a location.

Column B has a unique 10 number (each location can have between 1 and 10000). Example:


I need to get this data into the following format for a mail merge:

I was thinking I would set up a sheet2 with all the unique location codes, and then do a match between the location code on sheet 2 and sheet1 and use an offset function?

But I am way over my head when I work with vba.

View Replies!   View Related
Distribute Across Columns Based On Number
I have a list of parts with quantities that i would like to distribute among several columns that represent cities, based on the number of parts in stock. For Example, if i have 5 parts and ten cities, i want it to put one part in the first five. In the case where theres more parts than cities i want it to loop back to the first city and add one and so on. I attached a sample spreadsheet.

View Replies!   View Related
Distribute Workbook With Calendar Control
I would like to distribute a workbook that makes use of the calendar control in a userform.

Trouble is.... Not all the end users have the control installed or even a copy of the mscal.osc file.

This workbook is going to be rolled out to a large number of users with varying levels of computer literacy. For this reason I would like to try to 'attach' the calendar control to the workbook OR somehow distribute the mscal.osc fil and automate the installation.

View Replies!   View Related
Distribute Quantities From Known Average Price
I'm having some problems to find a way to distribute quantities from a known average price. Here's the Example:

My Client bought 100.000 Shares from a Stock, in various prices.

Qtty Price

Then the client wants to distribute this trades for 7 different funds at the same average price.

Qtty Fund 1: 294
Qtty Fund 2: 541
Qtty Fund 3: 1.213
Qtty Fund 4: 13.370
Qtty Fund 5: 16.582
Qtty Fund 6: 28.002
Qtty Fund 7: 39.998

I'm sending an example attached.

View Replies!   View Related
Password Protection & Distribute
Is there a tool that can make an Excel application behave like a ďrealĒ software? Meaning, the user will still be able to do what I planned for him to do with the file, BUT it will be very difficult for the more-than-average user to ďcrackĒ any password protections I have applied to the file? (Something like an MDE file in Access, perhaps?) The reason I am asking is because I have invested a lot of time and efforts into creating an application that I wish to sell to some of my clients, and donít want them to easily find their way into the formulas and macros I have placed in the file.

The protection tool does not have to be an Excel tool, necessarily, but it should not require any programming skills (because I donít have them. If there isnít such a tool? My application contains User Forms (for input), a Database (with Auto Filters and formulas) and some Pivot Table reports. BTW, my application was created in Excel 2003 but should also be used by users with Excel 2007.

View Replies!   View Related
Copyright © 2005-08, All rights reserved