I have 2 cells, one has a first name the other has a last name for instance:
A1 = Joe
B1 = Smith
I am trying to combine the first letter of the name in A1 with the next 3 letters in B1 so that C1 = jsmi
I am having troubles finding a row number, using it to specify a range and then counting the cells containg values in that range.
This is what I have
Code:
Private Sub TextBox1_Change()
Dim test1 As String
Dim FoundRom As Range
Dim i as String
Dim abc1 As Range
Dim Core1 As String
test1 = TextBox1.Value
[Code] ........
I am thinking that I have my "Dim"'s wrong or something but I cant figure out how to define the range including the found row number and then count the number of cells containing data within that range...
I am trying to make a puzzle and use excel to verify I have done it correctly.
What I want to do is when I type a letter in a specific cell have it put a number value in another cell automatically. For instance, if I type the letter "a" in cell A1 I would like the number 60 to populate automatically in cell B1. I want to give each letter in the alphabet a numeric value so I can type a message in the A column and then auto sum the results of the numbers in the B column.
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
View 9 Replies View RelatedFor the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.
To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.
Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
I 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 have a column with names, where i need the 1st letters of the Names to be displayed in CAPS. If its a Single letter also, the same should be displayed in CAPS
View 2 Replies View RelatedI was wondering whether it is possible to find a formula in Excel that would extract (using RIGHT formula) surname from Name Surname cell despite surname lenght i.e.:
'John Smith' in one cell would give 'Smith' in the other
'Tom Let' in one cell would give 'Let' in the other
I have tried to combine RIGHT, FIND and PROPER (for upper case) in one formula but it does not work.
how it would be possible to obtain the last two numbers before the letter B, if "Fri Aug 01 16:40:49 BST 2008" is placed in A1 so "49" would be placed in B1,
View 9 Replies View RelatedI want to put the letter A in cell a1(or any cell in column A, SHEET1) of SHEET 1 and get the information from SHEET 2, column A. Put this information in column B sheet 1. I will have about 40 columns in sheet 2. I used a if formula, but only 8 ifs can be used.
View 9 Replies View RelatedI need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.
So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter
Is there away to Combine a Text Entree in a Formula with a Formula
=IF('VEGAS - Main'!Z9=1,"MIXED DBLs","")
I am trying to do something like this:
=IF('VEGAS - Main'!Z9=1,"TEAM EVENT"+'Mixed Doubles'!P9
Result will be MIXED DBLs 1st Place
I'm trying to set a data validation in B1 from values in A1:A5.
A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes
What I want is show in dropdown list the words in singular (without the "s" at the end).
The formula below works if I introduce it in a normal cell,
[Code]....
or as array formula with CRTL+SHIFT+ENTER in this way
[Code] .....
But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.
Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?
View 3 Replies View RelatedI just want to show a letter and change it to any letter and show it again in seconds. Can it be done with formula, maybe with RAND formula or any other formula.
for example when i press F9 the letter A change B, and then i press F9, it changed to C, and then when i press F9 and it changed to A again, and i press F9 and it changed to B, and so on...
(Is it possible too..with a little variation if i press F9 continually, it changed every 5 second?)
I have the following letters in cells
D2= A
D3=G
D4 =L
These represent Accomodation,Groceries, and Liquor.
Starting at E12 I want a formula , to drag down the column, which will show either Accomodation, Groceries or Liquor depending on the letter I put in cell D12,
Eventually I will possibly need help to summarise these costs on another sheet with other data such as fuel costs and fuel analysis but this will get me started.
Well following on from my question earlier, I am still playing with my timesheet/rota I'm developing.
I have a start time and an end time, which is formatted as hh:mm then an hours cell which adds them up for you.
BUT if someone is on a rest day or a holiday day or a sick day etc, I would like them to be able to type that in without the cell that adds the hours up going doo laly!!!!! (######)
So, is there anyway they can type say 'R' and then getting the hours total cell to ignore it because it isn't a time?
i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.
View 9 Replies View RelatedI have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
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 RelatedNeed to write a formula that would assign a letter grade (in row W) to the average in row V, based on the school's scale. So, row V contains my averages (in % form) and I want row W to have the letter equivalents.
93+=A
85-92=B
75-84=C
67-74=D
<66=F
I have a formula that computes the row number in B58. I would like to combine the row # in the formula after M_MAN!A1015 for eg M_MAN!"&B58&" instead of having to type this. I can then use the foormula to copy the balance of the formulas
=-SUM(OFFSET(M_MAN!A1015,0,22),OFFSET(M_MAN!A1015,0,23),OFFSET(M_MAN!A1015,0,24))
Workings BC57Row #Quarter5810150Spreadsheet FormulasCell
FormulaB58=ROW(HC_MAJOR_EXP)C58=-SUM(OFFSET(M_MAN!A1015,0,22),OFFSET(M_MAN!A1015,0,23),OFFSET(M_MAN!A1015,0,24))
I want the user to select a currency pair in cell B4, eg, EUR/USD
Then cell R4 will look to see if there is any data in B4 and, if so, return the following value into Cell R4....
=MT4|BID! & cell B4 & "m"
the cell b4 is the bit i am not sure about.
Basically i need to type in a formula in sheet 1 under each of the months that will match the account name and location from B1 and C1 to the data range in sheet 2 (Columns B and C) and return whatever value is listed on sheet 2 under column E. My gut tells me I need to use multiple look ups but I've tried this and got numerous errors.
View 9 Replies View RelatedCombine two lookup/IF formulas:
[Code]......
So if formula 1 conditions aren't met then it by default would result in formula 2 conditions being met.
Formula 1 : =IF(SEARCH({"spinning"},A2),"AUTOGEN")
Formula 2 : =IF(SEARCH({"typing"},A2),"Manual")
I want to combine above two formula's with "OR" function.
I have a problem with a cumulative countif and offset formula that I am unable to solve. See the attached spreadsheet for an example of the dataset I get out from our systems.
I want the formula to be fully automatic, so when I change the month in column K2, it will automatically count all the different outlets in column B based on criteria in column L3 and downwards. The dataset will be updated monthly obviously.
I believe that it could be solved with the usage of a countifs + offset formula or something similar.
The main idea of the spreadsheet is to track the movement of customers between different chains. It is thus important that customers that show up in January will not be counted again in February or March, thus creating an unique countif list based on a given month.
Number of outlets example.xlsx
I have a column say column A that holds text. If the values match say:
ABC1 or
ABC2 or
ABC3
And also match column B if it shows "Yes" then show "Correct" or "Not Correct".
Is there any way to combine the VLOOKUP and IF Statement formula's into one
formula or is there any other kind of formula which does both?