I would be loading data into a spreadsheet, and when there is a phone number I'd like to have it populate the state field. (ex. (212) 123-1234 in the phone field, would populate "NY" in the state field, is this possible? If so is there any brief way to describe how to do so without taking too much of your time?
Basically I have a giant list (50,000+) of area codes (like 312, 216, 517, etc...) and I would like to make a new column right next to that, which takes those area codes and transcribes them into states so it would be (IL, OH, MI, etc...)
I have a table on the 2nd page of my excel workbook which has every single area code listed in column A, and in column B it has all the state symbols. I got it from here: .....
Basically I have a giant list (50,000+) of area codes (like 312, 216, 517, etc...) and I would like to make a new column right next to that, which takes those area codes and transcribes them into states so it would be (IL, OH, MI, etc...)
I have a table on the 2nd page of my excel workbook which has every single area code listed in column A, and in column B it has all the state symbols. I got it from here: [url]
How would I go about making this new column on page 1 of my workbook which takes all the area codes I have and makes a brand new list of the states by looking up the area code/state table I have on page 2 of the workbook?
I have a set of data (Data tab) that lists name, a high number, a low number, and the state that is associated with the name and range between the high and low number (see attachment). I am trying to figure out a formula that will return the state value based on a match of the name and the number between the high and low. I am trying to use Index/Match but i cant figure out the "between the high and low" portion.
tester.xlsx
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 RelatedI 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]....
I have a worksheet with various data autofiltered. I know when I filter on one of the fields, the drop down arrow becomes blue. If I filter on one or more fields, finding which fields I have autofiltered can become hard to find.
This is my question -- Can I put a button or some type of one touch command were I can take those autofilters off and return the worksheet backs to its original state before I autofiltered?
I have a button in my worksheet. When clicked on I want to change the text in this button
so far so good. I have however a problem how to return to the cell that was active, the moment I clicked the button. so far I have the following code
I have a column of data in a hierarchy (columnA).
As per the picture posted: if I type the store in cell D3, I want a formula in E3 to return the area that the store belongs in.
Making a macro that selects active cells (so anything with text in) and sets that as the print area.
Using the default builder I have:
Range("A1:AA44").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=30
Range(Selection, Selection.End(xlUp)).Select
Range("A1:AA692").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$692"
[Code]...
Can trim this code with something
Range("any containing data").Select
ActiveSheet.PageSetup.PrintArea = "selection"
I have 2 codes that set up different "print areas". Every time i run one of the codes the print area doesn't get set unless i run the same code again. So if i run code A which will set the print area with no issues and then right afterwards i run code B then code B's print area won't get set unless i run code B a 2nd time. And if i now i run code A, the same issue happens and the print area to Code A won't get set properly unless i run the code again.
Here is part of the code that discusses the print area:
[Code] .....
I have some code to select a print area depending on wether or not there is data in a cell. The code I have so far partially works but not quite like I need it to. Here's what I have,
Sub selectprintarea()
Select Case True
Case Range("C5").Value > 0
Range("A1:M47").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$M$47"
Case Range("Q5").Value > 0
Range("A1:AA47").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$47"
End Select
End Sub
It will select the print range if there is data in C5 but if there is data in C5 and Q5 it only selects the first print area. I know I need an "If - Else" or something of that nature but I cant figure that out. Also I need it to look at other worksheets not just the active one. I've tried a replacing the ActiveSheet with Worksheets (Sheet1). but that gives me an error.
I have a sheet in which I want to limit the scrolling area to B2:B60 & D2:H60, in other words I don't want to include neither A2:A60 nor C2:C60 in my scrolling area.
Is there a way to do this?
I know I can lock the cells, however, pre-existing code will not work if cells are locked.
I am having trouble setting print area on rows that can vary from 100 to 6000 rows. This code works good except that the print area cuts off the last row of data every time. "AreaBegin"=row 6 and "AreaEnd" = to last row after a set number of rows have been inserted based on a counter.
Basically how do I get this code to capture the last row for print area?
Sub SetArea()
ActiveSheet.PageSetup.PrintArea = Range("AreaBegin", Range("AreaEnd").End(xlUp)).Address
I wish the print area to be set based on the number entered into cell "D58". My code so far is:
Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NumPages As Range
Set NumPages = Range("D48")
Worksheets("Sheet6").PageSetup.PrintArea = Worksheets("Sheet6").Range(0, 0).Resize(NumPages * 21, 47)
End Sub
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints !
Is this possible?
FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells.
I used this code from a post on a similar topic, but excel is complaining code
in bold
Private Sub Print_Area_Click()
Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
End Sub
I have an excel sheet that I am importing to my network software. One of the columns lists telephone numbers, but they are as follows: aaa-bbbb
I need a way to enter an area code (the same area code) for all of the numbers in the column without manually entering them (the program I am importing this list to will not work without 9-digit telephone numbers).
I'm trying to remove all the numbers of a phone number an keep the are code for 2000+ phone numbers.
ex. 111-222-9999
I want to remove the 2's and 9's but keep the 1's.
I have a worksheet that serves as a navigation page for the workbook and use the following code to restrict from scrolling off the viewable content. The problem is that the code doesn't kick in until either a click if the mouse or keyboard input. This initially leaves the worksheet open for scrolling until input is received.
How do I make this code activate when the wb is loaded? Code in the Thisworkbook module already makes the menu the visible sheet, and code limiting the scroll area is in the sheet module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.ScrollArea = "A1:L11"
End With
Range("A1").Activate
End Sub
I've also tried using "Range("A1").Select", but it doesn't work either. How can I get this to work correctly?
How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time
View 7 Replies View RelatedUsing Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction
View 2 Replies View RelatedI am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
I have a workbook with multiple sheets that I need to print.
Is there a way to create a fixed print area within which you can mess around with formatting without extending or shrinking the print area?
I have tried adjusting the margins settings so that they are all the same, but this does nothing to keep a fixed print area.
with creating vb code that will select certain rows in a spreadsheet, set the print area to those rows and print them out, and then reset the print area back to default.
The data is all on one sheet, but is split into 5 different areas, each below the next, with the column headers at the top of each.
e.g.
header_____header_____header
data_______data_______data
data_______data_______data
data_______data_______data
header_____header_____header
data_______data_______data
data_______data_______data
data_______data_______data
and so on. At the press of a button it'll set the print area to the first section and print (the section includes the headers as well as the data). Then at the press of another button it'll set the print area to the next section. There could be any amount of rows of data in each section so it needs to cope with that.
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 RelatedI need to get the state (xlon or xloff) of check boxes on a worksheet. i have tried the following but unable to get it to work.
View 3 Replies View RelatedI have a spreadsheet that is used for sales territories. In column A, I have customer names, in column B, I have the State abbrev., In column C I would like to return as salesperson's name based on the State.
Aco IL John Smith
ABco OH Jane Doe
Cco WI John Smith
Czco IL John Smith
Dco WY Adam Scott
Fco UT Adam Scott
FDco CT Bill Jones
etc WI John Smith
So I need a formula to enter in column C, that will return the correct salesperson based on the state in column B. (when auto filled down)
For example: IL,WI,MN,IA,ND,SD = John Smith
OH,PA,KY,MI, = Jane Doe
etc
I've got a list of numbers between 0 and 1
eg.
0.25
0.59
0.73
0.15
0.99
in column A
if I want column B to state "low (0.00-0.33), medium (0.34-0.67) or high(0.68-1.00)" for a corresponding value in column A, how would I do that?
Our state carries a 4% sales tax on all items except food and prescriptions.
Our county carries a 3% sales tax on everything.
Attached on my work sheet:
Column "C" determines if an item is either food or non-food.
"G5" is the subtotal of column G
"G4" is the S/tx on "G5" at 3%
"G3" is the S/tx on "G5" at 4%.
"G2" is the gross pay out.
My question is:
I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C".
If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4" If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".
to automate a process that split name, address, city, state, and zip.
here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN....