I'm trying to write an IF formula that will return a number if the word in the adjacent cell begins with a specific letter. Here's what I want to show:
City
01
Express
02
Overnight
03
So "C" would return 01, "E" would return 02 and "O" would return 03.
I have a sheet that has a list of products in column A. These all begin with either an "H" or a "S" The following columns are sales values/pcm.
I would like to sum each row based on the first letter of coumn A. I have tred using the left function to deternmin the letter but I am not sure how to then sum that row accordingly.
I'm trying to have text in a specific column output a letter, only if every cell in a range of 4 contains the letter Y (yes). Using IF/SUMIF formulae I have managed to get he final cell to output the text, but it does it if ay one of the range of four cells contains the data, not all of them, which Is what is desired.
View 9 Replies View RelatedI'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies View RelatedThe following displays a name and number as the Chart title with VBA...how do I change the code so that is it name plus letter...instead of being Group 1 , Group 2 etc..etc.. it will be Group A , Group B etc..
Code:
.ChartTitle.Text = "Group " & lng + 1
How to translate a specific number into a specific letter related to the number itself.
I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.
So if the letter
B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course
B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course
P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course
P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course
and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.
The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.
and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course
and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.
My excel (2003) is stuck in array mode - is there any way to get it out of Array Mode and put it back in the letter:number display for formulas/functions? It's doing this in VBA too which is totally killing me.
View 2 Replies View RelatedI have a column of references I wish to standardize. Contained within a general text description there is also an order-specific reference number, which is not relevant for my purposes. I wish to find all of these numbers and replace them with nothing (i.e. retain the rest of the description).
The reference numbers are always in the format "P#####/##". Unfortunately these references are in the middle of the text field, not at the start or end, so I can't use a LEFT or RIGHT formula to delete them.
Once these reference numbers have been deleted I will then be able to filter for unique records only. When I do this at the moment the filtering has no effect due to these specific reference numbers.
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies View RelatedI have sheets named exactly the same as the entries of a listbox. Now I want excel to copy the value located in e35 in the worksheet previously chosen.
I tried th efollowing, but this doesn't seem to work. I simply recorded a macro doing what I wanted it to do (orange) but replaced the reference to the sheet with what I thought would be the choice the user made (green)
Do While continue = vbYes
UserFormware.Show
Cells(16, 4) = UserFormware.ListBox1.Text
Cells(18, 4) = UserFormware.TextBox1.Value
Sheets("UserFormware.TextBox1.Value").Select
Range("E35").Select
Selection.Copy
Sheets("Calculator").Select
Range("E35").Select
Selection.Paste
If UserFormware.ListBox1.ListIndex = -1 Then
MsgBox "You must select an item"
End If
Unload UserFormware
continue = MsgBox("Do you want to add another warehouse?", 4)
Loop
Basicly I used conditional formatting and lots of "IF".My solution lacks in style and it's time consuming ..
Long story short: I need to modify the way some cells are displayed based on the selection in a specific dropdown list.
I need that whenI select Task3 from the dropdown list next to "step1: please select" , everything from row 15 to row21 (both 15 & 21 rows included) and from row 23 on, to disappear.
I want to highlight an entire row of data if the text in column A begins with "ABC". I can't seem to get the conditional formatting formula correct. My data goes from column A to column O.
View 3 Replies View RelatedI've got a single column worksheet with a varying numbers of characters in each row.
At the end of each row's cell value I must add a 5 char string. Preceding that string I must have enough spaces to make the total length of each row 106 characters.
I've already:Defined and populated the string. Let's call it "strMyString".Established how many rows are in the sheet and stored it in "lngUsedRange"Written the following which cycles through each row establishing how many spaces need to be added:
Dim lngSpacesNeeded As Long
For i = 1 To lngUsedRange
lngSpacesNeeded = 101 - Len(Range("A" & i))
Next i
Now I just need to know what else to put before "Next i" to locate the end of the existing text in the cell and add the number of spaces in "lngSpacesNeeded" then add "strMyString".
I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.
Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.
I am trying to run a macro once a particular word shows up in the combo box. now in my chase i could try to use VBA code to run the macros such that once the arrow is used in the combobox to select a certain word a particular macro labled the same name as the word chosen would run.
View 5 Replies View RelatedDoes anyone know the VBA line for finding a specific letter in a cell?
eg.
Say cell A1="B"
Say cell C3 contains a word, and I want to know if that word contains the letter shown in in cell A1 (ie. contains the letter B).
I am entering data and want to know how to have the adjacent cell use an If-Then statement. The cell to the left has a FedEx tracking number. If the tracking number in cell D4 begins with an 8, I know the package was sent by OS. If the tracking number begins with any other number, it was sent by BS. How to create a formula in cell E4 that would have OS entered if the tracking number begins with an 8, and then BS if it begins with anything else?
View 2 Replies View RelatedI have recorded some script in excel to import a *.dat file, delete the first 19 rows of data, then export the data as a *.csv file.
My problem is I have circa 30 *.dat files to import, all of which have large file names and I am fed up with copying and pasting them in.
So my question is, in the below code, can I somehow use a reference at the start such as:
abc1.dat = "1"
abc2.dat = "2"
Then where the files were used, reference the "1".dat and its exported version "1".csv for the abc1.dat case.
ColA contains Purchase Order (PO) numbers and I want to return the department number or project number (which is contained within the PO) in ColB eg.
A B
PO # Department/Project #
AP001234P1234 Want to return everything before the 'P'
PR004444P4444 Same as above
TR2008-1234 No need to change
6501P1234 Want to return everything before the 'P'
I was using IF function but there are too many variables then I thought about the 'P' but not sure how to go about it?
I've done the following:
Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub
However, this doesn't even work.
When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...
I use the code below to enter a value from a list box in a cell on a workssheet. Is it possible to code VBA to enter a number for the position of the selection in the listbox to a cell in a worksheet rather that the actual value from the list box. For example if my list is:
Option1
Option2
Option3
And I click on Option2 in the list, I can sennd the value 2 to a cell on the worksheet rather that the value "Option2' from the list.
Private Sub ListBox1_Click()
Sheets("SA").Range("SA_Poistion_To_Archive_A_New").Value = ListBox1
End Sub
I type random numbers into column B
I want cell E10 to read column B then display (number 26 for example) how many times number 26 appears in column B
I have a picture on sheet 2. When I enter the text "htfo" without the quotes into cells a1 on sheet 1 , i would like the picture to be displayed for 5 seconds and then exit the picture and return me to sheet 1.
View 2 Replies View RelatedI am using the follow Array formula to display the value selected in an Autofilter:
{=LOOKUP(REPT("z",25),IF(SUBTOTAL(3,OFFSET($A$4:$A$585,ROW($A$4:$A$585)-MIN(ROW($A$4:$A$585)),,1)),$A$4:$A$585))}
The flaw in this formula, is that it displays a result even when no value has be chosen. Is there a way to display a value only once it has been chosen in the Autofilter?
Is there a way that I can click on a link in a worksheet that is linked to a data list in in another sheet and display the results in a text box on the original sheet containing the link.
For example ...
I have 3 sheets (Master,Detail1,Details2) in my workbook. In the master sheet I w'll have customer names. In the detail1 & 2 sheet2 I will have their details.
When I click on the customer name from my Master sheet it will go to corresponding sheet. I have provided link for that and it's working fine.
But my requirement is when I click the customer from Master sheet, I want to get into the detiled sheet as well as want to display the records only related to the custmor I clicked/selected in my master sheet.
I am trying to create a form where a user can click a button and add a document to a workbook. The file(s) can be hidden on a seperate sheet, but ultimately should be displayed in a List Box or Combo Box. Not sure which would be better in this case. Selecting the item from the list would open the document (either through a double click functionality or a seperate button). I have the beginning of the code below.
Private Sub AddFile_Click()
Dim vFile As Variant
vFile = Application. GetOpenFilename("All Files,*.*", Title:=" Find file to insert")
If LCase(vFile) = "false" Then Exit Sub
Sheets("RefrenceSheet"). OLEObjects.Add Filename:=vFile, Link:=False, DisplayAsIcon:=False, IconLabel:=vFile
End Sub
I have a list of names and I need to know how many names are greater than 6 characters in length. What is the formula I need to enter?
View 9 Replies View RelatedI am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)
In the A column, it looks like this:
CV42 6AQ
FC45 D4D
West yorkshire
PR42 6RD
Etc.
i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.
I WANT TO INSERT A LETTER IN FRONT OF A NUMBERS THAT ALREADY EXIST IN CELLS
IN A COLUMN. SORT OF LIKE USING "FIND & REPLACE" EXCEPT THAT I DON'T HAVE
ANYTHING TO REPLACE; I JUST WANT TO INSERT A LETTER PREFIX IN FRONT OF
NUMBERS.