I have a cell with a dropdown picker. If the user chooses "Distractor" from the picker, Worksheet_Change triggers an Inputbox to open, asking what kind of distractor it is. We'll use "Gloves" for this example.
I want the cell to display "Distractor - Gloves" while keeping the actual value as "Distractor"
The following is not working for me:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DistractorText As String
Select Case Target.Address
Case "$B$23"
If Target.Value = "Distractor" Then
DistractorText = InputBox("Type of Distractor:")
Target.NumberFormat = "@" & " - " & DistractorText
There's more code, but I copy-pasted the trouble area. The last line using the NumberFormat property is giving me trouble.
If I change the number of A2 to 0 decimals places, the value shown will be 2 (which is correct, of course), but the value of A3 becomes 20. I would like for this to stay at 15 because the correct value of A2 is 1.5, not the rounded value of 2.
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
Please take a look at the attached spreadsheet. in the ODDS column D, after download the some of the data has been changed from say 100-1 to a series of numbers how do i change them back i would also like to show the odds is they were 100-1 as just 100 and say if the odds were 13/2 then i want to show 7.5 basically i want th first number divided by the second.
This should be pretty simple but I don't know how to do it. When I run the macro recorder, it doesnt record the change. I just want to change the number format of the chart labels from currency to a normal number and visa-versa. Why doesnt this work?
I would like to change the number format of a cell in function of the value of another cell. For example, if A1=1, then I would like the cell A2 to be displayed as a percentage. If A1=2, then would like the cell A2 to be displayed as a number.
I need in changing the format of sheet2 to exactly like the following image below. *there are some codes in sheet2 which I think is the place to change the format.
The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format. That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values. I added the function Isnumeric to this line:
Isnumeric(cells(i, "M").value) Int(cells(i, "M").value) which I thought would only evaluate numeric cells only, but this was unsuccessful.
Original Code: Unedited.
For i = 4 To LRowf For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS") On Error Resume Next If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _ Cells(i, "M").Value Int(Cells(i, "M").Value) Then Cells(i, "M").NumberFormat = "# ?/?" On Error GoTo 0 Exit For 'End If End If
i have a cell which has a time format. but currently its displaying 2.00907E+13. is it possible that i can convert it into the normal time format? for example B2 in my sample is 20090718170156 which actually means 2009/07/18 17hrs 01min(s) 56Sec(s) and after converting, how can i get the time difference between start time and end time?
Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.
When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.
Example:
B20= 1:15 (1h15m) NOT 1:15:00 AM
B24= :30 (30min)
B28= :45 (45min)
B32= 1:45 (1h45m) NOT 1:45:00 AM
B38= 4:15 (4h15min)
I have attached a copy of the form for better clarification.
It doesnt seem to be complicated, but it's certainly a lot more than I thought.
I have data in cell A1 that often changes its number format(from $ to %), can i have the number format change in cell B1 whenever the number format in cell A1 changes?
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
At the top of my worksheet, I have a summary table of four regions. These take subtotals from the rows below, which are grouped by each region
I would like to create hyperlinks in my summary table to then go directly to the row below it relates to, however, because I insert and delete rows, I'm not sure how to make my hyperlinks dynamic.
E.g. Four regions are A, B, C and D A is currently found in A10, B in A25, C in A40 and D in A60
If I insert 3 rows above row 15, A is still in A10, B is now in A28, C in A43 and D in A60 (to confirm, summary table and linked cells are all on the same worksheet)
How can I make the hyperlinks in the summary table change automatically so that when clicked it takes me straight to the new row location instead of the previous one?
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
I am having slight trouble with the formatting of some cells. Every month I will be copying data into a row, the problem is Excel thinks it's a date instead of an integer of some sort.
The value copied will look something like this: 79:42:00 (meaning 79 hours and 42 minutes)
Excel is taking that and interpreting it as: 1/3/1900 7:42:00 AM
Is there an easy way to format this so Excel thinks it's a number (even if 79.7).
I tried to change the format of the cell to "Text", but it changes it to: 3.32083333333333
is it possible to custom format a cell in order to:
a) Present 3.33333 as 3.33 (max. of 2 decimal digits or less) b) Present 3.333 as 3.33 c) Present 3.33 as 3.33 d) Present 3.3 as 3.3 e) Present 3 as 3 (NO zeros and NO decimal dot)
I'm working with Loan amounts and i need to know how to set a excel spread sheet to conditionally format a cell that causes a sum formula to exceed a preset limit.
Unable to match as 1 column has format where number has a space in front of first character.
_425638 as opposed to 425638
Not an underscore tough
Have tried text to columns - have tried editing cell and replacing space with nothing via edit/replace but no joy only way appears to be entering cell and deleting manually - there are thousands to update.
I was wandering is there a way to format or put a formula which will format the numbers to be with two decimal places. The cell containing the formula is already formatted as number and the source tables are numbers with two decimal places.
i want the result to look like this $24.68 & $26.20
I need to evaluate some offers and for the management I need to follow some strict rules like:
1. € 1.000.000 instead of €1000000. But that's not the problem 2. to use maximum 3 digits... Example: 10,2 or 6.12
I tried with an if function and then round, but I think that's not right way. If number < 10, then round with 2 decimal places else, round with 1 decimal place
(3) examples when I type a number to a cell: If I type a number "1", I want that cell to show 1.000% but not 100.000%. If I type a number ".2", I want that cell to show 0.200% but not 20.000%. If I type a number "25.5", I want that cell to show 0.255% but not 2550.000%.
I play around with the below custom format cells but they do not work. _0.01*#.000% _0.01*#,.000% 0.01*#,.000%
I could use two cells one for number, other cell has a % mark. But I rather to format a single cell if it is possible.
I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?
I enter in numbers, eg: 1, 2, 10, 24, 100, 1000 in a consecqutive cell range They display as: 0.01, 0.02, 0.1, 0.24, 1, 10. Default cell format is general. If I change cell format to Number (with default display of 2 decimal points) it displays: 0.01, 0.02, 0.10, 0.24, 1.00, 10.00. If I also specify no decimal points, it displays: 0,0,0,0,1,10
The only way I can get the numbers I enter to display (and store) as 1,2,10,24, 100, 1000 is to change cell format to text and THEN retype th numbers into the cells. But, then it creates the green error comment in top left of each cell.
I have used this forum periodically and it has saved my bacon on numerous occasions so I know someone can help with this!
I created a workbook for staff planning and it's becoming a behemoth!
I'm wanting a range of cells within a row to change the number format to "[hh]:mm" based on a value in the 2nd column of that row. I have played about with a few things and can't seem to get it to work. What I have so far is this:
I'm trying to insert a random number into the unprotected cells on a worksheet depending on the type of number formatting in the cell. There are 4 basic cases:
Number formatted as percentNumber Formated general with no decimal placesNumber formatted general with two decimal placesNumber formatted in thousands
The code below only contains three criteria, I was trying to get that to work first....Based on this criteria I want to insert a random number of an appropriate size. Please find the vba below.
Sum randomnbr() Dim F as String
For Each c In ActiveSheet.Range("A1:AK2500") F = c.numberformat If c.Locked = False And IsNumeric(c.Value) = True And c.EntireRow.Hidden = False And c.EntireColumn.Hidden = False Then If F Like "*#,##0*" Then c.Value = Round(Rnd, 0) * 1000000 If F Like "*%*" Then c.Value = Round(Rnd, 2) Else: c.Value = Round(Rnd, 2) * 1000 End If End Sub
