Check If Abbreviation Used In Cell Is Correct Based On Given List?
Apr 24, 2013
I have a list of abbreviations in Sheet 2 and Names of Companies (Full name of Company and short name) in Sheet 1.
What I need to do is check if the abbreviation used in the Short Name Field in Sheet 1 is correct based on the given abbreviation list in Sheet 2.
here is the example:
Sheet 1:
Fulll Name of Company
Short name
AMERIPRISE FINANCIAL, INC.
Ameriprise Fin
Sheet 2: Abbreviation List
Full Version
Abbreviation
Academic
Acad
Bank
Bk
Financial
Finl
in the abbreviation list the correct abbreviation for financial is Finl so the short name should have been "Ameriprise Finl" and NOT "Ameriprise Fin". I'd like the rows highlighted in yellow if it is incorrect.
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
##################### 149 entries Column A - Date - OK Column B - Customer_Phone - Errors (Should be 11 digits) Row 21 - Customer_Phone - Error (Not 11 digits) Row 108 - Customer_Phone - Error (Contains letters) Column C - Outcome - OK Please correct and re-check. #####################
I have the need to create a workbook that will return values based on the entering of an abbreviation. I will use the chemical elements as an example, I wish to have a column formatted so that if 'Au' is entered the cell populates with 'Gold' or if 'Pb' is entered then the cell is populated with 'Lead'
So in my case I have all the abbreviations in one column and all the full descriptions in another (approx 500 different codes/description)
I'm trying to wrtie an IF statement to display one of Q1, Q2, Q3 or Q4 based on the three letter month abbrev. All i have so far is the following, which isn't leading anywhere - and i have a feeling theres an easier way to write it.
I have two problems sort of rolled into one. I have a spreadsheet with a list of skills to perform in cells a2:z2. Under those I put in dates where those skills are performed so a3:a100 , b3:b100 and so on. At the moment I have the these dates populating another sheet with the list of skills going a2:a200 and the dates in b2:b200.
Annoyingly I have been asked set it up so it inputs the other way so my problems are: The skills in the second sheet are in a different order than in the first sheet so its not a straight correlation between the two so I would need to search for the title and match it to the second sheet. Then the second problem is as the will be numerous entries I need to find the next available space in the first sheet. So can you add data at the bottom of a list??
Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.
I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.
Reporting period is shown in two columns Year and Period number (1 to 12).
I would like to know if it is possible to have in a single cell the information about the presence of an error in the List1, because a cell of List1 got a value that is not in List2.
I mean all cells in List1 have to contains the same values of List2. It is like a data validation. But it would be nice to have a single cell with "There is an error in your range".
how I can check if a cell contains part of a value from a list? Do I need a formula or VBA? (Both would suit)
Example:
A1= BLABLA Apple BLALBA A2= BLABLABLA Orange BLA
List in C1: C1= Orange C2= Apple
If it contains one of the word, I need the function to return the word...
What I have tried:
Search formula Match formula VBA with "If...Like" statement
Keeping in mind that the key word Apple/Orange in A1 or A2 can be located anywhere in the "sentence" (could be after BLABLA or BLOBLOBLO or BLU etc...)
This is something I would then apply to a range (I suppose if it requires VBA, then with a loop which I would be able to manage)...
EDIT: Cells in A1/A2 will never contain two different values from the list (it is either Apple or Orange)...
I have a tab with different city names and then a a series of answers to a number of different questions, what I need to do is create a macro which looks a table on a different tab, and if column b matches any of the words in the table to then print that entire row of answers.
So if the information found in R1!M3:M21 is found in Results!B:B then print that row to sheet R1. If the information found in R2!M3:M22 is found in Results!B:B then print to sheet R2 etc.
This is the only other way I could describe it, however I don't think it can be done as a formula anyway (even though it is somewhat gibberish)
IF(ISNUMBER(SEARCH(R1!M3:M21,Results!B:B))print the row,move onto next row)
I have been playing around with this for a bit and now have it sorted out. This is a macro that will check a name list and highlight any cell that contains one or more of the names.
I need to count a list of corporates claims settled as i have multiple Claims Status as outstanding, In process,Cheque prepared and Etc.
Basically i cannot use countif formula for the condition as it has only a range & criteria but here i have to check the list in the validation tab and map with the details in the side spread sheet given which are settled and count them so such criteria is so unknown to me to use .
I have problem with function, I have array with random numbers in cell G1 and columns with X in some of the cells, now I want to check the cells with numbers from cell G1 in column F to see is there X or not if yes count if not do nothing. In attached example result should be 3.
I've inserted the current date into an excel form using the TODAY() function.
I also need to provide the form in French, and when I change the language for the cell with the TODAY() function, it automatically abbreviates the month. For example, today's date appears as "13 nov. 2008" rather than "13 novembre 2008" as I would like it to appear.
I would like to have a UDF to convert any text string to an abbreviation. For example, "Del Puerto Creek at HWY" would be converted to DPCAH. Or "this text string" would be converted to TTS. This has to work on any string of any length in any cell. I guess the UDF would pass each character through a loop and extract the letter immediately after a space and capitalize the letter if it is not already in uppercase. The UDF would have to have the ability to drag using relative references. For example: =AbbrevTxt(A1). It does not matter what the UDF is named.
I have attached sample workbook that has a user form with 6 check boxes and 3 text boxes. The value of each of the text boxes is based on a cell value in Sheet2. I have the visible property of CheckBox5, CheckBox6 and TextBox3 all set to False. What I would like to be able to do each time the user form is opened is have the visible properties of those controls dynamically changed to True only if Sheet2 cell A3 has text entered in it.
Is there a way to set up a "watch" on a cell so that if you type in a different number on a cell, OptionButton1_Click() gets activated? Example:
A9 = 12.0104 OptionButton2 is active.
In Cell A9 you type "25.0508". OPtionButton2 becomes inactive and OptionButton1 becomes active. I already have the buttons linked, i just do not know how to make the button get triggered if A9 changes.
My workbooks have a column of state names that I need to replace the names with abbreviations. I am doing it manually with the Replace (Ctrl+H) right now, and it's very time-consuming since I'm doing it with numerous, large workbooks. I would be very grateful if there's a quicker solution. The state column is always the same (I). The states are United States and Canada, but I could edit if there's an existing solution from a different country, or modify a simliar work-around.
I want to be able to create shortcuts for cells by using abbreviations. i.e If I wanted a cell to come up as "Food Services" all I would have to do is type in "FS" rather then the entire word or continually using copy/paste for the various abbreviations. Is this possible through excel or would it be best to be done through ms access?
I'm new to excel and have had a hang up with the MkDir feature. I would like to check if a folder exists based on a certain cell value, and then create the directory if it does not exist. This is what I have so far.
Dim newFile As String Dim Path As String newFile = Range("D5").Value & " " & "op" & Range("B200").Value & " " & Format$(Date, "yyyy-mm-dd") Path = Range("A210").Value
I am trying to write a formula to figure out Body Mass Indexes for certain age groups and whether or not they fall into a High or Low risk category. So, I am trying to write a formula that does the following. I have 3 columns, Gender, Age and BMI. I need the formula to do the following.
IF Gender = M AND Age >18, <39 AND BMI >7%, <19%, Then return an "L" into 4th column IF Gender = M AND Age >18, <39 AND BMI >19%, Then return an "H" into 4th column IF Gender = M AND Age >40, <59 AND BMI >14%, <23%, Then return an "L" into 4th column..............................
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15 A drop-down list displays the values 1 through 15. Cell A1 changes to 20 Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
I have 3 related dropdown lists that work perfectly, if you enter data from left to right. But, if you click in the cell containing the second list, which is based on the first list, of which nothing has been selected, you can just type any value in the cell. Is there a way to prevent this, or at least validate that what is entered is a value in the list.
I have a table with 3 columns of dates and then a column with Set # that I feel in the box #.
I need to see how many items processed for each set per day.
Example: [url]
The problem is that it counts the correct amount but not with the correct dates. The formula that I use is: =SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
I am looking for a Macro that would randomly select 20 items from my inventory list for performing spot checks. Column F of Active Coil Log tab is where the data would need to pull from, however I would like all data in the row to go along with it. I've explored the RAND functions, but they don't seem to be the right fit .
I need the names of students from Summative tab in the correct box based on how many question they got correct. (Q 27-32, (0-1, below, 2-3, Average, 4-5, Above Average, 6, Excellent Performance. ) I have attached a file.
I have a project that looks at a table of train schedules for a range of dates. The data is formated so that arrivals and departures are on the same row but differnent columns. The dates are to the right and the grid is completed with the number of arrivals for a paticular day. I have created a formula that looks at the data and returns the arrival time for that date. It worked fine until the schedule was altered so that the same train could arrive at 2 different times on 2 different dates. So train 976 arrives at 4a on some days and 5a on others. In the example Ihave posted train 976 has 2 passengers arriving at 5a but the formula shows 4a. Is there a way I can have the formula return the correct time based on the day of the month? I should point out that the same problem could exist for departures.
to print last months results each month. I have a cell (range name = LastMonth) with formula =now()-15 to display last months full name since I will always do this in the first half of each current month.
I want it to look at that cell on the "FY" sheet, see what month is displayed then go to the "Monthly Audit By Cell" sheet & print the correct chart.
Each chart is named "Jan Audit" and so forth
I have this - only first six months done so far. Problem is nothing happens-no printing!
Doing this today on June 14th should print out the May chart.
