Create New Tabs For Every Different Field Value Within A Column
Jun 2, 2014
I'm trying to reduce some manual labor that I do each month by figuring out a way to create additional tabs for every different "branch" within a given column from a master table. The branch numbers may not necessarily be the same each month as some old may go and some new ones may come.
I have attached two excel workbooks. One shows the master "Data Table" and the other shows what I want it to look like "After Sorting". As you can see, I have to create a separate tab for each of the branches listed in column A on the master table. This becomes really cumbersome, especially when we really have about 20 branches but I'm only using these 6 for the example.
i got a workbook made by one of the members in which on clicking of a button (make sheet)---->tabs of all the data in column A is made in that sheet. but if i want to copy a specific data that is present on main sheet to all the sheets created on click of button.
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
I had in my macro toolbox a macro where in column A I listed names for worksheet the macro would then create and name the worksheets from the list in Column. But unfortunately our share drive where I kept the toolbox crashed and all the data was lost.
I have a list of pairs of 406 pairs of currencies like the list below and I need to create a tab for each one of them and will take ages to do it manually, I am looking for a macro that can do it.
I have a column with multiple currencies. Is there a way to write a formula or some code that will look to that column, and create a new Tab for each new currency name it finds, and then name the Tab that currency?
For example, C5:C15 has an assortment of entries either USD, GBP, AUD. I'd like a search that looks to C5 and creates a new tab labeled whatever the first currency is, then move down the column and do the same thing every time it encounters a new currency, but pass over a cell if it's a currency that already has a tab made for it.
I have two question for Excel in combination with microsoft outlook:
1. I was wondering if it is possible to create an email from Excel which automatically adds the From-field with a pre-defined emailadres. I am using CreateObject("Outlook.Application"), but I can not add the From-field.
2. I also would like to know if it is possible to do automatically fill the To-field with the emailadress from the user who send me the original file. Maybe this needs some more explanation (sorry my english isnt too good):
* A user can download an excel-form from the internet * The form is sent to my emailadress * I give a number to the form * The user will get a message that his question is attend to with the number of the question
So I use a macro-button to reply when I have filled in the question-number.
I don't know VBA code but i am familiar enough to know that a code can be written to separate out information into different tabs on a worksheet.
For example, I have a standard worksheet with say 20 columns going across and 20,000 rows going down. At each "name" change in row 1, I would like a macro to copy into a new tab. Basically the end result will have a tab for each name and the corresponding columns that go with the name.
I need to create a Worksheet within a macro that has 10 tabs. I had something running, then I made some changes and I got Application-defined or object defined error.
Sheets("Sheet1").Name=Sheetname(1)
Sheets.Add after:=Sheets(Sheets.Count)
I was looping thru this up to 10 now its not working. This isn't too complicated,
I have a large spreadsheet and want to sort by Column "L" and copy all the rows where Column L has the same value into a new tab named with the value in Column L.
Can I create a calculated field based upon the count of data items in fields as opposed to sums. My data is confidential so here is a dummy example:
Can I take the count of Cars per individual salesmen and divide by the total count of vehicles? Ultimately, I will need to code this into VBA once I know if it can be done.
Vehicles l salesman car1 Bob car2 Kelly car3 Bob car4 Bob car5 Tim car6 Tim car7 John
With this result:
Salseman l Percentage of Vehicles Bob 43% John 14% Kelly 14% Tim 29%
I need to create a formula using 2 different tabs within an excel workbook. I need to search column A on the first tab and column A on the second tab and if they match I need to compare column F on the first tab to column F on the second tab. (This column contains my staff names but the problem I am running into is on the first tab their names are listed first name last name and on the second tab they are listed last name, first name). If column A and F match I need to search column H on the first tab to column H on the second tab. If all columns match up to this point I need to take the data in column Q on the second tab and in put it into column K on the first tab. Is this even possible?
I would like to combine 2 tabs into one spreadsheet. I was not able to do so, because it exceeds the max rows allowed in excel. I have installed powerpivots but not sure how to combine data using powerpivots.
I am trying to create a workload spread sheet for work I need to have the same looking spread sheet 365 times but also need each sheet to be dated, EG (tue,01/04/2014 through to tue,31/03/2015) I can create 365 tabs that have the same spread sheet on and I can create dates but not do both at the same time, it isn't fun doing copy and paste 365 time.
I work with contracts and have to report the sales for each contract by customer number, name, address, product code or sku, sales amount, quantity, and invoice date. Each contract number is associated with a product group and within that product group are a laundry list of product codes or skus.
Customers are often on different contracts for different products and may purchase some or all of the products on that contract. When I run a query from the system to extract this data, I get one sheet that contains 20,000 lines that have to then be sorted and seperated into different tabs based on the contract number. Keep in mind that a customer may be listed multiple times because each line in the query represents a product code and an invoice date.
I currently have a macro that will format the reporting such that it sorts the data, however, what it doesnt do is look for each instance of a particular contract number and separate it into different tabs.
So below are two examples: April and May 2008 Reports
April 2008 Report Contract 1 contains 5,000 lines of data - extract into new tab Contract 2 contains 5,000 lines of data - extract into new tab Contract 3 contains 5,000 lines of data - extract into new tab Contract 4 contains 5,000 lines of data - extract into new tab Total lines = 20,000
May 2008 Report Contract 1 contains 7,500 lines of data - extract into new tab Contract 2 contains 5,500 lines of data - extract into new tab Contract 3 contains 8,000 lines of data - extract into new tab Contract 4 contains 9,000 lines of data - extract into new tab Total lines = 30,000
So for the April Example, I would like excel to take this one list and break into 4 new tabs based on the contract number and carry over all of the data into that tab.
Where this may become difficult is that there may be more lines of data one month and fewer another. Im not sure if there is a function that will tell excel to stop when it reaches the last line for one contract number and to start a new tab for the next contract number. (see the month of May example)
I have a Pivot table that pulls the Avg of two fields for two months, see example below.
Avg Gross $ Avg Net $ Jan 2014 20 10 Feb 2014 30 20
sample 1.png See sample attached.
The Avg Gross and Net is shown by going into the values and selecting "Summarized value by -> Average".
On the right side of this Pivot, what I wanted to do is to show a Avg Gross to Net $ in this pivot. So the formula should take "Avg Gross $" - "Avg Net $" = Avg Gross to Net $.
I am having trouble calculting this new field in the pivot table using a calculated field because the Calculated field pulls the variables from the existing field list and there isn't a field called "Avg Gross/Net"....I need to find a way to calculate the Avg Gross to Net into the Pivot table so I can pull a pivot graph out of it.
I have two or more fields of data, where parts of them are auto populated based on inputs from elsewhere. That part i have working fine, however the problem arises in taking that information and displaying it as a Bill of Materials list on another tab in my excel file. I want to take the information, and only display it in the BOM tab if that piece of information is being used (ie item count field not being 0). I know how to do that part in a rather simple format, however i'd prefer to make it so that when i have the list, and a certain item isn't used then its not included in the BOM, and an empty space is not left behind. I would prefer this to be all be done automatically by a formula if possible instead of just having to resort to sorting the table every time the tool is being used.
Is this even possible in excel, A quick example doc is attached, shows 2 example fields. i'd like to take the information from those 2 fields. and somehow create a single list, with a single heading along the top, with all row containing a 0 "Unit Count" to be omitted entirely from the list, and not have a blank space where the item should be. Keep in mind these fields may be greater than 2, and may not be position one after the other in a strait down line.
I want to create a macro that creates a new .xlsx document based on cell values. And input 4 tabs into each document with specified tab names. I have a document that pulls from external sources and fluctuates with amount of data per day. I envision something that will make a new document named for the contents in cell A1, then function as a control+down to create a new document for A2, then A3 until there is no content left (should mention these are lookup formulas, and if there is no data it pulls a value of "0")
Is there a way to put in the code, the tab names it would create in each new document, or would that have to look to a cell value for the naming? The tab names would be "GS", "MYSS", "COLL SHEET", and "WIRE".
I'm working on a project in which I have 50 states + DC listed by 2-letter abbreviation, and want to return a value in another column that will be a non-unique number (some will duplicate in the second column.)
Also need to do this same operation with first column (state) and third column, which also is comprised of non-unique numbers.
Hlookup, lookup & pivot tables seem to not be a fit for this.
I'm sure this will be a very simple one for someone - it leaves me *head scratching* though! I have attached a VERY generic example and it includes what I am trying to SUM for the given data. From the example if we look at Question Z1: "Total Cost of All types of "Lemons"" = ?? I could simply do
