Cell Acts To Trigger Multiple Cells?
Jul 6, 2014
Drop down list containing 5 stages of a typical sales cycle, i.e. client engaged, negotiation, prospect, Short-listed, Win/Close.
These are locate in column B and each cell in column B has a drop down list with these 5 options.
Now, based on which option is selected the corresponding cells in column C and D need to change. This change is defined as follows -
Cells in column C, need to change colour ( i.e. blue, orange or red)
Cells in column D, need to change to a percentage value (i.e. at intervals of 10%, 10%,20%, 30% ...etc to 100%)
Column C (corresponding cell i.e. if B3, implies change to C3 respectively). In this case cell colour needs to change to one of three colours listed above.
i.e. if cell in B2 has client engaged, C2 must show blue, Similarly if B2 has short-listed, C2 must show red.
Column D, Equally, depending on colour in Column C, the percentage must automatically populate.
0-30% = Blue
31-50% = Orange
51-100% = Red
I have used conditional formatting options on a trial and error for testing the above, but not getting much progress. I suspect due to the multiple changes and different simultaneous changes i.e. colour and text, a Macro is most likely needed? (I have limited knowledge of VBA).
In closing there are about 200 rows that need to have this capability. I.e. Contents of column B trigger respective cells in Column C and D.
View 7 Replies
ADVERTISEMENT
Jul 1, 2014
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
View 2 Replies
View Related
Jun 13, 2013
I have completed an automated time sheet but have realised that there are some days where overtime entries make it go wrong.
There is one column where you enter an overtime shift when the cell has an entry I need all the other calculations to be zero
A1 START TIME
B1 END TIME
C1 HOURS WORKED ARE CALCULATED using a formula
D1 OVERTIME COLUMN ( IF AN ENTRY IS PUT IN HERE MANUALLY I WANT C1 ALWAYS BE 0
I tried various ways with isblank but just get true or false.
View 2 Replies
View Related
Sep 20, 2012
Is it possible to write an event trigger macro to format cells? Let's say I want to add a new information to the row which is after FinalRow. Can I force excel to detect the input and then format that new row as previous rows or something custom?
View 4 Replies
View Related
Dec 6, 2013
Colouring calendar entries based on a date match... VBA to colour a cell on each line based on date match
The code works fine if you change the date in the cell and press enter however my date changes are triggered via a Vlookup which the code doesn't recognise has changed so doesn't trigger the macro.
Is there any way to amend the VBA code shown below so that it triggers the macro when dates are changed in column I via Vlookup?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Target.EntireRow.Interior.ColorIndex = xlNone
[Code] ......
View 3 Replies
View Related
Jun 15, 2009
In Excel is there a shortcut key the replaces using your mouse to double click to get into the cell?
View 2 Replies
View Related
Jul 14, 2009
I want to be able to have a link that uses cell data as the passed function parameter!
I want to do this so I can simply copy the formula down for new rows and not have to create a command button for each row?
Maybe there is an entirely much easier way to do what I want?
FOR EXAMPLE, the 1st column would be a link to an entry form passing the 2nd column which would be an ID field.
View 13 Replies
View Related
Sep 19, 2007
I have a workbook that starts off with the following sheets:
MAIN_PAGE - Where filter criteria is selected via checkbox and PLOT button
RAW_DATA - unfiltered raw data
After running a series of macros tied to the PLOT button, two new sheets are created:
FILTERED_DATA(HIDDEN) - where the filter criteria from the MAIN_PAGE is applied, and the filtered data is stored.
CHART - The plot of the hidden FILTER_DATA sheet.
Every time the PLOT button is pressed, the the macro searches for the FILTERED_DATA and CHART sheets, and if present, clears and overwrittes with the new appropriately filted data. This works and suits the user's needs 95% of the time. However....
QUESTION
If the user decides to keep the old CHART and FILTER_DATA as well as the new FILTER_DATA and CHART, I need something to differentiate the two charts and data sheets. My thought was to have the user rename the CHART. The problem is that because the FILTERED_DATA sheet is hidden (and has to stay that way), how can the name change of the CHART be carried over onto the hidden FILTERED_DATA sheet, therefore keeping the plot and it's data in tact? For example, if I append the name CHART with a 1 to make it CHART1, how do I automatically change FILTERED_DATA to FILTERED_DATA1?
View 7 Replies
View Related
Nov 1, 2006
I have a set of data in a given range with one column indicating "priority" of 0, 1, 2, or 3 for each row. I would like to be able to allow users of the file to filter the data simply by changing the text of a given cell, or by choosing among filter options from a drop down. For example, I would label the control cell "show items up to priority:" and then let them enter 1, 2, or 3.
The "auto filter" option won't work because if they use the "sort" options in the drop down it will mess everything up. I would only want to use the auto-filter drop down if I could suppress the sorting options and the other filter options. (I'm after custom options of <4, <3, <2, <1).
Also, the advanced filter method doesn't seem to work well because users would apparently have to re-run the filter each time they want to change the criteria range value.
I've heard of macros that can be triggered based on changes to data in a given cell, but how is this done in this situation?
I guess programming a custom drop down box for selecting 0, 1, 2 or 3 would work just as well as having them type the number in. But I'm not sure how this could be done either.
View 9 Replies
View Related
Jun 20, 2006
Here is what i want to do. # I have a workbook say 'Template', which has two columns: ColA -> Names , ColB -> Value, his acts as a template, i.e a budget. # Another workbook 'sample' which has similar ColA(may contain additional rows) and corresponding value in ColB.
Here is what i need::
I need to copy the contents of ColA and ColB of 'sample' wholse ColB valules are greater than ColB values of 'Template' for corresponding ColA values in both, into a new workbook. SO basically, template acts as a budget check for the sample workbook, and if any value in ColB goes above the budget in template, it will show up in the new workbook. I have attached both the workbooks. Basically the red highlighted cells in Sample are the ones i want in a new workbook.
View 7 Replies
View Related
Feb 21, 2010
I have a single workbook with five spreadsheets. My goal is to move data along a path (or work flow) from one sheet to the next by using a "trigger" pulldown menu choice.
Sheet1 = Prospects
Sheet2 = New Sale
Sheet3 = Upgrade Sale
Sheet4 = Won
Sheet5 = Lost
Sheets 1, 2, and 3 use the same data layout for column A - K.
Sheets 4 and 5 have the same A - K as above and also have columns L - R in common.
The last column in sheet 1, 2 and 3 is a pulldown menu (New, Upgrade, Won, Lost).
Data rows on each sheet start at row 7.
The goal is to use the pulldown choice to remove the data from the current sheet (ex: Prospect) and add it to the next open row in the required sheet (ex: New or Upgrade). I also need to be able to make a similar move from New/Upgrade to Won or Lost.
View 11 Replies
View Related
Dec 19, 2011
I am trying to trigger a formula if a cell (B2) is populated (with anything) but if it is blank for the destination cell to remain blank.
The formula I am trying to trigger is =IF(G18=0,100,100-(100/(1+H18))).
View 2 Replies
View Related
Mar 8, 2008
I have an application that updates the text string in A1 every x minutes (time may vary). I'm trying to develop a toutine that will trigger a Macro/call a function/subroutine once only on each change of data (and then not trigger again until the next change...and must only trigger on change of data in this specific cell). So the timeline looks like this
start A1 = "Apples", Fire macro once
start + 10mins A1 = "Oranges", Fire Macro Once again
start + 25mins A1 = "Pears", Fire Macro Once again
etc.
Triggering the macro is no problem but constraining to one triggering is proving a problem (for me anyway) and is really holding up my application progress.
View 5 Replies
View Related
Jun 10, 2014
Any way to trigger a macro in one cell whenever there's an input in another cell.
Attached is the example excel of what I want to achieve.
macro trigger.xlsx
View 9 Replies
View Related
Aug 31, 2012
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
View 1 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
Mar 24, 2012
I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.
"
JOES TOWING SERVICE [0123451]
123 STREET
CALEDONIA FL 12345
"
"BUBBA / SHOP
ACCURATE AUTO SUPPLY INC [0234567]
21234 HYDRAULIC DR
LOCKWOOD OR 45678
"
View 7 Replies
View Related
Oct 7, 2006
Sheet 2 is the problem.
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I have included a sample.
View 9 Replies
View Related
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
View 2 Replies
View Related
Jan 23, 2010
share a macro to copy cells from range C20:C300 and paste them in C2 on multiple lines. If possible two lines gap between each cell's value when they are pasted in C2.
View 9 Replies
View Related
Feb 27, 2014
I have a spreadsheet that is used to know the status of a job. Depending on which of three columns a date is in it means a different status.
I can get the status where there are no dates (using a counta formula) but it's the other parts I can only partially get to work.
View 4 Replies
View Related
Jul 16, 2013
I have an excel sheet which will be password protected and only opened as Read-Only. There are 3 cells which have a dropdown list associated to select YES or leave blank. When YES is selcted in any of the 3 cells i want to lock the other 2 cells.
View 9 Replies
View Related
Aug 26, 2013
I have come across a format issue I need to deal with it at work.
I have a list of data like this:
A
1234567
8956423
4561237
7531596
8524561
In Column A, I have about 2,000 lines of information. What we want to do at work is create this information to be held in one cell at the top of the excel and in numerical order.
Like this:
A
1234567, 4561237, 7531596, 8524561, 8956423.
View 5 Replies
View Related
Feb 24, 2014
I am trying to create a formula/macro which will take an identification number which is entered by the user and then split it out auto-populating multiple other cells. I have found ways to do this however they all rely on a specified delimitation character which I do not have. Maybe this is possible by basing it on the amount of characters from left to right? This isn't easy as the value I start with does have a clear deliminator between the values for the next cells. Additionally one of the hyphens and the comma will not be used at all in the auto-populated cells.
An example value would be:
7B8-1C-22,24
Gets split into the next four cells in the same row...
7B8-1
C
22
24
View 4 Replies
View Related
May 6, 2013
Other than using text to column feature, how to split values in a cell to multiple cells. For example; in cell A1 I have something like this:
text1
text2
text3
text4
There is no comma or seperation. What i want to do is:
B1: text1
C1: text2
D1: text3
E1: test4
View 2 Replies
View Related
Oct 14, 2009
I am running a 8 ball pool league and I am using a excel file to keep all the results in. We have over 200 people so the list is quite long.
Here is my problem. I have a list of 200 player with columns from left to right stating which days they are playing on. In each of these columns I note if that player won 3-0, 2-1 or lost 0-3, 1-2. The 3 and 0 or 2 and 1 are in separate columns. I am trying to write a formula to look for the players who have won 3-0 the most times to show how consistently well he has played.
I have tried =COUNTIF(A2:Z2,3) and this works fine, but the problem is, is that it counts the 0-3 that he lost as well, I only want his 3-0 win. His win score is always the first column so if he wins 3-0 I want it to count all the 3-0 wins and leave the 2-1 win or the 0-3 and 1-2 losses.
View 9 Replies
View Related
Dec 17, 2007
how to trigger a message box?
if i type TP123 in cell A1 i want it to trigger a message box with a comment
View 9 Replies
View Related
Mar 3, 2013
I attached sample file with data I received (more than 50k rows in each file) is contained in 86 files with varying population lengths in each file that needs to be broken down as shown in the attached file.
View 5 Replies
View Related
Mar 19, 2014
I realised that the screenshot attachment makes much more sense than trying to show within post.
EXPENSE MONTHLY ANNUALLY MONTHLY COST ANNUAL COST
BILL £4.00 £4.00 £48.00
BILL £120.00 £10.00 £120.00
BILL £260.00 £21.67 £260.00
BILL £12.00 £12.00 £144.00
BILL £19.00 £19.00 £228.00
BILL £14.63 £14.63 £175.56
BILL £550.00 £45.83 £550.00
BILL £94.00 £94.00 £1,128.00
For my bills I want to work out first the monthly cost e.g. monthly(B) figure OR annual (C) /12 - and give the result in column (D)
And then work out the annual cost e.g. monthly (B) *12 OR annual figure - and give the result in column (E)
So if there is no figure in the monthly column (B), the calculation will be dividing the annual column figure by 12 and vice versa, if there is no figure in the annual column (C) then the calculation will be multiplying the figure by 12
This is probably a very simple calculation to do, but I am struggling to make sense of calculating results from different columns to give a result in the same cell.
SCREENSHOT.docx
View 1 Replies
View Related