I am trying to return a number with a specific format. I need Excel to look in a particular column and when it finds the entry in the correct format to return it in a new column. I want it to repeatedly return the number to the new column until it finds another number of the same format in the original column.
View 2 RepliesIs there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?
This has seemed to have happened on a computer at work.
In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"
If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies View RelatedI'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:
474556788 to 047-455678-8
Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)
Is there a way to make a macro convert '1' to Yes and '0' to No? I have 2 columns (F and G) in a worksheet that contain the value of 1 or 0, but I need to convert them to Yes or No. I have tried some fo the samples but they pop up a msgbox which is not required.
View 2 Replies View RelatedHow can you sort item numbers, targeting only the numbers in the mock up file?
Example:
item number: GW14SO0000003
item company: GW
item location: 14SO
item counter: 0000003
How can I only sort the item by the last 7 digits "0000003"
I have tried to write code to format numbers to zero decimal places as well as to justify the format as the zeroes appears as 00000000 when imported.
I need the macro to do this on the first 7 sheets.
I also need ------- lines and ) to be cleared on the first 7 sheets.
I have attached sample data and my code below
Code:
Sub Format_Data()
Dim Cnt As Long, i As Long
Cnt = Sheets.Count
Application.DisplayAlerts = False
For i = Cnt To 7
Range("F:H").Select
With Sheets(i)
[code]....
how i convert text format into numbers format in vb.
Currently
i have a formula in vB:
Private Sub Textbox3_Change()
Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value)
End Sub
however..when i sum it up (in excel) using"=sum" formula...it ooes not sum up
I faced an error "number stored as text"..how to i convert it to numbers format in vb.
I am trying to format colums containing numbers & units of measure to numbers only.
I am using Office 2003.
How to format different numbers to the same format (as text). Mainly I need code that would format numbers like 25, 25.36 or 254.60 to numbers looking like this 000002500, 000002536, 000025460. They must be of nine digit length with the last two digits as decimals.
View 9 Replies View RelatedWhen working in Excel I format all numbers with the accounting format. I often use the single and double underlining feature on the Font tab of the Format Cells dialog box. Sometimes when I use the double underline it only puts (and prints) one underline. I've worked with the formatting many times with little success. The only way I can get the double underline to show up in this situation is to change the vertical cell alignment to be centered and then increase the cell height. This causes me other formatting heartaches. It's like the double underline is there it just will not show.
View 9 Replies View RelatedI have a value that the user is suppose to type in the range(A3). The format is as follow: 20091227-I1 (yyyymd-Letter and No.) The letters are I and EK and after the letter a # from 1-10.
I want to create a macro, which reads the Range(A3). If the format is incorrect, then a MsgBox would display "Typing error. Please type the year, month, date, dash, letter (I or EK) and number."
I don't have a problem creating the message box. My problem is validating the Range(A3) with the specified format.
I've attached the example spreadsheet.
I have 3 columns with multiple variable rows.
The 4th column is the specific result for those 3 variables.
I need a formula/s that will give me the correct 4th column # based on the previous 3 columns what would be input using dropdown lists I believe.
So for instance the final end result would be an HTML web page with 3 drop down boxes representing the first 3 columns and then based on what is selected in those 3 dropdowns the correct 4th column # would be shown.
In the attached document i need to work out the following solution for a formula but im baffeled! In the completed column i need to show 'ongoing' if there are any 0's in the 101 - 104 columns and 'completed' otherwise.
View 3 Replies View Relatedif I wan to get the value of specific column, how should I use it by vlookup?
The formula for vlookup is like is =vlookup(lookup value,table array, col_index,range lookup)
If the col index range, can i specific any column, example of I lookupvalue from A, then get value from CH?
I have 3 named range columns to query and a fourth from which I wish to return a value
Column 1 is called DateOE, column 2 is called NameOE and column 3 is called RunOE. The column from which I require the value is named ConcOE.
I have the following formula:
=IF(AND(DateOE=28,NameOE="Wayne",RunOE=1),ConcOE,"No Data")
My logic dictates that the formula should return whatever was run by Wayne on run number 1 on the 28th day from the values within ConcOE or return the value No Data.
The run numbers are unique, which is the identification key.
Every time I try it out, I have a #Value returned and if I convert to an array, the value no data is returned, despite the fact that I know what value should be retuned.
I have a column : Customer Number
Customer Number: we have 2 types of customer numbers (12 or 13 alphanumeric)
eg: 446828442414 (12 alphanumeric) or 8144144141442 (13 alphanumeric)
and need them formatted according to number of digits
If 12 alphanumeric: 00<3space>0<3space>00<3space>00<3space>00<3space>000
i.e. : 44 6 82 84 42 414
or if 13 alphanumeric: 000<3space>000<3space>00<6space>00<3space>0<3space>00
i.e.: 814 414 41 41 4 42
suggestion: IF (No. of characters = 12) use 12 alphanumeric space format
or
IF (No. of characters = 13) use 13 alphanumeric space format
I'm in the process of setting up an Excel document and I need to be able to have it display dates in a specific format. I need it to express just a month and year such that the month is represented by a letter (A thru L) and the year is expressed as its last two digits such as in the these examples:
May 2012 = E12
Mar 2009 = C09
Nov 2011 = K11 etc.
I want the date to come out in this format regardless of how the user enters it.
If A1 is 81209, how can I use functions in B1 to convert it into DEC/09/2008?
View 4 Replies View RelatedI work in the flooring industry and the format we use for cuts of carpet/vinyl is 40-3 (for 40.25 feet), etc. So we are trying to create an excel spreadsheet that will add up these cuts. Right now it's kind of a mess because we have decimals in a column we will be hiding and are converting that to our industry format.
The formula I am having trouble with is as follows:
=MID(A18,1,FIND(".",A18)-1)&"-"&MID(A18,FIND(".",A18),3)*12
The problem is that whenever the totals end in zero, the formula won't convert 40.00 to 40-0. It gives me an error every time. If the total is 40.25 it will convert it to 40-3 just fine.
Is there any way to format the date to DD/MM/YY, and user should be restricted in entering any other formats other than DDMMYY
View 3 Replies View RelatedI am trying to write a macro that given a selected range of cells goes in each cell and bolds the first 2 lines within the cell. An example of this would be if the cell contained:.............
View 2 Replies View RelatedWhat I have done is create a macro in Excel and then opened up the VBA editor to make a lot of changes to the initial macro. I am reformatting a whole lot of separate spreadsheets over many workbooks.
Here is the code that is generating the error (Run-Time error '438', Object doesn't support this property or method):
Code:
Range("E2").Select
Selection.Format = "long date"
With Selection
.Font.Size = 11
' .Format = LongDate
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
As you can see, I first tried the ".Format = LongDate" and did put LongDate in "" which did not work, then I tried the "Selection.Format = ..." after the "Range" statement, and that did not work either. So you will actually know, I am trying to set the format for the cell "E2" to a Long Date format.
I'm looking to generate a chart out of a table of data. I want the Y-axis to show a percentage return on an investment.
On the x-axis I want to have a range of scenarios from a market average return of -40% to +40%. Obviously, I wish for 0% to sit in the middle of the x-axis.
I'd want to have 3 lines observable in the chart. I'd be looking to build the above out of a table almost exactly like this:
Market ReturnDeposit90% Model100% Model403.51412303.51211203.5910103.57803.543-103.520-203.5-1-3-303.5-4-5-403.5-8-9
I’d like to return the range (or cells address) that includes text formatted in Bold and Arial font, this is always in Column “B”, so I’m actually behind the rows number....
View 9 Replies View RelatedI'm trying to convert a date in the following format: 19970121 to 1/21/1997 and am having trouble getting rid of the leading zero in the final version. I also need it to pick up the 2 digits in months that have them from 9 (Sept) through 12 (Dec), though, for use in a VLOOKUP function. Can this be done at all?
Here is the code I've been using so far that doesn't entirely work for what I need it to do:
=CONCATENATE(LEFT(RIGHT(B2,4),2),"/",RIGHT(B2,2),"/",LEFT(B2,4))
where B2 refers to the column the original date form (19970121) is in. I'm still stuck with the leading zero in this code.
Okay - the variable myDTStart is calculated off of this formula and I've put in what I have it testing as now and the result I am getting:
Code:
myDTStart = Application.WorksheetFunction.WorkDay(MyDate, (myBMLT - myAssy - OMSpd), holidays)
MyDate = today's date (coming from a cell - current value of 2/6/2012)
myBMLT = # of days (coming from a cell - current value of 15)
myAssy = # of days (coming from a cell - current value of 3)
OMSpd = # of days (coming from a cell - current value of 1)
holidays = refers to a range of cells that has holidays (Dim as range)
It is currently returning it as a "40998" , but not in the right format in a msgbox. How do I get it to return it in date format "mm/dd/yyyy"? I've tried doing it with a With statement and a Format statement, but can't get it to work.
My workbook contains two worksheets a scorecard template and a data sheet. I use the lookup function to retrieve statistics from the data to populate various fields in the scorecard template. The value can be numeric, currency or percentages depending on the lookup criteria. Although the data shows the correct formats in the data sheet, when the lookup retrieves the value it only shows the value as number format. Is there a way the cell can retrieve the cell format information as well as the value and apply it to the cell
View 7 Replies View RelatedI am in need of a forumula. I am trying to extract one word before a keyword, one word after a keyword, 2 words before a keyword, and 2 words after a keyword.
For example:
The keyword is blue:
birds are blue today - 1 word before=are; 1 word after=today; 2 words before=birds are; 2 words after=today
blue is my favorite color - 1 word before=(null); 1 word after=is; 2 words before=(null); 2 words after=is my
see blue colors in the sky - 1 word before=see; 1 word after=colors; 2 words before=see; 2 words after=colors in
I have a list of names, and each name belongs to a particular group
I want the group number to auto populate in row 2, so when Andrew adds his name on the bottom of the list, row 2 returns a value of 1, Barry, group 2 and so on.
The list of names and groups will remain the same over time, but the list will be repeated each time that the individual inputs his name.
All information is in one sheet as below
Name
Group
Andrew
1
Barry
2
[Code] ........