i have all the states listed in A1 and i would like to seperate them into east and west.
example if a1 have FL and want a52 to say East and if i put CA it would say West.
i have 4 areas north east south and west, what is the forumla to enter into a cell that would automatically add the Salesperson when you know the area
Table is
Date, Area, (FORMULA), Amount
13/11/09, North, ( ), Amount
Basically there is a table with the salesperson per area
if i was writing it i would say its as follows
If Area = North then Mr Smith else if Area - South then Mr Jones etc etc
Attached file where i m not able to split data in seperate seperate coloumn
View 3 Replies View RelatedI had a worksheet with Employee name,Place,State & Amount Columns. I want the data in the MasterSheet to be copied into Individual Sheets based on Place & State. All The rows should be copied into one sheet based on the State of the respective Place one after the other. For Excel to Know which Place belongs to which State I had created the sheet match List.
The Data is dynamic.Each Day the data may grow or shrink depending upon the transactions at that place.I had shown how the data should be copied into individual sheets.
I currently have an issue with some of my user defined functions. These functions work perfect for everyone here at my company in the USA / UK. The problem happens when we send it to our client who is overseas.
The client opens up the file and all they see are #VALUE! errors where our UDF's are being used. But when they send it back to us the file works properly.
I have currently added VBA code so that when the workbook and the user enables macro's the file will do an application.calculatefull. This still does not seem to work for them.
The excel version that I am running currently on my PC is MS Office 2003 Pro (11.8146.8132) SP2. The version that the client is running on their PC is MS Office 2003 Pro (11.8105.8107) SP2. Could this problem occur because of updates that they have not yet installed? I have been working on this problem for about a week now and have not come up with a solution.
Can someone please build me a quick VB code?
Below are states which are located in column "I". I need a VB code that will cut any entire row that has any of the following states below into a new collective workbook - if any of the states don't exsist then to just ignore and move on. They all have to be in the same workbook in the end.
If NONE of these states exsist, please just insert a msgbox ("No states to move")
(I basically need to divide West coast and East coast by removing all WC states and put them in their own workbook)
WA
OR
CA
MT
ID
UT
AZ
WY
CO
NM
OK
TX
I am trying to figure out the excel formula I need for a project. Basically i am try to do a lookup for Country/State. What I have is a column called Country and once called State on the first worksheet on the second I need to do the calculations that will allow user to select Country and the when they click on the State it shows only the related States based on the Country which are loaded on the second worksheet.
(EG.
COUNTRY = United States - will return/diplay in dropdown
STATE = New York
STATE = New Jersey
etc.
Yahoo IM: rmdyjoe
I'm trying to make a little dynamic map of the United States where each of the states change color based on a particular cell value that corresponds to that state. I've got the map of the United States in Excel and ungrouped so I have a collection of seperate shapes - one for each of the lower 48 States. I've named each shape (state) by it's postal abbreviation: TX, CA, NY, etc.
Now I'm trying to write a macro that will COLOR the state according to a numeric value in the cell next to the cell containing the State abbreviation. These are transit days to this state that I will calculate in another location. Like this:
TX = 3 days
CA = 1 days
OR = 1 days
NY = 7 days
KS = 3 days
FL = 7 days
Calculation Order. As the title states.
View 2 Replies View RelatedI have been working on creating a dependent drop down list and have been able to create lists (Insert > Name > Define) and it has worked for all the lists except 2. It will not allow me to create a list titled C or R. I get a pop up stating "that is not a valid name". Why is that?
I have a list for each letter of the alphabet except the letters C & R. No matter what I seem to do I just keep getting the pop up window stating that it is not a valid name. I also tried closing the workbook and restarting the pc and then trying but the same thing keeps happening.Thanks!
I want to sort all of the "column2" by alphabetical order. The order will be first by state and then by city. For example the first box the column2 order should be (Fountain Hills, AZ) properties first followed by (Mesa, AZ) properties second followed by (Peoria, AZ) etc.... ending with (Flower Mound, TX) last.
The issue that I'm having is that when I create a custom sort the comma after the city indicates a new value being AZ. For example my custom sorting looks like this: Fountain Hills, AZ, Mesa, AZ, Peoria, AZ. This is limiting my ability to sort the files as is with city, state. I solved this issue by removing the commas after all the cities. This worked but was time consuming. Is there a faster way? The workbook is below.
Development Projects Mr Excel.xlsx
I am trying to insert custom formatting into a cell so when I enter a set of 9 numbers for phone numbers in the U.S. and 14 numbers for international phone numbers, it will automatically display it in the correct format. Reason for the 0's on the international is because country codes often start with a 0, so I need to ensure it isn't removed from the number.
View 4 Replies View RelatedI'm trying to get a VBA macro put together that would delete all the rows, throughout every sheet, and shift up wherever the cell in column A = "Delete Row" in a workbook I've made. In the below copy of the workbook you can see that a lot of the information from all the tables is linked to the topmost table on the 'Mix Detail Sheet'.
If the the Cell in column A is blank on that first table, its corresponding cells in tables throughout the workbook displays the words "Delete Row". I need the macro to delete the rows and shift everything up where this occurs.
Ideally I'd also like the macro to then delete the rows in that first table where the column A cells are blank.
column I row 11 has a functional argument that simply states to display the output
as .843 I need to edit it to .844 and I cannot seem to find out where or howto edit it.
There are others like this that I need to do too so I need to learn how to do it. Not just have someone do it for me.
ALSO as you can see this sheet displays #N/A all over the filled in cells... I would like them to be blank until I enter some pertinent info... I tried this is cell m7 BUT as you can see in m12 it goes back to the N/A
I am trying to make up a workbook for a friend for their daily food intake. The formulas were working fine but she was hoping to be able to just enter the food and have the rest of the information automatically entered.
I was having no problem with it using a vlookup formula for the foods and a data validation drop down menu but when it comes to the final formula I am getting a #VALUE! error in that column unless there is a food listed.
I have attached to workbook. If you notice in the Sunday Tab I have the problem but Monday is fine since I have not added the vlookup formula. Is there a way to have it not show anything like in Monday when there is nothing entered?
i know by the formula =combin(49,6) how many combination of 6 numbers there are from a available 49 numbers (13983816).
how do i manage to get all combinations listed in a excel spreadsheet? I would want the numbers in individual cells. For example, Row 1 would show 6 numbers individually from A1 - F1.
I do realise that excel does not have enough rows to do this so may have to continue on other sheets.
I have a list of jobs names on one sheet and there are certain ones that i would like to put on a list on a second sheet.
what I would like to do is put a "1" in a cell next to the job names that I want on the other sheet and have them automatically listed.
I need a macro to find for example
Bigg, Kelly
Bigg, Kelly
Bigg, Kelly (find the last name listed in the column) then move over (0,2) and copy - paste into a different spreadsheet.
Where I am mainly stuck is it locating the last name listed in the file - if the name is listed multiple times.
I do have one issue I need to work on with a VLOOKUP formula.
I have a list of cities and counties that the VLOOKUP goes and looks at to pull information from.
The problem is that when there is a city and county with the same name (Example: Boulder and Boulder County) VLOOKUP always seems to jsut grab the one that is listed first in the list.
When you use the city name of Boulder, VLOOKUP always retruns the information for Boulder County.
Here is my forumula:
=VLOOKUP(qtProjAddress3,'City Mileage and Tax Rates'!A2:C518,2)
Is there something I can add to this formula that will allow it to return the proper city or county when the names are similar?
Private Sub Workbook_Open()
MsgBox "Insert Text Here" , vbOKCancel
End Sub
What I would like to do is have a MsgBox open up that states this file is confidential and you click 'I Agree' button, or a cancel "I Decline"
I do not want the spreadsheet to be visible until they click on 'I Agree' button, and then if they click cancel, the Excel closes.
This is not a huge security issue, just a reminder for the Top Brass not to email out the file. This is for looks and show only
I have a spreadsheet which is used for tracking work items for a team of people. On one sheet I have a column of dates and rows of named 'tasks' against them. I need to identify the first and last dates at which a 'task' is listed.
My spreadsheet is attached.
On sheet2 K2 I want to see the first / earliest date where 'Fish' occurs on sheet1
On sheet2 L2 I want to see the last / latest date where 'Fish' occurs on sheet1
I have tried using SUMPRODUCT MAX / MIN but I got myself in a muddle!!
TrackerRH.xlsx
I am looking to create a formula to get the initials of a name, where the the last name is listed first, followed by a comma.
Now, the names could be full names or initials.
So, it could be something like: Smith, John R - result would be JRSSmith, John Robert - result would be JRSSmith, John Robert Michael - result would be JRMS There were similar formulas that I found here, but these only got the last letter of the last name - in this case the following formulas would have resulted (with the above examples) in: JTS or the last example with Michael, JLS.
=UPPER(MID(A1,FIND(" ",SUBSTITUTE(A1," ",REPT(" ",2)))+1,1)&RIGHT(A1)&LEFT(A1))
=UPPER(MID(A1,FIND(" ",A1)+1,1)&RIGHT(A1)&LEFT(A1))
Note: While I am using Excel 2010, there could be users that are still using 2003.
The current method I'm using to get the results I need in Excel are a bit manual and excessive. is a simple all in one formula for what I'm trying to do,
The data is 3 rows. We can also assume the data is in the first three columns starting from A1.
698
885
499
As you can see in the above set from 0 through 9, the numbers that are not listed are 0,1,2,3,7
And As you can see the numbers that are listed are 4,5,6,8,9.
How do I find and show which numbers from 0 - 9 are not in the set.
How do I show the number that are listed in the set Example:
Note: the special characters aren't needed.
698 4,5,6,8,9 --- 0,1,2,3,7
885
499
Note the the evaluation is for every three rows. So the three rows in the example are from a table with 150 rows. Each row begins a new evaluation for the next three.....Example:
698 and the following two starts the next set
885 and the following two starts the next set
499 and the following two starts the next set
and so on ......
I need a code that will copy a list in the same order from different sheets and will paste special and transpose it on the summary sheet.
View 1 Replies View RelatedI am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.
Excel File as follows:
Planned Delivery Time Planned Order Day Planned Delivery Day
8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)
13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)
I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.
I have a project I need to get done, and I've done most of it. But, there are a few things in VBA I'm unfamiliar with, and I was wondering if I could send you the file so you could take a look at it, and see if you're able to do it. This involves VBA. Honestly shouldn't be too too difficult, but something beyond what I can do. I have the specifications listed already, and I am ready to send it to you. Anyone feel comfortable taking a look at it?
View 9 Replies View RelatedI am attempting to create a macro that will loop through a range, each cell of which contains a worksheet name which needs to be activated in turn. My limited knowledge says that either of the two techniques below should work, but they do not.
Sub SelectSheet()
For i = 2 To 50
Sheets(Range(("J" & i))).Select
Next
End Sub
OR
Sub SelectSheetv2()
For i = 2 To 50
Sheets((Cells(i, 10))).Select
Next
End Sub
I have a Combobox with it's 'RowSource' set to two columns x 1500 rows on a spreadsheet. The Combobox is set to 'fmMatchEntryComplete'. When the user types in invalid text I get an "Invalid Value Property" error. I would like to validate the Combobox so that the user cannot type text other than available in the list, or not allow for the focus to be taken away away unless the item is a match to the list. At present due to the interaction of other controls on the userform, the only way to clear the error is by pressing the 'Escape key'. I have a button designed to reset the 'RowSource' of the Combobox but even after adding a 'MouseMove' event to this button with code
Application.SendKeys "{ESC}"
I have a list of names on my first sheet, and I want to mirror these on a second sheet, but at the start of a repeating table containing data releating to each named person. eg. I want a Name in
'Sheet 1'!A4
to display in
'Sheet 2'!A5
, and then the name in
'Sheet 1'!A5
to display in
'Sheet 2'!A32
I have a "activity log" sheet which records the date in column A, the activity in column C and the name of the customer that was contacted in column D. There may be multiple entries of a customer's name in column D.
In a separate sheet, I want to find the latest date from the "activity log" sheet that a each customer was contacted and the specific action on that date. In this sheet, the client name will be in column A, and I want to record the last contact date for the client in the same row in column J and the type of contact in column K.
