I am trying to build a table from different worksheets into a table in order. I have 65 sheets with peoples names and a points total for each person. I am struggling with making a summary table with everyone in numerical order. Highest at the top.
Names is in cell P1
Total points is in T53
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
I have a file that has 62 worksheets in it. 1 for each day shift, and 1 for each afternoon shift (1 Days, 1 Aft, 2 Days, 2 Aft, etc). I want to have a new worksheet that has a table with a column for EVERY shift, with the associated value from BH11 on that sheet. (see chart below).
The problem is that I don't want to write these formulas 124 times each month to create this table, and Find & Replace doesn't work with worksheet names in formulas.
Is there a way to do this without manually typing all of the formulas? (I don't want a sum formula--My table will be 62 columns and each cell will have a link to the cell BH11 on the associated worksheet.--If I want to see what our value was on the day shift on Jan 15, I would go to that column in this chart and it would have a link to '15 Days')
1 Days
1 Aft
2 Days
2 Aft
3 Days
3 Aft
4 Days
[Code]........
I have two worksheets. One shows imports, the other sales.
I might import Item A at a price of $1.00 and a few months later Item A costs $1.25. I got 100 at $1.00 and 40 at $1.25.
I have sold a total of 110 items.
What I want to do is make a pivot table from both worksheets. I want it to show that I imported 140 units of Item A at a total cost of $150.00 yet I have only spent $112.50. My arrangement with the source vendor is to pay AFTER the product is sold. So I owe $112.50 to the source vendor.
I've tried copy and paste of one sheet into a 3rd new sheet and then copy and paste the common information PLUS Quantity Sold from the 2nd sheet into the 3rd.
When I make the pivot table, and I can not get Item A to multiply 100 x $1.00 and 10 x $1.25. It shows sales of 100 and sales of 10. I can get it to show costs of $1.00 and $1.25. But I can not get it to multiply, as the figures are one two different lines.
I want to create a summary pivot table report + chart. What I have is 12 worksheets, one for each month, each with total amount invoiced and gross profit by client.
I want a pivot table that shows me the clients and amounts each month (this is the bit stumping me). Basically I want a drop down box to select the month on the pivot table, which then shows the data from the relevant worksheet (Aug-09, Sep-09 or whatever).
I've tried using multiple consolidation ranges but this just aggregates all the data; it doesn't allow me to limit by the worksheet the data has come from.
I'm wondering if I have to consolidate all my months into one big sheet, then add a field for the month. I would rather avoid this but accept it might be the only way.
Final thing; can't use VBA at all (too much hassle for IT apparently).
I am trying to convert a workbook that we commonly use from worksheet functions over to VBA. Looking to use controls (buttons and such) to automate the tasks which functions used to do.
Most I figured out on my own, but there is one that is giving me some problems.
I have a worksheet page that queries data from an external database. I need to take this data and move it to another sheet with the correct formating and calculations. (see attached sample workbook. Sheet 2 is the database info and sheet 1 is where I need to move it to.)
Since the database data will have a variable number of rows, I need to do the following with a button:
1. If a row from sheet 2 contains data,
- Move the 'wonum' value from sheet 2 to the 'W/O' column on sheet 1
- Move the 'description' value from sheet 2 to the 'Name' column on sheet 1
- Move the 'wopriority' value from sheet 2 to the 'Pri' column on sheet 1
- Move the 'laborhrs' value from sheet 2 to the 'Hrs' column on sheet 1
(here is where it gets tricky)
- If values for 'targstartdate' and 'targcompdate' on sheet 2 are the same, then insert the 'laborhrs' value from sheet 2 into the correct day of the week column (minus 1) on sheet 1
(for instance if a database record's start and end date are both '4/13/2008' and the 'laborhrs' value is 3.00, then place 3.00 in the cell corresponding to the column labled '4/12/08' on sheet 1)
- If values for 'targstartdate' and 'targcompdate' on sheet 2 are not equal, then the 'laborhrs' value must be equally divided by the number of days difference and placed on the date columns on sheet 2 that correspond to all of the days (minus 1) that fall in that date range.
I filled out 2 of the rows on sheet 1 to give an example of what I am looking for.
I have a file with 26 worksheets, each have four columns and the last column is averaged at the bottom, and I'd like to make a summary worksheet tab showing the name of the table, the number of records, and the average score of all 26 sheets.
The problem is, some worksheets have 20 rows, some have 1400 rows. I could go choose the data manually, but isn't there an easier way to do this?
The table name is merge/centered across the four columns, and is the worksheet name (using a formula I found on here). Then the four columns have headers, then data. The last row of each worksheet doesn't have the normal data in it, but says "Records Counted," then has a COUNT of the rows, then has "Average," then has an average of the column above.
I have an excel workbook with 11 different worksheets. Each worksheet represents a project my company is working on. And in each worksheet there is exactly the same table, just with different data).
Now we would like to get an overview of all these different data put together in one 'summarizing' table on a new worksheet (number 12).
-Is it possible make such a table without having to copy past all the time?
-The data in the tables may change over time, so it would be good if that 'summarizing' table automatically adapts to the other ones.
I have a spreadsheet with a single worksheet. This worksheet contains 1200+ rows of equipment. Each row of equipment is made up of 4 columns: the equipment name, then the department (made up of sections), section (made up of teams) and team (a few workers) responsible for maintaining it.
I have been given the task of making it easy for the departments, sections and teams to identify which pieces of equipment are theirs. I have been specifically told to add a new worksheet to the spreadsheet that contains 3 drop-down menus; you guessed it, department, section and team. The idea is that someone chooses the department, section and team that they work for, then click worksheet 2 and their equipment has been filtered for them. (Some teams come under more than one section or department, depending on the task so I do need all three drop down lists). What I can't do is get a the drop down lists in worksheet 1 to filter the data in worksheet 2.
Range B9:B58 and I9:I58 data set 1 (worksheet Deal Selection)
Range J5:J18 and L5:L18 data set 2 (worksheet Tables)
Identical tables. Taking worksheet Deal Selection as our starting point column(s) B and I
If the user adds a new supply customer in column B then I would like this new customer to be
Reflected in worksheet Tables column J5 at the end.
And visa versa for column I add to column L5 at the end same work sheet.
I am guess that this could be done by amending the current macro button
Add Supplier and Add Demand on both side(s). but not removing it current functions
I have also added the question to another forum experts exchange as i am have difficult completing this task.
Is there a way to create a pivot from multiple external worksheets. I have the same format and field names in different folders on our network. I am proabably looking at reading from 5 different files. Is it an option to write a query with UNION ALL?
View 1 Replies View RelatedI have a spreadsheet with data across multiple worksheets and I am trying to figure out how to use the data from the multiple spreadsheets to create a single pivot table on a separate worksheet. I cannot reasonably bring all the data into one workshet because each one has thousands of rows. I want to pull data from select columns in each worksheet. How can I isolate only the data I need from different worksheets into one pivot table?
View 3 Replies View RelatedI have a workbook that contains one worksheet with data. This data has 7 columns and is a database metadata report.
The columns are as follows:
Table
Joined Tables
Column Name
Column Alias
Column Description
Column Data Type
Column Length
From this data, I am looking to have a script that automates the creation of a new workbook for each unique value in the Table column (i.e. tablename.xls) and saves them to my local drive. Each workbook then would have x number of worksheets named joined table 1, joined table 2, etc that relate to the name of the table in the original Table column. Finally, each worksheet will contain the related Column data for each Joined Table as mentioned above.
I'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.
The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.
Sub SalesSheets()
Dim Salesbook As Workbook
Dim NewBook As Workbook
Dim SalesDataSheet As Worksheet
Dim ClientDataSheet As Worksheet
Dim SalespersonListSheet As Worksheet
Dim Template As Worksheet
Dim rRange As Range
Dim rCell As Range
Dim strText As String
Dim SalesGroup As String
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here
Set Template = Salesbook.Worksheets("Salesperson Template")
Set SalesDataSheet = Salesbook.Worksheets("SP product YoY")
Set ClientDataSheet = Salesbook.Worksheets("Client YoY")
Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................
I believe in the past that I have told Excel to send the output of the pivot table to different worksheets. For example if I have three regions- the pivot table would send the output to three worksheets and name the worksheets with the three regions.
View 5 Replies View RelatedI would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
I have 2 large XLS sheets that need to be split into seperate sheets.
The first is only 5 columns wide but the amount of rows changes day to day.
The second is a maximum of 7 columns wide and again the amount of rows will change.
I'm hoping for a VBA code to be able to do both on seperate books.
The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters.
I would like to seperate the sheets by the company name and have the name appear as the sheet name.
Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.
I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.
I am using Excel 2007. I have a population that I used to create a pivot table. I am currently double clicking on the value cells to create worksheets of only particular "row label" categories. I am then copying the "row label" information into the newly generated work sheet name tab. This works fine when I only have a few "row label" categories to do but it is tedious if there are many categories.
Is there any way to automate the creation of work sheets for all row label values and also naming each work sheet tab with it's respective row label information. Here are images of the pibot table and the type of work sheet I would like for wall row label values.
I have a huge worksheet containing Blackberries expenses of my company divided by multiple sections. Each section follows the following pattern: Example:
Client 222-5555 MY COMPANY NAME LCC
International Calls
- Call to Bahamas on June - $50,00
Local Calls
- Call to NY on June - $ 30,00
Total Price - 80,00
I would like to select each row between client and total price and paste then on multiple sheets in order to create individual invoices. The selection must be based on the client number (for instance 222-5555) that is part of the cell value.
I am trying to divide a table of data (7 Columns, 500-1000'ish rows) into individual worksheets based on any unique values in column. There may be anywhere from 10-30 instances of the same value in Column C, and I need to bring all rows of each unique instance into a newly created worksheet to be labelled the same as the value in Column C.
The RAW data will be updated monthly by keeping a template file copying and pasting data, therefore I am hoping to have a "SORT" button with this Macro assigned to it. I have quickly thrown together a sample workbook with how the RAW DATA will be pasted into the file, with additional sheets showing how I would like the data split and organised.
I upgraded to Excel 2010 from Excel 2000 a couple months ago. In Excel 2010, after double-clicking on values in a pivot table to create a new worksheets, I'm unable to highlight those worksheets and add a column to all of them. I was able to do this in Excel 2000... I can't figure out what's stopping it from happening for the life of me. Am I missing something obvious here?
View 3 Replies View RelatedI have a table thats acting as a database analysing player data and I have a second table in a report sheet based on the database table. In my report table I have a drop down with the player names at the top and I want the data cells underneath to populate based on that particular players data from the database.
View 1 Replies View RelatedWhat I'm trying to do is link a cell to give me the sum of 2 different cells located in 2 different tables I created.
So I want to have Cell B5 look for the number in cell B3 in the Table that starts in column Q1 and give me the corresponding number in the same row in the column R1
then lookup the number in cell B1 in the Table that starts in column T1 and match it with the number in Column U1 that corresponds to it in the same row
and Finally give me the sum of both corresponding numbers.
IE.. the number in B3 is found in Q13 which uses the number it corresponds to in R13. The takes B1 finds it in T16 and uses the number it corresponds to in U16.
Then returns the sum of R13 and X16 into B5. (B5 should show 176)
I've seen several posts for how to sum the same cells over multiple worksheets, but what I'm trying to do is sum different cells over multiple worksheets. I know what the syntax would be if I was to manually type in the formula. For instance if I wanted to sum A1 to A5 on sheet 1, B2 to B3 on sheet 2, and A20 to D20 on sheet 3, it would be SUM(Sheet1!A1:A5, Sheet2!B2:B3, Sheet3!A20:D20). but how can I do that by simply highlighting the desired cells and using the summation button rather than manually typing in the formula?
View 8 Replies View RelatedI would like to copy several cells in a Summary sheet. I have 20 worksheets, corresponding to the information of 20 companies, with exactly the same format. They have obviously different data/figures.
I want to summarize some key figures into the summary sheet. I have already created the links manually and pasted all of them in a row for the first company. Now I would like to copy the same references from the other companies. Even if the cells are blocked with the dollar, excel will not go across the different worksheets to copy the same cells.
How can I do it? Certainly there is an easier way than to do 19* 70= 1330 clicks manually.
I would like to make a macro which compares the content of the same cells in two worksheets which are in the same workbook.
More clearer I have a workbook with two sheets (Sheet1 and Sheet2). What I would like the macro to do is to check cell A1 in Sheet1 and compare it to cell A1 in Sheet2 and so on till the last cell (IV65536). If there is a differnce between the two cells, then it should highlight the background of the cell in Sheet1.
I know that there has been similar requests on this forum (I searched and read them) but thew were different.
I'm trying to sync two cells in two different worksheets so that regardless of which of the two cells has a new entry entered into it, the other one will update to the same value.
I have two worksheets which both contain a cell for "Weight", the value in both cells needs to be the same and I need to be able to enter the value from either sheet....
Not sure what is missing here, but this will only highlight duplicates on the active sheet and won't cycle thru all worksheets in the workbook.
View 2 Replies View Related