Changing Values In Column Based On Value In One Cell
Jan 12, 2009
Based on the attached (much simplified) spreadsheet, I want the values in the green cells on the "Month" page (B6:B10) to vary depending on the value in the yellow cell (B4), and for these cells to to look up the relevant column in the "Full Year" page - i.e. if B4 (on "Month") = Jan, look up C6:C10 in "Full Year"; if B4 = Feb, look up D6:D10, and so on. I know I can nest IF functions in order to achieve this, but could be a bit messy. I'm sure there must be a simpler formula within Excel ... just don't know what it's called!
View 3 Replies
ADVERTISEMENT
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
Jun 27, 2013
Is it possible to format a row of cells (B7:KX7) depending on the value in column (G7:G1000) changing?
For example column G might have the following value entered into them.
A
A
A
B
B
B
C
C
C
So all the rows containing the letter A would be formatted one colour, then it would notice it changed to B and format all the rows with the letter B in, then C etc... The letters would always appear in blocks of 10 or more (representing students in a group) so it wouldn't go A,B,A,B,C,D,E
I am not bothered about the letter being tied to a certain colour, it just needs to change when the letter changes. I'd have no more than 10 different variables in this column, meaning no more than 10 different colours to format. Formatting would be black text with different coloured backgrounds.
View 1 Replies
View Related
Dec 31, 2013
I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:
Counter
Column 2
Column 3
[Code]....
Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.
What I would like it to do is if there is a blank value in any column remove column one from the denominator.
Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.
View 3 Replies
View Related
Jun 3, 2008
I am trying to do is to write a code that will change the values of cells B17:B25 to "false" when the user selects "true" from the drop-down box in cell B16.
Here 's my
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "B16" Then
If Target.Value = "TRUE" Then
Range("B17:B25").FormulaR1C1 = "FALSE"
End If
End If
End Sub
This is not working! Nothing happens when I select "TRUE" in cell B16!
View 9 Replies
View Related
Jun 10, 2006
how I can change the color of an autoshape based on a value in a cell?
View 4 Replies
View Related
Mar 2, 2012
it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.
View 5 Replies
View Related
May 6, 2009
I am looking for a way to simply change the values in column B (start time) back by one hour, for instance 06:00:00:00 would become 05:00:00:00. Because this schedule is a broadcast schedule, time is shown in 4 fields hour, minutes, seconds, and frames (hh:mm:ss:ff).
I can't find a way to quickly adjust it, and because my company broadcasts in a lot of different timezones, I spend a lot of time manually adjusting the values myself. It seems frustrating because I know excel recognizes the values, because it is capable of sorting them, of course that may mean absolutely nothing. I have attached a sample file that I hope should make my problem a little clearer.
View 5 Replies
View Related
Dec 15, 2009
Attached is the file i am working with. The table spanning from A:AA5:16 contains hours worked per day of the week with respect to the Project chosen from the drop down menus in A7:16. Cells in the range of F,K,O,R,U,X,AA7:16 total those hours worked on a daily basis. The table below should sum the total hours worked per day according to the Project selected in A19 from the drop down menu, with respect to the Project(s) chosen in cell range A7:16. For example, if i am looking for total hours worked Monday on Project RSB i select "RSB" from the drop down menu in cell A19, i want cell F18:19 to be the sum of cells A7 and A9, with respect to the Project selected from cells A7:16. If I select "FRG" from the drop down in cell A19, i want cell F18:19 to reflect the sum of cells F8 and F10.
View 2 Replies
View Related
Oct 14, 2008
I m trying to get around here within the company I work for.
I'm interested to know how to set a specific range value e.g. "5 to 10" once a value is between those 2 numbers, and then if the value in the next row below is 34.12 it will be "30 to 35".
Here's an example code I constructed, but the darn thing just keeps on running
View 4 Replies
View Related
Jun 26, 2014
I'm trying to execute something like this: [URL] ......
but without changing the cell value... I basically want a button that will wrap/unwrap all of the cells in the "B" column with one click. If i have to do the cell range for every individual cell in column B that's fine, but I can't seem to make the above example work while keeping the cell contents the same.
View 3 Replies
View Related
Apr 11, 2014
I'm creating a workbook to keep track of my utilities payments, one sheet for one utility and so on. I like to copy two cells from each sheet to another one to keep me updated of the amount to pay and the date. an example: column A with text, if text "NEXT" appear in column A, copy the value of two cells (at columns B & C) at the right of "NEXT" to another worksheet, if that possible? Below is a photo as an example:
excel.jpg
View 5 Replies
View Related
Oct 28, 2009
I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:
If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.
View 5 Replies
View Related
Jun 20, 2007
I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg
= vlookup(RC1,table,i,false) where i is a predetermined variable in the code
for i=23, I need the result to be of the form
=vlookup(a1,table,23,false)
View 7 Replies
View Related
Mar 4, 2014
I have 1 sheet called Setup which users enter their character name, their class, and their race, additionally the entire workbook manages data for up to 9 individual characters.
On another sheet called C|R|E, I have tasks listed that are based on each class type and each race type that are available. Currently there are nine classes and four races. Currently on this sheet each class and each race has its own individual data lists and such, and formulas are done in a way that user inputted data can be entered anywhere, but wont count unless the class and race match those selected on the Setup sheet. To keep the sheet short in appearance I used command buttons to hide/unhide classes or races not in use by the user.
What I was wondering is (and this could be hard to describe), can data in one column be changed to reflect data based on another column's focus? I will explain in more detail.
Column B contains the task information for each Class and Race. For example Cells B13-B51 for one class, Burglar. Columns D-L is where users enter data to show if the task is completed or not. In this example lets say the users first character listed on the Setup sheet is a Champion. What I want to do is check the Setup sheet Char 1. Then based on what Char 1 is selected as on the Setup Sheet, C|R|E B13-B51 data is changed to reflect it, IF Column D currently has focus. If Column E gets focus then B13-B51 changes based on the class selected on the setup sheet for character 2. and etc.
If this can be done I can remove the 13 different sections and have it down to 2 sections, one for class, one for race. For now I am using check boxes to hide classes and races they are not currently using, but would like to move all classes into one table and all races into one table to they dont have trouble with columns they have to skip over currently.
This is a sample of how I am currently working on the C|R|E sheet, the full code is much much longer since I am using so many command buttons.
View 1 Replies
View Related
Oct 30, 2007
I'm trying to update a series of different sheets in a book based on what month I have filled in data for. For example, at September Month End, I will fill in data vertically. Now, I have different tabs that reference certain cells in that column. There row number never changes, but once a month I have to go through and increase their column letters by 1, and there are a lot of places to update. Can anyone lend me some ideas or a solution to how I should go about using formula/macro to update these cells.
View 9 Replies
View Related
Mar 17, 2014
Please see attached sample worksheet. Column A will be generated by the user manually.
I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.
I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.
Book2.xlsx
View 3 Replies
View Related
Aug 16, 2013
I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.
Below is an example with the solution I am attempting to produce.
Sample Table
Name
Dept
Abbruzzese,James L
188100
[Code] ......
Output
Name
Dept
Abbruzzese,James L
188100, 231100, 600377, 600656, 600663, 600708
Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435
View 2 Replies
View Related
Jun 24, 2009
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
View 9 Replies
View Related
Dec 5, 2013
Below the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
View 2 Replies
View Related
Jun 11, 2014
Here is a table
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail
[Code] .....
Here is some context
Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).
In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4
[Code] ....
What I need is a formula that will produce this result.
View 5 Replies
View Related
Oct 25, 2006
I currently maintain a program that creates an Excel report. The report is full of formulas (mostly vlookups).
After the report is built, I'd like to get rid of the formulas. The only way I know to do this is to copy the report and do a paste special values.
View 3 Replies
View Related
Feb 4, 2014
I want a functionality in a sheet wherein if i select a value from a drop down, i will get specific fields to be entered in the sheet below and when i select a different value from the drop down the fields to be entered should change.
View 3 Replies
View Related
Mar 27, 2009
Made a macro to Copy a value and paste is as "Special" for Value only.
The problem is it won't function correctly Unless I force a switch to the sheet where the calculations are. (Which I want to avoid)
The current code is:
View 5 Replies
View Related
May 4, 2009
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
View 7 Replies
View Related
Jun 13, 2013
I am working on a workbook with 200+ sheets and an index linking to each of the sheets. On the sheets there is a cell containing the results of calculations and I need the cell referencing the sheet in the index to change background colors depending if the calculations value is greater or less than 0. I currently have working code that also changes the tab color of each sheet depending on the value, but i am trying to add the index cell background change functionality into that. also, the index listings is subject to change. my working code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("d34").Value < 0 Then
Me.Tab.ColorIndex = 3
ElseIf Range("D34").Value > 0 Then
Me.Tab.ColorIndex = 4
Else
[Code]...
and the code i am trying to get working is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngX As Range
Set myRange = Worksheets("Index").Range("A1:A500").Find(Range("C2").Text, lookat:=xlPart)
' the C2 is the reference the title that would be on the index
If Range("d40").Value < 0 Then
[Code]....
I keep getting the runtime error '1004: application-defined error or object-defined error the thing is, i ran the cell background changing code in a normal macro before integrating it in my other worksheet code first and it worked fine. I have a little code experience, though not much in vba and I am unsure what the issue is. if there is another better way to accomplish what I'm tring to do, that would also be fine.
View 4 Replies
View Related
Jan 12, 2009
I did a search on this site and found some code I was looking for (see link:
http://www.excelforum.com/excel-prog...e-in-cell.html - Leith Ross's response code).
The code works perfectly, however, if I save the workbook with a different spreadsheet on top than the spreadsheet referred to in this code, I get an error message: Method 'Range' of object '_Worksheet' failed.
I should state that I did change "Private Sub Workbook_Open()" to "Private Sub Auto_Open()".
I assume I need to modify the code but am unsure as to how.
View 11 Replies
View Related
Dec 30, 2009
Quick question, I know how to change a cells background / text color based on it's own value using conditional formatting but what would be the formula to change cell A1's background color based on the value of cell C1?
View 6 Replies
View Related
Jun 8, 2009
Is it possible to select a cell on any given row and the value of a cell on that row for example C5 & C6 are shown in cells A1 and B1 respectively? So if the user moved down the sheet selecting different rows the data would change?
View 4 Replies
View Related
Sep 21, 2012
I'm trying to change the colour of a row based on the value in a cell (in a column) and am wondering how I can do that. Conditional formatting doesn't allow enough colours unfortunatly. Ive googled it and can only get a cell colour to change not a row colour.
To give an explanation:
Sheet 1: Rows 5 - 100 colour to change based on Sheet 2 Column K5-100.
Column K5-K100 will have values between 0 and 10.
View 4 Replies
View Related
