I 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.
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)
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
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
I 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.
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.
I'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
On sheet 1 I have a list of 1000 firstnames On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
I'm creating stockportfolio's based upon historical stock data. I have 300 stocks with monthly observations. Stocks are assigned to a portfolio based upon their return in the previous 6 months. I need the top 10% stocks in terms of return in previous 6 months (so 30 stocks) in one portfolio and the lowest 10% stocks in terms of returns in previous 6 months returns (so again 30 stocks) in another portfolio. Each portfolio is just a sum of the returns of the stocks that are in there.
First sheet: rows contain the return a stock had in the 6 months before the month in the left column (so january 2006 contains the return a stock would have had from july2005 -december 2005). This is done for 300 stocks. Second sheet: contains the return the stock had in that actual month (so january 2006 just contains the january 2006 return) The third sheet is where I want to create the decile portfolio's. This means that for every month i am creating 10 portfolio's: Each month "portfolio 1" should contain the sum of the returns of the stocks with the 30 highest returns in the past 6 months. "Portfolio 10" should contain the sum of the returns of the stocks with the 30 lowest pas 6 month returns. (30 is 10% of 300: that's why it is called decile portfolio)
So I should sum things from sheet 2 with the condition referring to sheet 1 where the returns of the past 6 months are displayed. I am trying formula's like =SUMIF(RANK...) but am getting no results. Somehow excel should select the right stocks and sum the 30 returns. I'm totally lost after a lot of trials.
I have two sheets with a Unique ID that I'd like to match and generate a third sheet. The third sheet would consist of First Name, Last Name, ID number where the phone numbers match from between sheets.
Sheet 1: A = Fname, B = Lname, C = Phone Sheet 2: A = message, B = Phone
I'd like to make a Sheet 3: A = Fname, B= Lname, C = Phone, D = Message
Is my only option to try and get on a PC and use MS Access? Never had to do this in Excel before.
I'm trying to delete sheets based on a list in Excel 2003 using VB, any ideas how to accomplish it as the various ways I've tried haven't even come close to working!
I am looking for a macro that will, upon command, print all sheets (20+) in the workbook with a value greater than $0.00 in cell I27. Some have a value there, others don't.
I have two sheets that both have all the same company names on them but one of the sheets doesn't have ALL of the company names. So, one sheet might have 2500 rows with company names and data specific to that sheet, and the next sheet might have 2450 company names (all match up to the first sheet, but 50 are missing), with different data.
The first sheet is A through L, the second is A through K.
Company Name and Name will match EXACTLY on each sheet, but the second sheet won't have quite as many. So, if possible, I would like to, where column A in matches in both sheet 1 and 2, move or copy all the data from sheet two into the columns on sheet one that come after L.
Sheet one column A has random dates from 1 jan 2013 to present in order base on fuel purchase dates for our company.
sheet one column F has the purchase price i.e 3.98 gallon
Sheet one column A can have a date entry of lets say 2/4/13 with no data in F because no fuel was purchased just the meter was being read.
Then Sheet one column A can have 2/5/13 where column F has 3.78 where fuel was purchased. (these are 500 gallons tanks that are being refilled as needed)
Sheet 2 row B has just the month formatted as Jan 13, Feb 13, Mar 13, .... you can use B1, B2, B3...ECT for the remaining months
What I am looking for is on sheet 2 in cell C1 a way to look at B1 for the month (i.e Jan 13) look at a range of cells on sheet 1 column A for any dates that fall in the month of Jan, then look for data in corresponding F range and if no data is found then use the last months data (Dec 12)
I can make sure I place a dollor figure in the first row of Jan on the sheet, so by default we can use that if no data exist for Feb.
I'm pretty new to coding from scratch in VB, and I've got some code that should loop, but it doesn't. The idea eventually will be to download a spreadsheet, make a new sheet for every item in a range, and then filter for each of those items and put it in the right sheet. I have to code to create the sheets (lightly modified from something I got here)
Sub CreateSheets() 'Written by Barrie Davidson For Each c In Sheets("FilterList").Range("b2:b74") Sheets.Add ActiveSheet.Name = Right(c.Value, 30) Next c End Sub
That's working well, so I've tried to modify it so that it will also delete sheets based on the same range. I've gotten it to delete the first item in the range, but then it stops.
For Each c In Sheets("FilterList").Range("b2:b74") Application.DisplayAlerts = False Worksheets(c.Value).Delete ActiveSheet.Name = Right(c.Value, 30) Next c End Sub
By the way, I don't know what the ActiveSheet.Name = Right(c.Value, 30) line does, but I put it in since it was in the code I was modifying.
Now I'm looking is when opened in Excel, it should create a two sheets excel file 1) with the First Section and the 2nd with second section . based on headers included in file.which then will be used for PivotCharts..in 2nd stage of automating..
