Creating A Master Table

May 3, 2006

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

View 3 Replies


Creating Master List From 4 Tabs?

Apr 24, 2012

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.

View 1 Replies View Related

Creating Master Database With Linked Columns?

Jul 1, 2013

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.

View 8 Replies View Related

Creating New Worksheets Based On Information From The Master

Jun 14, 2007

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.

View 3 Replies View Related

Creating Master Sorting And Improving Workbook View

Jul 31, 2012

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

View 4 Replies View Related

Creating Macro To Copy Time Sheets Data Of Employees Into Master Sheet

Apr 15, 2013

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.

View 12 Replies View Related

Update Master Table

Jul 20, 2009

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.

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.

View 10 Replies View Related

Creating A Mat Table Based On A Price Table

Dec 7, 2008

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)


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

View 9 Replies View Related

Update Table Based On A Master

Aug 22, 2007

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".

View 9 Replies View Related

Formatting Budget From Master Worksheet Using Table

Mar 19, 2013

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 Related

Combining All Data Tables Into One Master Table

Apr 10, 2014

I 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.

View 4 Replies View Related

Preserve Format Of Table When Splitting Master Into Separate Tabs

May 27, 2014

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?

Sub DispatchTimeSeriesToSheets() Dim ws As Worksheet
Set ws = Sheets("MasterList")
Dim LastRow As Long

LastRow = Range("A" & ws.Rows.Count).End(xlUp).Row

[Code] ......

View 4 Replies View Related

Creating A Top Performers Table

Apr 7, 2007

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


View 14 Replies View Related

Creating A Sports Table

Nov 16, 2009

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.

View 14 Replies View Related

Creating Rules For A Table?

Dec 9, 2013

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 Related

Creating Multiplication Table?

Aug 1, 2014

Using 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


But when I enter the two numbers It just displays the value in cells(2,6)

Attached File : table.xls‎ ..

View 1 Replies View Related

Creating Pivot Table In VBA

Mar 27, 2012

I have the following code to create a pivot table in VBA

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.

View 9 Replies View Related

Creating / Populating A Table

Jun 22, 2012

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.


Spreadsheet I have now


View 3 Replies View Related

Creating Pivot Table

Feb 16, 2009

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.

View 9 Replies View Related

Creating A League Table

Mar 4, 2010

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

View 9 Replies View Related

Creating Monthly Data Table From Weekly Data Table

Jun 1, 2009

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 :


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:


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:

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.

View 14 Replies View Related

Creating Pivot Table Format?

Jul 21, 2014

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 Related

Creating A Ranked Table Using VLookup

Oct 8, 2009

This 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?

View 2 Replies View Related

Creating Table From Data From Several Other Tables

Jan 10, 2014

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....

View 2 Replies View Related

Creating Random Multiplication Table

Aug 9, 2013

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.

View 14 Replies View Related

Creating Full Titles Using Table?

May 13, 2014

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



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

View 2 Replies View Related

Creating A Pivot Table Using A Macro

Mar 23, 2004

Creating a pivot table using a macro ...

View 9 Replies View Related

Creating Pivot Table From Multiple Sheets

Jan 15, 2014

I 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 Related

Creating A Table To Record Productivity In Hours

Jul 25, 2014

I 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 Related

Creating Lists Based On Values In A Table

Feb 16, 2014

I 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:


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?

View 6 Replies View Related

Copyrights 2005-15, All rights reserved