i have one row of values ie c4="-121" d4="122" e4="123" f4="124"
and h4="test" like this .
if h4="right" (i change the value test to write) i want to change the
c4,d4,e4,f4 signs positive to negative
ie c4=+121 d4=-122 e4=-123 f4=-124
i write sample code like this
I need a formula that reads If a number is less than zero then it will show as positive, but if the number is greater than zero then it will show as a negative.
View 6 Replies View RelatedI have 50 share returns for 2009 (A2:A51). I am solving for optimal portfolio by allocating weights to my 50 shares in 2009. in solver, i am changing cells B2:B51, but the criteria is that if any of the 2009 returns is greater than 75 then the weight on that share should be 0. i tried to write an if function in column C of the changing cells but solver overrides the command when i solve.
View 4 Replies View RelatedI 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!
I tried to make use of the indirect function but don't think this is the right type of formula to be used.
Essentially in column A..i have linked cell references
e.g = D2767
However, for the whole of the column I'd like to change the refs to =$D$2767, ALL in column A in one go.
I know that if you place the cursor in one cell immediately after the = sign and press F4 it automatically adds $ SIGNS TO THE CELL, which I find useful for V-lookups, but I obviously cannot do it (fill it) all the way down the column i'm using excel v7.
In addition or related how would I write an IF formula to read eg.
= IF (cell ref begins with C, then link and add PRECEDING AND FOLLOWING $ signs to all refs)?
I have a Macro 'Timenow' to give current time in a Cell of MS Excel, but then it changes all Cell values of sheet where the macro was used.
Sub Timenow()
'
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
Range("F5").Select
End Sub
Even assigning value to variable does not work:
Sub Timenow()
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
Dim TN As String
TN = Format("=now()", "h:mm")
ActiveCell.Value = TN
End Sub
how to restrict the macro to change the value of current/active cell only without effecting other cell values?
I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:
My boss uses the + symbol and the = symbol in his formulas eg "=+E3*E4" What is the advantage or difference in this as to just using "=E3*E4"
View 3 Replies View RelatedIn a formula, what effect does putting a plus sign after an equals sign? e.g.
=+((1+B8)^12)-1. I orginally assumed that it made sure that result the would always be positive but I was wrong.
I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
View 5 Replies View RelatedHow can I add "$" sign in multiple cells such as:
=D7
=E7
=F7
so result:
= $E$7
= $D$7
= $F$7
A solution would be to use the "F4" every cell, but the process requires more time.
I tried to select multiple cells and press "F4" but do not works.
I have a column that tells whether an adjacent cell is positive or negative. I'm trying to come up with a macro/formula that will tell me how long (# of rows going down) a negative or positive sign repeats before changing.
So it would go down the column and count the rows where the same sign repeats. Once the SIGN switches from + to - or - to + the number goes back to 0. Essentially counting the cells that move in one direction (+ or -).
For example. if A1 = +, A2 = +, A3 = +, A4 = +, A5 = -, A6 = + then it would count A1:A4 and output 4 in B4, meaning that the numbers have stayed negative for 4 rows. For A4:A5 nothing should appear as this sign change we don't want to record. Same for A5:A6. I don't know how to make a formula/macro to identify a repeating SIGN, and then dynamically count all those rows until the sign changes. Does this make sense? I've been beating my head against the desk trying to figure it out with no luck.
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 RelatedFor Exempel
A1=B
A2=1
A3="="&A1&A2
I have even tried with concernate and indirect in A3 but no difference there...
I want A3 to return =B1 but it only returns it in text but i want it in form of a formula. Is there a formula that returns text directly into a formula or an easy way to do this?
1. The Text to column function and separate values by the ";" sign
2. Then separate the first column ( which contains both date and time ) into their separate
columns.
3. The 2nd step will result in values such as "mm/dd/yyyy 0:00" and "hh:mm:00" followed by
an "AM" or "PM" which is unnecessary. The code should clean up the resulting date and
time into "mm/dd/yyyy" and "hh:mm" respectively.
4. Flip the rows upside down so that time is decreasing downwards.
The resulting spreadsheet should contain 6 columns and should look like:
12/5/200810:421.266491.26651.266091.26629
12/5/200810:431.266291.266431.266041.26609
12/5/200810:441.266091.266131.265641.26593
12/5/200810:451.265931.266681.265741.26604
12/5/200810:461.266041.26671.266011.26645
Under Solver, the changing cells box only alllow 200 adjustable cells. I had problem that my adjustable cells is more than 200. Is there anyway I can increase it ? maybe using VB.
View 2 Replies View RelatedWhen I use the =RAND() function, everytime I do anything within my spreadsheet the number changes. Is there a way to get that number to change after(for example) 10 refreshes of my spreadsheet instead of every single time I do something to it?
View 6 Replies View RelatedI have two worksheets. One is called "Orders" and the other is "Invoice2".
On my "Orders" sheet I have a listed of product codes and ordered quantity. One column being ordered quantity and the other being product codes.
For instance it is like this:
Product Codes Ordered Quantity
TH1 5
AB3 0
JC5 10
LFK 11
On my "Invoice2" sheet I have a column for product codes and ordered quantity. Basically, I want the "Invoice2" sheet to reference the "Orders" sheet in relation to these columns. However, on the invoice I only want to display the product codes ordered and ordered quantity. For instance TH1 would be the first line and JC5 the second line. This is because AB3 is less than 0. So ever product code on the "Invoice2" sheet must be greater than 0 in ordered quantity on the "Orders" Sheet. This is what the invoice2 sheet would look like:
Product Codes Ordered Quantity
TH1 5
JC5 10
I have a code that clears the content of a cell if the value of that cell exceeds two.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(1, 1).Value > 2 Then Cells(1, 1).ClearContents
End Sub
My problem is the value of this cell comes from a function and changes every second automatically (linked to a data feed). So I'm not changing it manually, therefore my code is not working unless I touch it. I just wanted to ask if I can add a code onto this which checks the values and runs the ClearContents macro if there is any change.. I've got a few sheets like this so I need this to run in the background..
i have a spreadsheet that has 3 shifts. its a weekly data sheet. i have 52 sheets for every week of the year. so far i have a "data" sheet aswell. this is the first sheet. I use this data sheet to generate the information or the other 52 sheets. example is you enter the date of the first monday of the year and it generates the dates on all of my sheets for the whole year. i would like to do something similar for the shifts. i would like to have a cell the enter the shift of the first supervisor on that first monday and have all the shifts generate. 1=nights,2=days,3=afternoons. but i do not want the number to appear i would like the word. the set up goes like this:
DATA E9 = the first number for shift to generate all other numbers
A5 = cell to display first shift
A22= cell for the next shift
A39= cell for last shift
on sheet1 i have A5 as =sum(DATA!E9)
i would like A22 to be the shift after example if A5 as 2 for days than A22 would be 3 for afternoons and A39 would be 1 for nights.
preferably displayed as DAYS/AFTERNOONS/NIGHTS
I'm designing a spreadsheet currently. I want to input a number into cell A1 - for example 13. Now the number 13 has a percentage value of 67.3%. When I enter the value into A1 I want excel to automatically fill cell A5 with the percentage value of 67.3%.
I need to have the numbers from 1-20 all with varying percentage values.
1 = 100%
2 = 97.8%
3 = 94.3% etc..
So essentially I would like to enter 13 into cell A1 and for cell A5 to automatically fill in the value of 67.3%, if I was to change the value in A1 to 10 it would also automatically change the percentage from 67.3% to 74.7%..
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.
I have two defined ranges, and they keep changing to include the workbook name. XV2 =
View 3 Replies View RelatedI'm looking to change raw data into a "point" system. One of my value exchanges is every $1,000 equals 5 points. So if cell A1 equals $4,685 I am looking for cell A2 to give me a value of 20. (5pts times 4 increments of a $1k) The point value only changes at the $1k milestones, so the remaining $685 does not change the point value. What would the formula look like for something like this?
View 3 Replies View RelatedI 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
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.
When I type in a ticker into TextBox2 the form is prepopulated. Sometimes the info that is prepopulated is incorrect and the user should just be able to click in the textboxes and make the changes. The problem is that when the user does click on the boxes and makes the changes and hits submit the combobox values do not change and stay that of the vLookup results. So, the vLookup results can't change even if the user makes the change and hits submit. What am I missing. Do I have to declare somehwere that ComboBox1.value (for instance) is actually the value as of the submit button ebing clicked and not when the vlookup happens?
Private Sub TextBox2_Change()
On Error Resume Next
TextBox1.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 2, False)
ComboBox1.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 4, False)
ComboBox8.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 3, False)
ComboBox11.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 5, False)
On Error Goto 0
End Sub
i have 118 cells and they are changing every 10 second so, how to gather the max value from these cells and displaly them in Marquee box or in pop message?
View 3 Replies View RelatedI 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 RelatedI 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