I have a cell tha't set up as a number format with the validation only accepting 3 whole number characters, however if the number is 098 then it converts it to 98. I need for that cell to be a number type format and to accept 0 as the first character. I thought I tried everything but I'm sure I'm missing something.
View 9 RepliesI wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.
For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?
how to write a formula that shows the sum of a row of numbers and then it's percentage of the overall figure in brackets in the same field e.g.25 (75%)
View 9 Replies View RelatedI am going to create a large drop down list with data validation the list is to do with fitness the list will containt a sport with a numeral after it
for example : GOLF 4.5 then under that Basketball 12
what i am wanting to do is make a calculation based on what sport is selected in that cell for instance GOLF 4.5 is selected in the cell so i need to say valueinA43*D43/B9 can this be done?? Or if not. can i extract the value from the list to another cell without the text and then perform the calculation.........
I want column S to show today's date if any cell is modified within that row.
Private Sub Worksheet_Change(ByVal Target As Range)
Cells(Target.Row, 19).Value = Int(Date)
End Sub
and it works, I get today's date in that row. But when I do a test on the date, (=ISNUMBER) I get false which means I can't check that date with a macro.
I am looking for a solution to split text from numbers.I have found a couple examples on the web but I cannot get the examples to work with the correct syntax to function.
Sample cells. The string could be any integer or floating point number with text. (The text is always after the number.)
1234txt
12.222txv
102.1hknn
Result needed
col1
1234
12.222
102.1
col2
txt
tvx
hknn
I need excluding text from a cell and come out with a random number up to 3 numbers. Please see the attached image;
If you notice in column D there is text and a number that starts with a decimal, for example on the first row is .437 the next row is .215 next row is .63 etc... Is there a way to come out with a random number mixing it up to 3 digits and output those number on column "E"? Im only interested in filtering and coming out with the random number i mentioned .437 .215 .63 etc... not the +/-.010in.
There is no right or wrong number, i just need to come out with random number using up to 3 numbers up.
Maybe for .437 three numbers up could be .440 for the next row .215 maybe a number up .216..etc..
Hope there is a way to do this, my excel file is too long to type a number manually.
I need to have a cell with a date in it then the next cell needs a formula to caluclate how many days it has been since that date???? I'm a real novice with excel.
View 9 Replies View RelatedI use pivot tables extensively in my job. I have several spreadsheets that feed from acess database tables to build pivot tables. Besides the fields imported from Acess, I also create calculated fields. The problem is, for both the calculated fields and the imported fields, the number formatting does not stick. I know how to go to field settings, then to number, and change the customized format. The problem is, once I take that field out of the pivot table and bring another one with a different format (say, bring a field with currency format and take away one field with a percentage format) and then bring the original field back to the privot table, the number format is gone.
This is really annoying since I work with dozens of fields, and it takes time to change the number settings every time I change fields on the pivot table, and the number of decimal places, etc. I stress that I know how to change the number formatting on fields, it's just that it excel does not remember it afterwards.
I thought that maybe the formulas in excel might allow one to include the formating, but I have not been able to find if this is possible, and if so, how to do it. So, for example, the following formula for a calculated field:
=IF(ISERROR(Contribution/'Block Hours'), "0", (Contribution/'Block Hours'))
would be nice if the formula itself could include the number formating. I tried including something like , 0.00% at the end, but it does not work.
Example below of the data I have. I need to convert dates into DD.MM.YY (8 chars) for SAP. I receive spreadsheets where the date is dd/mm/yy and I use the replace to convert the "/" to ".", then format this column as TEXT. My problem is some of these sheets have hundreds of records and rather than individually scroll through and look for data that requires the "0", is there a way to insert this. I can custom format that it looks as it has the zero, but it is not in the true data.
1.10.12 data should be 01.10.12
11.10.12 this is correct
3.11.12 data should be 03.11.12
3.06.12 data should be 03.06.12 etc
I have a spreadsheet with a column full of dates
I used the formatting wizard to make all dates the format of dd/mm/yyyy but, now, when I put a date in the column it throws up a number '41313' instead of a date, even if I manually input the date in the same format as above.
Also, I have tried to turn cells back into general number fields, but this doesn't appear to be changing it back to a date format..
There are HUNDREDS of dates I might have lost if I can't get them to start showing as dates again!
I created this formula =G2*2.9%+.3
what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88
the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30
How do I prevent the formula from calculating if the G column is blank?
I have a column with a general format that looks like this: "057828001 - WACS - Irving".
I need to remove the latter part of the value " - WACS - Irving"
I am doing that by using the replace all and typing in "-*"; that gives me a result of 57828001.
I need a result of 057828001.
How do I retain the leading zero?
I have a column of data that is given to me that is a mix of letters and numbers and I need the numbers to have leading zeros, they must all be three digits. The data has either 3, 4, or 5 letters followed by numbers 1 through 999. Example: ABCD7 I need to change it to ABCD007. I am using Excel 2004 for the Mac.
View 9 Replies View RelatedHow can I change the ....
View 14 Replies View RelatedI am trying to do some basic VBA in my spreadheet, I just want to create an auto-open statement that inputs then next sequential number in column A1 every time the s/s is opened i.e.1,2,3,4 .....
I am using excel 2007 and my knowledge is very limited. I used a code:
Sub AUTO_OPEN
Range("A1") = Format(Range("A65536"), "YYYYMMDD") & Right(Range("A1"), 3) + 1
End Sub
(the first time you open the file you would need to add the first entry '20021122001)
But this does not work and I get a compile error when run thru debugger.
Is there a field setting in pivot tables to return the number of unique records?
Is there an excel formula to define a range of cells, and returns then number of unique records: (i.e =Unique(A1:A10)?
if it was possible to limit data input to DDE (I am using winwedge to capture numeric data). Dave Hawley replied that all I had to do was lock the cells and protect the workbook. I have now done this but the data is not accepted unless the cells are not protected, anyone any ideas on what I am missing?
View 4 Replies View RelatedI have a set of 4 multiple choice answers B,C,D,E column "F" lists a # 1-4 which lets you know which column letter (b,c,d,e) the correct answer is in. I want to set up a formula that will copy the contents of the correct answer (based on that # identifier) and copy it into column "A" which is currently blank.
View 7 Replies View RelatedMy code is correct, I have confirmed this with several people. However, the code does nothing.
View 9 Replies View RelatedI have to validate a cell to only accept the letter a, d ,f ,g h,. I know this is probaly very simple but i havent touched excel since last november and have completely forgotton the most simple of things!
View 4 Replies View Relatedbelow code. I need to change this code to accept 12 digits.
View 13 Replies View RelatedI have a large spreadsheet with approx 1250 rows, the email address was filled in like this: Name (address@company.com)
the software that I am importing the sheet into will not accept the address in this format. I need just the address, no parentheses, name, etc.
Is there a rule I can run to delete everything I do not need so that I don't have to delete info in 1250+rows?
Here's an example of what i want to do
A_________ B Frank Sinatra C Sammy Davis D Dean Martin E Joey Bishop F 3
I want to put "D"'s value 'Dean Martin' in column A - i know it belongs in A because the "3" in column F indicates its the 3rd answer listed i.e Dean Martin. This format would be the same where there are 4 possible answers on the column to the right dictates which answer is correct.
In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.
Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.
Currently Col K has a validation list which comprises dates from 1st of each month from July to June.
I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.
The information is currently part of a Table in Excel 2007 that has about 500 records.
IdeasList
*
J
K
2
Status
Month Completed
3
In Progress
*
4
Complete
Jul-13
5
*
*
6
*
*
Data Validation in Spreadsheet
Cell
Allow
Datas
Input 1
Input 2
J4
List
*
=ValidationList_Status
*
K4
List
*
=ValidationList_Months
*
code which will allow me to update only todays date in particular cell in that cooumn and once the date has been entered it will save as value so that next day it will not change the date.
If I want to let cell accept date or word "NA" only if i use custom data validation.
View 3 Replies View RelatedWith the UDF below and the portion highlighted in red, how can the red part be made part of the arguments instead of specifying it in the code?
Code:
Function Countcolor(rColor As Range, rCountRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim Result
Application.Volatile
[Code]....
I am trying to set up a chart, such that its charted ranges can be change by changing some cell values.
I am having a hard time getting past first base, however.
I have created a named range:
Code:
Name: LowerTrend
Refers To: = Offset(Values!$AV$200,0,0,800,1)
When I try to edit the chart series by placing 'LowerTrend' into Series values it will select the range by showing the dashed line around it, but it will not chart the data and when I exit the 'Edit Series' dialogue and then come back, the named range is not shown.
i have a userform which has more than 20 textboxes. these boxes need to accept numbers only. what codes can i use so that all the textboxes will be formatted in such way?