I have 9 identicle spreadsheets, one for each user on a shared workbook.
I want to create one master table linked to the 9 spreadsheets which will automatically update including when new records are added.
With this in place I can then create a pivot table
I am trying to create a master list in a new sheet based on the data contained in 4 separate tabs. The data is Username, Display Name, Company. Unfortunately some users have accounts in 2 of the companies (with the same username) and I need these to default to Company A.
The output need to be username, Display Name, Company.
I am working on creating a medical master database. I have a master sheet with a huge number of columns. I have a couple of guys working with me, and they have their own sheets in the workbook.
So here is what I would like to do: anytime one of the guys enters data into his respective worksheet, I want the data to be copied over to the master sheet in a new entry.
The columns among the worksheets are different, and I would like the data to automatically sort in the appropriate column on the master sheet when it is copied.
I have attached a copy of the workbook.
I have included an example of what I would like to see. For example, if Charles puts in an entry in his worksheet, a new entry is created on the master sheet with the information from Charles' sheet sorted in the appropriate columns.
I have a spreadsheet that I am trying to use as the 'master'. I want to break down this master by category (Column D) and label the worksheet by category (AA, Code, SQL...)
I need for the code to search the D1:D500 column and find "AA", "Code" or "SQL" and place the entire respective row on each respective worksheet. Then sort by colmn E. There are columns a:h that need moved to each sheet.
I have an excel file as attached. I have two departments Digital and Industrial. Under this I have done a drop down option for different quarter for different projects.
When I open my excel file I just want to see the Quarter column under Digital but when I double click on Digital all other columns appear.
Second this is it possible to create a master filter or sorting for Quarter above the table so that if I select Q1 in it it will displays all the project and corresponding values in both Digital and Industrial.
Attachment 47491A.xlsx
I need to create a macro to copy the data from time sheets of different employees into the Master sheet. ie., each employee's time sheet details in to a separate sheet. If any employee fails to provide his/her time sheet details. a mail must be sent to the respective employees regarding the Time sheet submission. I am providing the sample data of my files too.
Intentionally TS_Employee5.xlsx timesheet isn't provided. In which case, I have to send a mail to the respective employee, in order to remind him about the time sheet.
I'm new to Excel Macros and have hit a brick wall on what I would consider
to be my primary workhorse macro! I'm embarrassed to admit that I haven't
mastered coding for movement of data around the spreadsheet much beyond
the macro recorder. I need something tight, efficient, and (instructively commented)!
I've got a spreadsheet consisting of a Master table with 42 rows, (2 rows
per record), and 14 columns of information. To the left of that is another
table consisting of live data from the process. It has anywhere from 1 to
24 rows with 10 columns representing Current/Live/Pre-processed information,
pulled in from the company web location.
Both tables start with an indexing column of 2 character alphanumeric IDs.
They are as follows: C2, D3, D4, E3, E4, F3, F4, G3, G4, H3, H4, I2, I3,
I4, J2, J3, J4, K1, K2, K3, K4.
MASTER TABLE Index Column is B4 to B44 (2 rows per record, in A->Z order)
UPDATE TABLE Index Column is Y4 to Y24 (Could be from 1 to 21 rows (Max),
1 per record, normally in A-Z order.
What I need to do is Key on the Master index column for a particular ID,
against the UPDATE index column. When there's a match I need to take the
updated values from the UPDATE table and assign them to the appropriate
cells locations in the MASTER table.
Example:
Starting with ID "C2" in the MASTER table, check to see if "C2" exists in
the UPDATE Table Index. If there's a match the following cell value
transfers must occur. Z4 -> E4, AB4 -> J4, AC4 -> J5, AD4 -> H4,
AE4 -> K4 and AG4 -> I4. If "C2 is not found in the UPDATE Table then skip
to next ID, (D3).
If ID D3 is found, another cell value transfer must occur. Z5 -> E6,
AB5 -> J6, AC5 -> J7, AD5 -> H6, AE5 -> K6, and AG5 -> I6. If "D3 has no
updates then skip to next ID and so on. Continue checking IDs and updating
until all IDs have been checked.
I'm having difficulty crafting a macro to search 2 columns against one nother,
specify the correct ActiveCell, than offset appropriately to place the desired
value in the correct location.
Part 1: Pricelist
The user presses 1 of 3 macro buttons to access the price list.
The buttons represent 1 of 3 parts of a construction project.
The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro,
depending on which button is pressed, hides all but the relevent quantity
col & total col.
Each job phase needs its own total & having it setup this way makes it
easier to enter new items into the table (only have to enter
the item once, rather than 3 times).
The first html shows a simplied example of the "price list" described above
(before & after the quantitiy & price cols are hidden).
The user adjust the quantity & the sheet calculates the price total.
Part 1: Material listThe material list is a list of items & quantities the user selected in the
quantity col of Price list. This list is to be printed & given to the workers.
It does not show price, only item & quantity.
This list, unlike pricelist, must be visiablly broken down by job phase.
I dont want the material list to be the price list repeated vertically
on a sheet 3 times. This would take up too much space & require new
items to be entered once in the pricelist & then 3 times in the mat list.
I would rather the mat list be on a seperate sheet from price list & have
a formula in cell matlistA1 that searches the phase 1 quantity col
(then phase2 & 3) for the first #>0.
When a #>0 is found, the formula
would return an item name into the cell on matlist. Then the formula
would run in matlistA2, & search for the next #>0.
(& so on until a complete mat list table, broken up into phases is created)
Sheet2
ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73
..............................
I have one "master" spreadsheet I utilize per month. Each month my "master" spreadsheet is updated twice a week (so, for a four week month I update my "master" spreadsheet a total of eight times).
To update my "master" spreadsheet I compare it to an "update" spreadsheet that contains the same column headings as my "master" spreadsheet.
What I need updated:
I need to compare account numbers (column A) between the two spreadsheets, once I find a match I need to update each of the costs for the 5 products (columns B thru F) from the "update" spreadsheet to the "master" spreadsheet. I currently have a simple SUM(IF) statement that completes this for the first update.
I am lost at how I can have my "master" continually update for the next seven updates without losing each of the previous updates.Any help on this would be greatly appreciated.
I am attaching two sample spreadsheets - "master" and "update".
I have a Master Worksheet that I am using to populate other worksheets using the Table function. I would like to keep the format the same as the Master worksheet even if lines get added or deleted. I have seen that others group the sheets together when making the changes but I can not really us that options. I will be giving the Budget to someone else to fill out and they will not know how to do that.
View 1 Replies View RelatedI have 10 workbooks that each contain a table of data. Each table is consistent in format and headings.
I need to group all 10 tables into one big table. I do not want to merge or combine data just simply have all the data in one master table.
Previously I have used named ranges and arrays to copy them in but this seems cumbersome and thought there must be an easier way to do it.
I have a macro that enables me to split my master table into separate tabs based on the first column. However, the column widths and the header format is not retained. Is there something I can add to the macro below to keep the formatting?
Code:
Sub DispatchTimeSeriesToSheets() Dim ws As Worksheet
Set ws = Sheets("MasterList")
Dim LastRow As Long
LastRow = Range("A" & ws.Rows.Count).End(xlUp).Row
[Code] ......
I am creating a spreadsheet for my company that lists each one of our 14 stores' performance for a month. This is shown as a (£) figure in one cell and then a percentage underneath.
Question is, is there a way of listing (somewhere on the spreadsheet) the order of branches going from top performer to worst performer (1 - 14) so I know who is the best and who is the worst in the company.
Like this:
1st Place - Br 2
2nd Place - Br 12
etc
I have not found a complete excelsheet that works to my wishes... so I thought maybe you guys could help me.. This is quite a big request. What I want is to add an sheet with a automatic league table to my spredsheed. I already have all the fixtures and the reports will be filled in as the season goes on.
(so what i want is that when i fill in the results of the fixtures there will be an table that automaticlly updated. The +/- statistics isnt essential. I was regarding to +/- in total sets...)
I attached the spreadsheet.
The games will be played as a best of three stets so there will be no draws.
How can I create a rule for a table that finds a specified word and replaces it with another such that the rule would continue to apply to the table even when a new record (or row) is added. There has to be a simple way to do this without a macro.
View 3 Replies View RelatedUsing array
Part 1:
1. Create a Multipication Table up to 10x10.
2. Store the values in a multidimensional array.
3. The program should ask the user what two numbers from 1-10 would he like to multiply.
4. The program should not multiply the two numbers but instead use the two numbers as reference for the element number and locate the corresponding element.
5. The corresponding element should have the value same as the product of the two numbers entered by the user.
Part 2:
1. Do the same as Part 1 but this time automate the creation of the multiplication table using the concept of array.
Here's what I've done so far for part 1
[Code]...
But when I enter the two numbers It just displays the value in cells(2,6)
Attached File : table.xls‎ ..
I have the following code to create a pivot table in VBA
Sheets("test").Activate
Dim data As Range
Set data = Range(Cells(1, 1), Cells(1, 1).End(xlDown).End(xlToRight))
Set pt = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="data").CreatePivotTable(TableDestination:=Sheets("summary").Cells(1, 1), TableName:="ItemList")
When I run this I get the error message Runtime error 1004: Reference not valid.
There is something in the "set pt=....." that it does not like.
creating / populating a table. In the table below, the left half is the data I currently have. I need to wiggle it around to look like J10.
Basically, I need a formula that I can plug into every cell in Column J that says at the value to the immediate left in Column I. Take this value and look at Column E... then every time there is match, take the value in Column D, and find the next match. Put pipes between each value.
Basically, I am trying to get a row for each parent ID that has all the children in one cell delineated by pipes. I am trying to get a row for each parent ID that has all the children in one cell delineated by pipes.
*
D
E
F
G
H
I
J
7
Spreadsheet I have now
*
*
*
*
[code]....
I would create a field in Access that would have the Names of the Counties (for my Row Labels) and I would create a field that would contain Years (for Column Labels) rather than using each individual date from my database. I want to be able to do this on the run in a Pivot Table.
For my Row Labels I am using the Municipalities in Wisconsin. Each one is assigned a number such as: 67-251, 67-015, 67-123. The "67" identifies the County and the "251" or "015" identifies the City, Town or Village. We need to use the whole number for the Rows but we only want to use the first 2 Digits to sort by the County. 67 is Waukesha County as is 40 Milwaukee County. I want to total the county's using the first two digits of their name. The field is called "SC" and it's a 6 char text field.
For my Column Labels I have many different dates that span a period of about 15 years. I would like to only use the Year for my Column Labels. And that year would accumulate all the dates for that County for the year I want to use..
In the end I would like to be able to get a Count of Permits for each County by Year. I would also like to be able to specify only certain years that I might want to see data for. Every time there is a date in the "First" field that is a count one home for the "SC" field.
I used to be really savvy with Quatro Pro, most people don't even know what that is anymore. In that program I had a line of text where I would enter the criteria that I was interested in for my Table. Such as 51, 40, 45, 60, 67 would be the counties I want to use. 2000, 2002, 2005 would be the column values I want to use.
I started back in august doing the fixtures of my local rugby league league. I have created a league table using excel but i have been having to enter each teams points in manually adding and subtracting the points differences. I was hoping that you would be able to enlighten me in how I could just enter the results and the league table would automatically update.
I dont know if I can upload the file to show you if you can do this please let me know
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009
13/04/2009
20/04/2009
27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009
13/04/2009
20/04/2009
27/04/2009
04/05/2009
11/05/2009
etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables:
=IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
I have a table (attached example with only a SMALL part of all data) I would like to transform this into pivottable format without copying and pasting. In row 2, all of the numbers pertain to years. Column B has the number of years in force for each property account (Column A). I
View 2 Replies View RelatedThis is a spreadsheet I'm using for my fantasy basketball draft that by adding information into table C3:N15 will populate stats into team sheets and output average stats into table D20:L31 and then create a rotisserie style scoring table outputted into B36:M47.
What I want to do (and have tried to do in table B51:M62) is output this to create a sorted ranked by Approx Score without using macros or VBA. I also want to do this using Excel 2003 rather than 07 (I'd love to take advantage of the AVERAGEIF function but i figure compatibility is my first concern).
The issue I have run into is that tie scores are going to make it difficult, and utilizing a table w/ summed ranked will lend itself to that happening...a lot. How can I solve this?
My family owns a Fast Casual Middle Eastern/ Mediterranean Restaurant. I'm taking charge of making it as profitable as can be! Our plates are different combinations of kebabs, rice, Falafels, Gyros, salad, platters. So theirs tons of combinations.
I am Dead serious about knowing REAL plate costs:
I spent days entering price data, weighing meat, produce, calculating usable yields. I then built "component builders" using data validation drop-downs, and Index + Match functions to construct the costs of the recipe items,standard base items and side items. So I now know How much a skewer of Marinated Kebab Costs me, and all the Components that go into building a plate.
I've now built a "Plate Builder" (See Pic) to create the EVERY combination available at our restaurant. So when I'm done filling in all the builders, I'll have a whole lot of tables. I'd like to pull the name and plate cost from each table and create a big table organized by whether its eat in or take out. How do I do it.
TL;DR: Need to create a table that pulls data from several other tables....
Here's what I'd like to do:
Have 10 columns... one column would be for the "1s", another for the "2s", another for the "3s", etc...
But I don't want to have them in straight order (i.e. 1 x 1 =, 1 x 2 =, 1 x 3 =, etc...) because the pattern is easily memorized, no matter what number he's working on.
I'd like to be able to hit a button (or something) and have each column shuffle (or randomize) the order in which the equations will be displayed.
So, a column might come up 3 x 6 =, 3 x 9 =, 3 x 1 =, 3 x 4 =, etc...
And next time it might come up 3 x 5 =, 3 x 2 =, 3 x 4 =, etc...
This way he won't be able to memorize any patterns and he'll actually need to learn his multiplication tables.
I am trying to create a system to organize my company products. Up until now we had no part numbering system so I created one. The part numbering systems goes as such
Brand Code.Product Code.Bike Code.Color
These are examples
S7R.SF.101.Black
S7R.SF.101.Blue
S7R.SF.101.Gold
[Code]....
What I would like is to create a formula to expand the part number back to its full length name using tables on other sheets. I have tables for the brand code, product code and bike code. What I would like to have it do is Lookup the first part of the code "S7R" on a column from the following page and return the data from the cell next to it. Then continue on to do the same for the second part "cs" and the third"101" and so forth.
Basically the top example above would expand to be
Strada7 Short Folding Aprilia RSV4 Black
S7R = Strada7
SF = Short Folding Levers
101 = Aprilia RSV4
Black = Black
Creating a pivot table using a macro ...
View 9 Replies View RelatedI am trying to create a single pivot table using data from multiple sheets (one per month). I am trying to summarise the headcounts (count of assignment number) by month in a single table and then be able to select single months/areas or combination of months/areas in the same way as a single sheet pivot table. I have managed to get the table part way there but it’s not counting the assignment number and my Jan data is not appearing (see attached). How do I fix this? (I have only used 5 months of year in example due to the large file size) Alternatively is there a different or better way to this other than pivot table?
View 2 Replies View RelatedI have created a table to record the daily hours worked by staff, however ,since the data I'm dealing with is quite large (over 300,000 rows), I need a function/formula to make populating the table quicker and less daunting.
View 2 Replies View RelatedI am trying to create an array formula that would list all the corresponding rows that contain a certain value.
Here is an example workbook to illustrate what I'm trying to accomplish:
Book1.xlsx
I need to create a list/table that would show all the row labels that correspond to a value. For example, I need to make a list of all cells with a value of 1 under the "Green" column and list the row label(s) that correspond to 1.
Creating the list would result in following return values for "Green": A,C,E,F in separate rows.
I've been using [URL] ..... as a guide but the formulas did not return a corresponding row label. Pivot tables did not accomplish this either.
Is it possible to do without using an array formula?