Any way I can get an equation to only apply to cells with information in? E.g. I have two columns, one with 'disposal dates' in and the other with 'extended disposal dates'. I would like to get the difference between them (Extended disposal dates- disposal dates) however not all of them have an extended disposal date, therefore some cells display as '#VALUE!' because its calculating the disposal date-blank cell....
View 3 Repliesi am plotting graphs which i need the equation of the line from. i have been manually typing the x^3, X^2, X^1 and X^0 values into 4 cells which is then used elsewhere, but wanted to know if its possible to get some code to do that for me?
View 2 Replies View RelatedI want to do is a simple list, 2 items, call them "Thing_1" and "Thing_2". I have an equation in a column of cells. The equation is a simple If statement. If the user selects "Thing_1" then the first equation will run, and if "Thing_2" is selected then the second equation will be used. That's it. No more complex than that.
My original questions about how to initialize a ComboBox are cross-posted here: http://www.excelforum.com/excel-prog...ml#post1996135
im trying to implement the equation bellow in one cell so that i can then copy paste to about 300 cells.
A= 0
A= A + {IF(B279 = B(i), C(i), 0)}
= ‡”i= 6 B(i) & ‡”i= 6 C(i)
........i= 274 .............i= 274
Is there a way of copying an entire sheet, but pasting only the cells that don't contain an equation?
I have a number of populated templates that need to be copied into a master document, both formatted and laid out the same. However, I have some instances where the templates have been fiddled around with so the formulas have been corrupted. So, I'd like to pick up the raw data without the corrupted equations.
Is there anyway to apply the $ to cell references in formulas across a range of cells? For example, I currently have the following in Column A:
=AAA_SR_F_1!$G5
=AAA_SR_F_1!$G6
=AAA_SR_F_1!$G7
And the following for Column B:
=BBB_SR_F_1!$G5
=BBB_SR_F_1!$G6
=BBB_SR_F_1!$G7
Is there anyway to apply the $ to the entire range listed, i.e. AAA_SR_F_!!$G$5, etc.
I have a worksheet that I use to check invoice prices, which automatically indicates to me if any prices have changed, by using conditional formatting. For example, Column A contains original/current price. Column B contains new price (which I enter by hand and is calculated to round off, etc via other cells). If the price changes in B, a simple formula in Column C shows the new price and uses conditional formatting to turn the cell yellow so I can see that it needs to be changed in our system. However, we also must account for shipping charges on our invoice - which change with each order. So, when calculating new price, I also add shipping costs. This same setup repeats for every row of items.
My question: if I change the shipping, but only change the prices on a few rows, the prices on ALL the rows change because of the new shipping. Is there a way to say, select that only certain cells were (received with new price) and have the changes apply to only those cells?
Example:
Row 1
Cell A: current price including shipping from last invoice (which is calculated in several other cells to include shipping, round off, markup, etc)
Cell B: changed price (from invoice)
Cell C: compares A and B using =IF(F9<>H9,H9,"") and applies conditional formatting if price changed.
Row 2
(repeats)
If we only get a new order of row 1, the shipping changes. But I DON'T want row 2 to change it's price, since the price did not in fact change.
So basically, what I want is to be able to check off, or select, only the rows that come with latest invoice.
I attached a sample worksheet. Excel Forum example.xlsx‎
How do I change this formula to represent a range? I want cells C2:C18, but I ran out of arguments - can't add any more. If DRC or Production shows in the column, I want the word "Outstanding" if not, I want the word "Completed" .....
View 9 Replies View RelatedI have data in columns that I want to create charts for. However, this data is dynamic and the number of cells where there is data varies. The rest of the cells that don't have numbers have 0s, however if I applied a macro to the whole column all of the zeros would show up in the resulting charts. How can I create a macro where I can highlight just the cells (the ones with non-zero numbers) that I want a chart to be generated for. Or is there a way to ignore zeros/blanks completely in the macro?
Example: here are two columns with data. I want to make a macro that creates a chart by just highlighting the actual numbers and ignoring the zeros.
Mexico
0.171896
USA
0.132952
Germany
0.113626
Colombia
0.096198
[code]....
Not sure what I'm doing wrong here but I think my syntax is wrong, here is the
Sub CalculatSG()
Dim FinalRow As Integer
FinalRow = Range("C" & Rows.Count).End(xlUp).Row
Range("BU5") = "=(BT5/100)*AE52"
Range("BU5").Copy
Range("BU5:BU[" & FinalRow & "]").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
My objective is to apply the same calculation to the range of cells; the range may change which is why I have defined FinalRow and passed this as a refernence.
I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I was wondering if is possible to apply a conditional format rule to a range of cells only when certain other cells outside of the range are the active cells. eg row C4-J4 has a conditional format to identify duplicates from range C5-C10, but I also want the same conditional rule to apply separately and uniquely if the active cell falls within the next active range D5-D10. So basically only apply conditional formatting separately and uniquely as the active cell moves its way across the columns?
View 3 Replies View Relatedi m trying to set up a macro to convert a range of user-highlighted(selected) cells to 3 significant figures: for example, convert 0.135564 to 0.136
the equation i found elsewhere online: ROUND(xx,3-(1+INT(LOG10(ABS(xx))))). but i can't quite figue out how to apply the equation to a selected range of cells via a macro.
formatting time as follows :
I want to apply a score to the time cells ( decimals ) Basically i want to apply this set of criteria to a Row >
IF LESS THAN 0.00694 (10mins) then score 3
IF LESS THAN 0.01388 (20mins ) then score 1
IF BETWEEN 0.01388 & 0.9811 (20mins and 23:40:00) then score 0
IF GREATER THAN 0.98611 (23:40:00 ) then score 1
IF GREATER THAN 0.09930 (23:50:00) then score 3
I have this code :
IF(D9<0.0069444,"3",IF(D9<0.01389,"1","0"))*AND(IF(D9<0.986111,"1",IF(D9>0.993,"3",)))
it seems close to what i need but gives a #VALUE error ....
I am using DSUM functions which result in having to have an extra row beneath each of the DSUM formula rows (for the criteria arguments)
The code below checks for color coding and then populates 1 or a 0 in the database for the DSUM formulas
Im trying to optimize the code so that it runs faster. how can i code to skip the rows that do not contain data?
other optimization / best practices that you recommend are appreciated!
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errorout:
If Not Intersect(Target(1, 1), Range("j3")) Is Nothing Then
Application. ScreenUpdating = False
For Each rcell In Range("j11:n731")
If rcell.Value = 1 Then
rcell.Interior.ColorIndex = 37
Else
rcell.Interior.ColorIndex = xlNone
End
I have used VBA to apply conditional formating to a range of cells. i.e if cell B14 <>"" then row 14 is pale blue untill cell V14 is populated with the time then it removes the formating. One criteria is that if Q14(21/08/06 20:00)>NOW(AA2) and < NOW+1(AA3) then Row turns green(i.e. is due in the next 24 hrs).
Problem is that this formula is applied after a field is updated. When 21/08/06 20:00 comes and goes the row remains green unless I update one of the fields along that row. What would I have to do to make the formating change back automatically when the critera is no longer being met. Here is my codethere are 2 other if statements similar to this with in this code but this is the only part that shouldn't require any user input for the formatting to occour)
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B13:AP162"
On Error Goto ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "AP").Value = "YES" And _
Me.Cells(.Row, "Y").Value <> "" Then
Me.Cells(.Row, "B").Resize(, 30).Interior.ColorIndex = 43 'mad green
Else
End If
End With..................
I want to copy Four Cells of info from one tab to another if the value of a cell is a specific value is this possible?
View 2 Replies View RelatedI have a excel sheet from a supplier of mine, that has a mixture of text, and numbers and more importantly "Custom format cells" that have prices in them. I need to apply a multiplication formula to each price, but they are all in different rows and columns, and it will take forever to type a formula into each cell.
Is there a way that if I can select all these cells, to add a global formula that will update all these cells?
Column b in sample is conditionally formatted based on it's values. I want to also apply that same formatting to the person's name in the chart in D2:I9. For example, Jeff is in bottom 50% so cell B2 is shaded red with red text. I would like to apply that same red shade and red text to all the cells in my chart that say Jeff. Also, as example, all of the cells in my chart that say Kelsey would be formatted with green shade/green text and so on...
View 1 Replies View RelatedIs there a way to apply conditional formatting to a range of cells that contains formulas?
View 6 Replies View RelatedI'm looking to see if there is a way to take the decimal place formatting of cell and apply it to other cells.
For example, if someone types in 0.0001 into a source cell, I'd like to take that decimal formatting and apply it to other destination cells. This way when values are typed into those cells it will automatically display 4 places past the decimal, no matter what the value. ie 50 will display 50.0000
I have data that I've converted from a different source, and the formatting doesn't always come out as I'd like it. For example, I have data that has two different values, and I need to split them into different columns. Is there an easy/quick way to do this?
15* 27016* 1324517* 2086518* 24943
Whenever I get information from finance.yahoo.com or from my job and put it into excel all the information seems to go in one cell and numerous rows. (I think its b/c I am putting information that isnt' meant to be in excel and forcing it to open up there.)
For Example in cell A1 I will have
"AIG , AMER INTL GROUP I , 66.08 , 1:16pm , 169.455B , 16.736 , 2.01 , 9.47 , 0.78"
Cell A2 I will have
GOOG , GOOGLE , 508.9 , 1:21pm , 158.845B , 22.361 , 17.511 , 26.29 , 1
What I want to see is cell A1 as AIG and cell B1 as 66.08 and cell A2 - GOOG and cell B2 508.9. I don't even want the rest of the stuff.
Is there any way or formula I can use to just pull the information out of cell and have it separated for me?
I have a drop down list that shows phrases for different math functions, like "divide by 2," "divide by 4," divide by 8," etc. When one of these items is selected, I want it to perform that specific calculation on a range of nearby cells.
However, I also want the user to be able to see the original value of the cells if they select "show original value" from the drop down list.
I have attached a workbook as an example. I think macro would probably work best.
oz_list_math.xlsx
I am having trouble getting some conditional formatting to apply to all cells in a column in a pivot table. Currently, the conditional formatting is only applying to the top level items in the pivot but is not applying to the lower level items. I can see why it is doing this. the range in "Applies to" is only specifying the rows that contain the top level items. I tried to change the range to D10:D647 but, it reverts back to just the top level items. How to get it to apply to everything?
Image attached : Capture.JPG
I have productivity data sheet of employees for a month and want to update in a tracker sheet.Every productivity sheet has 5 columns containing numbers.Since its monthly it would be contain 30-31 rows and.I want to copy this data then go to tracker apply filter with respective employee name and paste it there.Is there a way to do this using vba code?
I have prepared vba code to copy paste individual rows from productivity sheet to tracker.But preparing row by row code makes it way too big.Hence i am looking for another solution.
I have a sheet containing lots of information (lists of flight information to and from all countries). So entries are duplicated in the entire sheet. For example LITHUANIA, USA, CHINA... appears lots of times. I only need to highlight particular countries.
I would just use conditional formatting, but maybe there is a quicker way to do that?
So there is a list of 50 countries in one sheet and in another sheet I got all the info. I need those 50 countries from the list to be highlighted in the info sheet (sheet has many columns and rows).
I have a very simple userform that has name, address, phone, etc.
I want the command button the insert the info from the user form into certain cells in my spreadsheet then clear the info out of the userform and close it.
I have this code going for a sheet i've been working on, but thers only one thing I can't figure out how to do.
I want it to take the text that was entered in the "Grid Date" input box and stick it in the bottom 10 cells (plus one space). IE - if A14 is the last used cell, paste it into A16 through A26.