State And Country Check Macro
Jun 17, 2009
1 - If any cells (with data in it) in the "state" column contain any of the following abbreviations in it...
AB, BC, MB, PE, NB, NL, NS, NT, NU, ON, QC, SK, YT
Then all matching row cells in the column named "country" should say "Canada",
AND....(it must also meet this criteria)...
Criteria 2 - If any text in "Country" column is "Kryponite", AND "State" column is blank
If BOTH criteria 1&2 are met.....THEN...
then delete both the country & state columns
If not, keep both columns and highlight in light red any errors to this macro.
View 14 Replies
ADVERTISEMENT
Apr 15, 2008
format a cell depending on the state of a check(tick) box
View 3 Replies
View Related
Apr 2, 2009
I've got a worksheet with five columns of data. One of the columns contains country codes. I have a list of countries that the codes relate to. I would like a new column in the worksheet that contains the country name. I'm not sure if this is possible, but it seem that most things are with VBA! The countries and their codes are on 'Sheet2' I relalise I might have to do some boring re-ordering of the data on 'Sheet2'
View 2 Replies
View Related
Jun 3, 2012
I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.
View 7 Replies
View Related
Jun 8, 2014
I have a dropdownmanu in sheet1 with different countries taken from Column A in Sheet3. I need a macro to run when i select a country example Denmark. It will fill out transmittal code and also country code in named cells for it in sheet1. Info taken from Sheet3
Transmittal code is in below testsheet in Sheet1 cell E12.
Country code is in Sheet1 cell.
But this have to be possible to change. Also the range for the country have to be possible to change.
This vba code i need to run as soon as i select a country in the dropdown manu.
But one thing i would like to solve also is. When i select a country it will create a dropdownlist in I13 taken from the info in column, i have in Sheet3 column F. So if i select example Denmark, it will show a dropdown menu in sheet1 I13, with the ledger codes 10 and 6x. I have tried to make this work but cant make it work good.
I use excel 2003. Please have a look and upload the testsheet back.
View 14 Replies
View Related
Mar 12, 2008
I have a checkbox and a command button on my sheet. I want that, depending if the checkbox is marked or not, I can call two different macros. I don't know if this is possible at all or maybe can be done easier otheriwise, but this would more or less be my idea of what should happen:
Private Sub CommandButton1_Click ()
' If CheckBox1 = marked
Call MacroA
'Else (CheckBox1 = unmarked)
Call MacroB
' End If
End Sub
View 4 Replies
View Related
May 10, 2007
I am looking to find a way and synthesize code that will prompt the user for text data when he/she clicks a radio button, then place that text data in a "Notes" worksheet within the same workbook. Given that there are multiple radio buttons, with options of "OK" and "attention needed" (it is a checklist of maintenance items), I also need to find a way to have each note listed sequentially in the notes worksheet. My questions: Is this possible using macros? Can you steer me in the right direction? (change event, inputbox, then what?)
View 3 Replies
View Related
Feb 26, 2014
I am trying to create a macro, that ultimately does the reverse of one written a while back.
I have created a macro that exports "Roster_Data" to "envision_Roster" in .csv style formatting. What I want to achieve is reverse engineer the macro to return the data back to a similar state.
I am trying to transpose column D into rows that correspond with dates in column c in a sheet called "OutputView" this in essence is similar if not exactly the same as the original worksheet "Roster_Data"
I have split the macro I am working on into three separate modules.
Module 1 - Initial Export of "Roster_Data" to "envision_Roster"
Module 2 - Format and output worksheet to find MAX date and MIN date and produce top rows of data
Module 3 - Analyse, Undertake Logical Tests of data, and transpose to suit (Replicate the initial "Roster_Data" view)
I'm having trouble visioning this altogether so I've been starting with basic code to output basic stuff, but I still can't relate this back to my data.
[Code]....
View 2 Replies
View Related
Jul 17, 2014
I have on sheet1 a number (72 at the moment) of Form CheckBoxes.
In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)
Then, go through and Check All checkboxes
Call MyMacro
Once MyMacro is complete (Filtering & Printing)
Revert the checkboxes to their original state.
The purpose of the checkboxes:
When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden
Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.
View 11 Replies
View Related
Nov 27, 2009
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts
8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"
these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
View 9 Replies
View Related
Jul 28, 2006
I have numerous hyperlinks in cells on a worksheet, next to these I have checkboxes. I am looking for a macro that will check if the boxes are checked and if not ignore them and if so run a certain macro.
View 6 Replies
View Related
May 6, 2014
i am trying to find country name in cell consisting of sting, i applied "=INDEX(K2:X2,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(K2:X2,A2)),0),0))" this formula i am getting desired result but i am not able to apply ctrl+d since all cell reference are iterating.
View 8 Replies
View Related
Mar 24, 2014
I have a very big Excel table with many products. A product like a "pen" can have different attributes, then each of it gets an own row. Each row has one entry in the column "country". It indicates in which country the product gets shipped.
I want to count the country, in which the products get shipped.
But I don't want to count two times if a product "pen - blue" and "pen - yellow" both get shipped to the US.
I tried it to achieve this with a pivot table but it counts every row and I haven't found a way that it only counts each country per product once.
I have an simpliefied example attached. The columns "product" and "country" exist already, the column "country count" I hope to get automatically.!
Country
Product
Country count
Attribute
US
Pen
2
Blue
[Code] .......
View 6 Replies
View Related
Mar 16, 2007
I have countries in column A and the values in column B.
I want to see the avareges of values for each country.
I have tried a few samples that I have found in the forum but they do not work for me.
View 9 Replies
View Related
Feb 23, 2009
We get a very detailed Call Data Records(CDR) for all telephone calls made by our employees. CDR typically contains the called number, duration in seconds and cost. What is missing it the city name and country name for the called number.
For e.g. a typical CDR log would be
Called # duration(secs) Cost
442075000000 40 0.02
From looking at this number, the country code is UK - 44 and the city code is 20 which is London.
I have a spreadsheet that contains Country code and city code for all major cities in the world.
Is it possible to create a lookup function, that would take the 'Called #' from the CDR and lookup the data in the CountryCity code spreadsheet and give me the called City and Contry name.
View 9 Replies
View Related
Aug 5, 2014
Data validation using named ranges as countries are listed multiple times and to relate country with cities.
How can i relate country with city in two different columns(named ranges) not in order.
Formula to be used for Data Validation would be useful.
Sample data file is attached.
Its just a sample.data can be for 250 rows.
Book1.xlsx‎
View 2 Replies
View Related
Dec 6, 2013
Find attached Sample sheet : Sample Data.xlsx‎
View 2 Replies
View Related
Mar 23, 2014
I have 2 excel sheets format is below mention. In sheet one i have some number with ISD code and in second sheet i have country name and Code. now i want to match Country name in sheet one but my problem is that there are some code are in 1 digit and some code are in 2,3,4,5 digits. If you see in example 220 is belong to Gambia and 224 belong to Guinea so first i want to match all code who is having 1 digit code second who is having 2 digit than 3 digit, 4 digit respectively.
If I'm using below mention formula it's give me first value from sheet to:
INDEX('Country Code'!C2:C229,MATCH(TRUE,INDEX(ISNUMBER(FIND('Country Code'!$B$2:$B$228,B4)),0),0))
As per Ist number 54911678222709 Formula will check Ist 5491 digit if it is Match than ok else chek 549 if it's match than ok else check 54 and 5.
Sheet One:-
Caller Number
54911678222709
5493426121222
971501364886
9715013648860
2207771650
22430919600
23190000368
23222273217
Sheet two as Country Code:-
Country CodeName
54Argentina
971Emiratos Arabes Unidos
220Gambia
224Guinea
231Liberia
232Sierra Leona
View 4 Replies
View Related
Apr 11, 2013
how to add county flags to excel table.I have a table with list of countries and I would like to add picture of their flags into the same cell or to the neigboring cell.
View 1 Replies
View Related
Jan 3, 2014
I have a master data sheet with raw data in 19 columns and a variable amount of rows per week, but usually 50k+
The data contains entries from different Countries, and this I need to split up per country into different sheets.
Naming the different sheets with Country code like US, DK, UK, PL, etc...
Below code does that almost perfectly.
Only thing is that when there is no more data to split up, it creates the last sheet called "SheetXX" and since it can't name the sheet "nothing", I just get an error - Run-time error 1004 Method 'Name' of object'_worksheet' failed with the possibility to END or DEBUG. When going into DEBUG this line is highlighted. (wsNew.Name = c.Value)
I can just hit END and then proceed running my remaining macros, but I would prefer it not giving me an error in the first place. (My end goal is that all my macros (10ea) are run by the click of one button, and that is currently not possible, when this gives me an error).
I have tried the "Application.DisplayAlerts = False" without any luck, and also "On Error resume Next", maybe I have used it incorrectly. Do I have to search for a different way to split the data..??
VB:
Sub Split_data_pr_Country()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = sheets("Template")
[Code] .....
View 9 Replies
View Related
Jun 29, 2014
I have a list of reports for many countries, I need to make sure each report is there for every country, except for weekend days.
report check.xlsx
View 1 Replies
View Related
Apr 18, 2013
I have a Userform that allows the user to input a country from a combobox which after clicking a button writes that data into Cell AH11 in the excel spreadsheet, what I need is for Cell AI11 to read what has been entered into Cell AH11, look up whether or not that country is on a list I have and enter a yes or a no if it is or it isn't on that list.
View 3 Replies
View Related
Dec 5, 2008
I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.
I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.
View 9 Replies
View Related
Dec 5, 2008
I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.
I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.
View 9 Replies
View Related
Feb 26, 2008
I am struggeling to format a column to display currency values formated with a different currency mark than the local system currency. Right now, when I run the macro on my machine I get my local currency (Israely Shekel), and on the Client's machine I get Euro. (I need it to be in dollars)
View 6 Replies
View Related
Mar 7, 2013
I have a column which contains a list of countries and the next column is filled with Yes and No's.
Is there a formula which calculates how many Yes a country receives?
e.g. USA
Yes
Germany
No
France
No
[Code] ..........
View 2 Replies
View Related
Dec 21, 2008
I have some data for international dialed numbers, I need to translate this into a country or destination name.
for example if the dialled number begins with 001, the is the united states etc..
I have all of the required codes, but they all differ in length.
ie 0033 is spain but 003356 is a spain mobile.
I was thinking that i could use VLOOKUP to find the relevant text to display, but this does not work with the varying lengths of the codes.
View 9 Replies
View Related
May 14, 2014
Basically, what I'm dealing with is an inventory system. When a cell value reaches below a certain quantity, we have a cell light up with the word "YES" (under the "re-order" column). What I'd like to do is have a macro that checks to see if that cell is populated with "YES" and if it is, to run another macro (which I already have written) that sends out an email notifying us of the need to re-order.
How it would work: The person pulling out the inventory would fill out this form and click "Submit"
submitbutton.jpg
I would then have a "call" code tied to the "Submit" button that when clicked, would run the macro to check cell population and send out the email. The email code is already written and works flawlessly on it's own. I just cant seem to figure out how to write a macro that checks for cell value and then runs it (or not) based on that.
View 14 Replies
View Related
May 22, 2013
I'm having a problem with getting a result from another workbook. The purpose is to extract information from a closed workbook representing data from a country (ie US.xlsm, UK.xlsm) into the open one (summary). I want to count the number of results in a column which are above a certain amount (in this case 100).
So this is my coding for getting this result
=SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))
Which works no problem for getting the number of results in the E column above 100. my issue is that I want to be able to change the name of the country in a cell, and then get the results automatically from the relevant workbook. I've been using CONCATENATE for these purposes and EVAL (from MOREFUNC)... and I get #REF!
So if I split up the above coding into two cells "=" and "SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))" (just to keep it simple) and then use CONCATENATE to combine them in a cell. Using EVAL on that cell gives me #REF!
I've tested SUMPRODUCT with EVAL and CONCATENATE on formulae like the one above which do not link to other workbooks, and they seem fine. and SUMPRODUCT and linking to a workbook also works fine in the first example.
So the problem is somehow coming from the combination of SUMPRODUCT, EVAL and accessing an external sheet.
View 4 Replies
View Related
Jul 8, 2009
Depending on the text entered into one cell, I'd like another cell to state n/a. For example, if cell a2 shows the text of cash, then cell b2 would show n/a.
View 3 Replies
View Related
