They sent me a workbook with a lot of information including Employee Name, Position, Supervisor name and Organizational Unit (in columns A, B, C and D respectively). With this data, I need to create a "Tombstone" for each supervisor with his/her name, organizational unit, and the name and count of each position he/she manages. For example:
John Smith
IT
Analyst - 10
Programmers - 15
Now, I was able to easily get the names of all the supervisors and their respective organizational units in the tombstones. However, I'm stuck trying to find a quick way to enter all the positions each supervisor manages and their count. And with more than 300 supervisors in my company, doing this manually would take too long.
This particular formula is used several times to gather various data points across 3 different sheets named Month-1, Month-2 and Month-3.I gather the data the following 3 formulas then sum it as needed with a helper cell.
I 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.
I am trying to create a 2 variable data table. I have the variables in a different sheet from the datatable sheet, and I read here XL: Error Message: "Input Cell Reference Is Not Valid" that if I link these variables to the active sheet where the datatable is located it should work, but the table is showing the same result in all the fields.
When I put the put the variables value in the same sheet as the datatable and link to these values from the other sheet the table is working fine, so I know that my design is correct but it has to do with referencing or something else that I need to set but I am not sure what it is
Trying to create a chart from a table of data, however my column of data has zeros throughout. How do I insert a chart with just the data that are not zeros?
I have a client list/table that gets new data each day. (ALL) - see file
I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria...
On the (ID) sheet I need all the clients that have ID numbers to display, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.
Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.
Is there a way to do this using formulas or do I need code to do this?
I’m trying to do is create two tables from data evenly spaced throughout columns A to I. To make the screenshot easier to view I moved the second table under the first. The data is arranged so that each “Subject” has 42 columns and is arranged by “Type” and then by “Subject.”
The tricky part is that the number of subjects will vary so everything has to be done in some sort of loop. From the little I know about VBA I was able to record and edit a macro to add spaces every six rows and average the “MaxResponse.” (that’s what stage the screenshot was taken at) To make the table I think I need to write some sort of Dim statement but that is way beyond my comprehension.
Here is a link to a photo of the table http://yfrog.com/77excelshot3j
I have a set of exported data from a Project Management SW (activecollab). The result is an excel .xslx file with a table inside with a lot of cells I am not going to use. Additionally, what I would like to do is creating new tables on other sheets that use only the data I want from the export. For example, I have two projects and three employees. I want to create three tables with the names of the three employees. In each table I want the tasks done by them and the time they will spend on them in order to create a Gantt chart. I need a solution that allows me to create new tables with selected data from a bigger cluster (the export). you do not need to give me the exact solution, I only need to know whether it is possible or not and where could I get the info to do what I want.
I have written two macros to create a pivot table that displays data by month. I created a column that is a flag that indicates if the if an instance occurred in the last 4 months(later used as a filter). The issue is how to handle defining the last 4 months. If the current date is prior to the 16th, I want to define the last 4 months as not including the current month. If it is after the 15th, I want define the last 4 months as including the current month. Currently I have two different macros and I make the decision on which to run. I would like the macro to handle this for me. Below is the cell formula that I use to set the flag if it is in the first half of the month.
I have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
how to create a pivot table that does not skip through blank data cells. I have a pivot table with data for several dates, but not every date has a data point. I would want the pivot table to show all the dates with the blanks, rather than skip through the days with no data.
I am improving an old spreadsheet that contains a tab for inputting the following data, all using data validation:
date employee (who did the work) team (for whom work was done) department (for whom work was done) type (of work done) Project (which property worked on) new/revision (was this a new project or revision of old) time (spent working on this)
I have a sheet with a drop down in which you can sort by employee and the associated chart and graphs adjust the information accordingly.
I need to create a chart and graph in which I can track the trailing month as well as year to date. Ideally, I would like to sort by the following:
- This month (i.e., the 1st of the month to present) - previous month (etc., be able to select all previous months for which data was entered, i.e. Jan., Feb. March. etc.) - Year to date
I know there is a way to do this - I thought maybe it would be through some formula incorporating =today() and subtracting back to what you need using numeric dates?
I have created a pivot table in a workbook which relates to data from 2011 - 2012 and this works perfectly. What I now need to do is to copy the pivot table (without the data source) to a new workbook for data which will be collected from 2012 - 2013. Unfortunately when I copy it and try and find the new data source it does not update the filters according to the data in the new workbook.
I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name 1/01/2014 2/01/2014 3/01/2014 6/01/2014 7/01/2014 8/01/2014 9/01/2014 10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
Is it possible to aggregate data in a pivot table from different categories?
I have excel 2003.
For PURE illustration, I have 6 columns, A-F, respectively:
"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.
I want to see in a final output:
Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.
When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.
It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
I have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets:
I want to accomplish something like this but slightly different:
[url]
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
I have a 11 worksheets in my workbook and 1 of them I'm using as a Index. All the others are for a individual person. Basically, I want to gather all the information from the 10 worksheets (but I want to keep the info on those worksheets) and transfer it all to my Index worksheet.
The thing is, I want to transfer a certain range to my index worksheet, and when I do so, it could automatically number the rows.
Let's say I add information on 3 rows to all of the worksheets and then gather them and insert them in my index worksheet. The next time I add information, I want to select all the information again and write it over the one I inserted before.
Example, I want to transfer range B5:K? (? being the last cell with info in it.) from all my worksheets to the range B5:K? on my index worksheet; writing over the old info on that sheet.
I have an excel with several sheets with task for different departments. All of them are assigned to a person responsible for that task. I applied auto filter which gives me the overview for a person Per department, now i would like to gather all departments into one so we have an overview for each person what his tasks are.
I have a problem with my spreadsheet. I am trying to created a sheet to record time trials for bicycle racing. I have created in the main tab 'time entry' a class, rider #, name, incremental start time, and finish time. There will be 6 classes of racers (i.e. xm,xw,sm,sw,nm,nw). What I am trying to accomplish on the results tab, are the results based on the riders class. All of the riders classed as 'xm' listed with their respective info and time. I have attached a copy of what I have so far.
I have a static table that contains a list of all posible components for a particular product that we sell. The sheet successfully calculates the qty. of each component required for a particular install.
What I would like to be able to do is to create a new list able from the static one that only shows the components that are needed i.e. those that don't have a zero qty count.
I have seen some examples that come close on YouTube but can't find anything that does exactly this.
I have a Master list of kids names and age groups, that will be used by Coaches to update, from this i would like to have each of the different age groups details extracted into different worksheets within the same workbook.
I used an IF statement to but found it was leaving blank lines between finds.
I've started building a macro that loops the sheets and collects the information onto the first worksheet. I've been using Activesheet and activecell references but i'm afraid looping will change these references.
I'm using excel 2007 sp3. I have a function that I want to adjust so that, when used, it spits back the last two numbers in a specific cell. Is there an easy way to do that? If my cell said blahblah12395-01, how can I get excel to gather the '01'? My current code is below...
Code: RowPos = RowSpec & ", and " & Range("C" & LoopStrt) The 'Range("C" & LoopStrt)' is what I currently have to gather the contents of the cell. I have a message box later that displays the "RowPos" string.
