P123182.63323.393225.873299.541635.611641.7
1615.983304.913313.791637.02Totals52097.561575.5561750.5864889.2554803.6754775.2661905.5465112.9563407.6266701.1861598.34
The table supplied, starting at B8 demonstrates one of 8 worksheets in the spreadsheet... of which I would like all exported to 8 named worksheets in a new spreadsheet in the list format as:
Asset | Date | Value
I need to ignore the Totals column and row,
The table can grow or reduce the assets and
grow the days of the month, starting again at the begining of each month. I have to run a report each day of the month.
I've never worked with arrays before but after looking through some books, would like to attempt a crosstab table. I currently have data in cells A1:K8316. Row 1 consists of my headings, which include Team (column K), Ind/Dir (column I), and Hrs (column G). There are a total of 25 different teams and I would like to sum their hours for both direct and indirect. (The columns for Team and Ind/Dir are based on vlookup tables).
I've tried some of the steps I'm finding in the book but, unfortunately, they're assuming I know how to even start and what to select - which I don't.
how a crosstab query works in ms access. Below is a visual. IN my source material, the columns are Category, Commodity, Supplier, and Dollars. I need to be able to get only the top 5 suppliers in descending dollar rank in the columns adjacent to the pivot table, as displayed below. Try as i might, i have been unable to get this to work using a pivot because i don't know how to limit the supplier columns to only the top 5.
I am using a formula to find the oldest date of a list of dates depending on what country an item is from. In my place of work we use profiles so anyone can log on to any computer and access all of their own details etc.
When I use the formula the date is returned correctly in the UK format (DD/MM/YY), and another of my colleagues also. However on some other profiles, the numbers (not the actual dates) are reversed to the American style (MM/DD/YY). The file that the formula is part of is remotely stored, so the formatting does not change.
I am sure there is some local excel setting that is reversing the dates, does anyone have any idea where I might find it? Or any idea how to stop the change.
Unfortunately it is not a simple case of 3-august-08 appearing as August-3-08, instead it appears as 8-march-08. (month titles only used for ease, I the formula uses numbers).
I have debits & credits in the same excel column and i want to delete the matching amounts but with opposite signs.
Example: A B Name Amount 1)Mr. A 2000 2)Mr. B 6000 3)Mr. A -2000 4)Mr. D 4000 5)Mr. A 2000
Now i want to matching amount of Mr. A of row 1 & 3 as these two entries are reversing each other. I am poor in english but hope that i have clarified the problem
I have a crosstab query in Access that groups by month and year. In Excel 2003, I have a query that pulls in the crosstab query.
At the time, I couldn't just pull in the Access table. For the other Access tables and queries, I could go to Data >> Import External Data >> Import Data >> select the database as a source >> select the table name.
However, to get the crosstab query, I had to go to Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select the data I wanted.
Since I wanted all the data, there did not seem to be a difference. But now that new dates are being added to the database, there *is* a problem. When I wrote the query, I only included the months available -- up to June 08. Now that there is data in July 08, that month exists as a heading in the crosstab, but Excel's query doesn't know to pull it! I have to manually edit the query to add the new month.
So my question is... is there a way to do this automatically? I have two crosstabs in five different spreadsheets, making a total of ten manual updates I have to remember to do each month.
VB: Sub CreateFlat() Dim wsData As Worksheet Dim wsNew As Worksheet
[Code].....
In 2006 posting, this code was presented to the Forum. It works perfectly for a one dimension Crosstab. I have 4 dimensions that I need copied to the output.
I have attempted to modify the code, but nothing appears to adjust the pull of more than the first column of dimension data.
see the attached spreadsheet for the Crosstab data and desired output.
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
I have a large table that gives me the intersection points of many lines. I have used conditional formatting to highlight the intersection points that match by turning the text red, thus showing me when 3 or more lines intersect. Manually scanning the whole table is inefficient as I wish to expand it.
Due to size limitations, I've uploaded a shortened version of the workbook, just a copy with one example of a match.
How do I get excel to locate the matches (currently highlighted with red text) and list them in column I "Intersections" all neatly sorted?
I thought Advanced Autofilter but I don't think the criteria range can search for red text alone.
I have data in a list of sales people and dates of their sales calls. What I want to do is transform the list into a table as shown in the attachment. Then I want to calculate the number of days between the first and last sales call. This part is easy using date formatting and min/max I guess. What I can't do is get the table arranged. I have used a pivot table to count the number of calls per sales person but pivot table wont just display the dates in a table format, it will only sum or count etc.
I am wanting to manage my inventory and need to convert spread sheets with tables specifying style, size color to lists with skus specifying style, size color. please see attached spread sheet with one sheet with table and second sheet with list.
I have three columns of data starting in row 5, the headers of these columns show Origin, Destination and mileage. Is it possible to have a macro that converts the data into a table?
I have a file and it has a table on it with 2 categories.
what i want to do is select a color in the first drop down list and than in the second only select the items that match the color i have previously selected.
Is there anyway to do this with named ranges because i would like to index everything in a table.
I'm trying to create a Pivot table with various columns comparing information of executives from various Private Equity Firms. Some of the additional columns are Industry Specialty, Harvard Grad Year, My companies contact, etc . . .
The goal is to allow me to then query this information so if I wanted to see all Bain Capital employees who graduated with an MBA in 1990 and their specialty.
My question comes in trying to set up my source list for the data under Industry Specialty and my companies Contact. Is there a way I can set a value for these cells and mark them as an "AND" in the source if there are multiple contacts or industries that one of the Private equity executives is linked too, that would then allow them to be displayed as so when I create the pivot table?
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
I'm trying to create a automated calculator that i can customize and adjust. start and what functions should i be using. I've looked into Vlookup/Hlookup and it does not seem the function i need to make the workbook work. Basically what I need is when I enter a item on the A column excel would retrieve the list of Ingredients to the calculator worksheet and multiply it by the amount of variable entered. Then product will be printed and added together to the "sum row".
I need a two parameters table deriving data from a list in another sheet. The vertical column of the two parameters table will be the names of the personnel, and the horizontal one will be the dates, in weeks form. The list contains the names, flight departure, flight arrival and no. of working days spent in the foreign country. What I need to do is to tranferr the data from that list onto the table.
For eg, if john flew overseas on 20-Dec to 15-Jan, the list will show john on column A, 20-Dec in column B, 15-Jan in column C and 20 working days on column D.
This list of personnel and their data should go into the two parameters table, where it will be identified when the personnel will be overseas according to the calendar of the project. Since the vertical heading is the dates of every week, a date that corresponds to period where the personnel is overseas will reflect a number in the cell. The number will be the number of working days the personnel spent on the foreign country, therefore the max will be a 5 and minimum is a 0. I need a formula that will fill every cell within the area of the tables.
I am trying to do is create a list from a large table (see attached). I have 2 columns (A and B) listing 'Asset' and 'Area' and then 2 rows (1 and 2) listing 'type of task' and 'task'. This forms a table with entries in the cells (from C3 onwards) denoting frequency of each task at each location, i.e. daily, monthly, 6 monthly and annual. This gives me quite a large table which is quite confusing. Can I create a formula to sort all the daily activities which will give me a list of the 4 corresponding properties 'country', 'location' (in columns A and B) and 'type of task', 'task' (in Rows 1 and 2)? Then do the same for the different frequencies populated in the table, 'monthly', '6 monthly' and 'annual' ???
I have a pivot table with a group # on the left, and team members on the right. There are multiple team members in each group, and each are on a separate line in the pivot table, like this:
Group #Team1chrisdawnsally2cassiechrisdawnkathysally3jimjoejohntomGrand Total
I need to find where "sally" is on the team (easy to do"), BUT I need to display the rest of the team members in the group with her, like this:
Group #Team1Sally, Chris, Dawn2Cassie, Kathy, Sally, Dawn, ChrisGrand Total
Using the concatanate function to put them all into one cell and then filtering for "contains" was all I could come up with, but there are 14,000 groups
Is there any lock/security option in excel sheets, suppose we make some table/list and after entering one data when the cursor goes to second raw, the previous raw data automatically lock. the user cannot edit or delete that record.
I have a list of location names that may be repeated within the list. I also have possible blank cells within the list. I would like to filter them automatically into another table without having to use the autofilter or any macros to keep things simple.
How could i do this by removing blank cells in the new table and having only uniquely listed names ie get rid of any duplicates.
I am copying info from a program into the "Input" worksheet - It is from my phone switch of logins and logouts of my employees. I have 11 supervisors and my end goal is to have each one of their coordinators show up in their specific tab automatically with all of their login information, ids, logins, and logouts. I can do the other math for the Marks and break lengths but I am having a hard time with this. I have been trying vlookup, but cannot figure out a way to bring all of the different rows... It only brings over one row.
I have tried to use some of the code from other posts on this sight, but have not had any luck.
What I am looking for it to do is on the Alis worksheet - the light blue sections. I am also finding it difficult in the light purple section also. I am trying to figure out how much time they were on the clock.
