Free Microsoft Excel 2013 Quick Reference

Help to Create Matrix table.?

Good afternoon,
would you please help me to create 21 persons matrix showing 10 weeks for pocker game,each table will consist of 3 player,each player need to play each other player
only onceand ech player to play one game a week.
I would appreciate any help,thank you.

Post your answer or comment

comments powered by Disqus

I've created a Excel Add-In using Excel 2007 and created a OLEDB connection with this Add-In to create pivot table with that conenction. I've added that OLEDB connection to Add-In (not for activeworkbook) for security resons (If I add the connection to the activeworkbook with help of Add-In my username& password will be displayed in active workbook connection properties).

I'm trying to create pivotCache to create pivot table with Add-In's OLEDB connection, it is raising error(1004: Application-defined or object-defined error).

The following code used to created pivot table cache
ThisWorkbook.Connections.Add "OlapConnection", "", Array("OLEDB;Provider=MSOLAP.3; Persist Security Info=True;Data Source=datasource;Initial Catalog=dbname;roles=Role2005;TimeOut=5000;User ID=UserID;Password=password;"), Array("SampleCube"), 1
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ThisWorkbook.Connections(OlapConnection), Version:=xlPivotTableVersion12)

Can anyone help me to resolve this issue?

Thanks in advance!

Let's say I start in an random cell. Is there a macro that can help me to create a table and all I need to do is to input the row number and column number I want for the table?

Thanks a lot!

I need to create a table with numbers ranging from 1 to 600. It could be
Just automatically filling cells with these numbers in increasing order.
I'm trying with excel, which sould be the most appropriate software to do
that, but I'm very negated with it. Could you please help me?
Thanks a lot, ciao!

Hi i'm creating a workbook to orginize stock, i'm not sure of how to create a
table to give varying degress of discount according to the quatity bought ie:
5 items or less = no discount, 6 to 11 items =2% discount and so on, i have
used an if statement so far but i'm not sure how it is meant to work in
relation to the products and customers sheets, how does it all work together.
Any help & guidence would be much appreciated.

I'm trying to create a table(or form) using Excel, but i need to know is it possible to create this kind of complex tables in excel ?

Pls check this link for sample table which i have create

And i'm a novice in excel so pls help me out...

Advance thanks

Hi Guys I need help to create a dynamic chart by using a combobox. ex: if I select abc from combobox all list included in abc category will only be shown in the chart, the difficult part is abc has ex 10 rows while def has 19 and so on.... is it also possible to add a scroll bar in that chart? I included the data

Hi ,

I have recorded a Macro to create Pivot Tables, I would like this to run in any workbook.

The problem is that every time a Pivot Table is generated Excel alters the number of the table(Pivot Table options). This then does not match that in the Macro as below("PivotTable1").

How would I rectify this problem.

All ideas are appreciated.

Many thanks


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
"Sheet1!R1C1:R37C6").CreatePivotTable TableDestination:="", TableName:= _ 
"PivotTable1", DefaultVersion:=xlPivotTableVersion10 
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
ActiveSheet.Cells(3, 1).Select 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GAME") 
    .Orientation = xlRowField 
    .Position = 1 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("LANGUAGE") 
    .Orientation = xlRowField 
    .Position = 2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I need help to create a drop down box in a cell. The information I want to be available in the drop down box is in a range of cells located in the same worksheet.

Create a drop-down list from a range of cells. ?

In workbook "sheet2"
From A1 to A7 I have Monday to sunday
I highlighted these 7 fields and defined the "NAME" for the list as "=weeks"
(no quotes)

I then went to
"sheet1" and I select the field in which I want the dropdown selection

On the Data menu, i click Validation, and then click the Settings tab.
In the Allow box, click List.
To specify the location of the list of valid entries, I did the following:

The list is on a different worksheet in the same workbook , so I entered the name that I defined (=weeks) for my list in the Source box.


Any help please


I'm having problems trying to get some uniformity in our documents. The problem is that all of our company Winword documents (thousands of documents) already have a table in the footer in which the following information is listed in sequential order:

Machine no.:
Page x of x

The table is made up of 2 rows and 5 columns.
The bottom row is updated by the worker that processes the file.

Furthermore, we do not edit the winword footer but instead edit the document properties because there are fields in the winword footer that are connected to the document properties. In other words machine no., version and date are edited in the document properties and this changes the footer.

Can this be done in Excel with a footer?

I have found out how to insert the properties into the footer (in Excel), but not how to create a table in the footer.

I need to monitor asset tags for a very large company and need to create a table to show what machine types are at each location

The problem i have is that the machines are tracked through a repair loop and can be sent to another site with in weeks so the data is spread over a number of cells up to a maaximum of 7 moves

I need to pick up the site ID after the UKD from any of these 7 possible move records and then create a pivot table to show the type count by this location number

[type (could be server, till, email, word processor, LAPTOP ) etc
serial number 1234567abc
Initial location UKD99999-000
date 1st move whenever 0
1st location UKD99998-001
date 2nd move whenever
2nd location UKD99998-000 ETC ALL THE WAY TO 7TH LOCATION

So the finished table looks somthing like below

]site Server Till Email W/Processor Laptop
99999 1 1 1 1 1
99998 1 1 0 0 0
99997 1 1 0 1 1

obviously the various counts would depend on the type count

Hi all,

I have an existing Table with 8 to 10 columns of data. Instead of using Excel's Table Filter feature or Pivot Table, I would like to create a new table "filtered" on one of the original columns and have all of the data rows and (columns) displayed on another sheet using formulas (without "helper" columns). For simplicity, the example below shows just 5 of the columns.

Overall Usage (sheet name)

PCN PartID WC 2008 Use 2008 Issue

8430109 SEA37 124 1 0.015
8430109 SEA37 124 3 0.045
8430109 SEA37 152 2 0.030
8430123 RES44 124 6 0.200
8430123 RES44 C09 6 0.200
8430123 RES44 P11 2 30.00
8400188 PAI23 124 5 3.66
8400188 PAI23 542 2 No PCN

The No PCN refers to a lookup on another table when there is no match to the PCN on this table.

What I am needing on another worksheet called Sheet 124:

A1: WC=124 (filtered on this in WC column – which is changeable to create different tables)

PCN PartID WC 2008 Use 2008 Issue

8430109 SEA37 124 1 0.015
8430109 SEA37 124 3 0.045
8430123 RES44 124 6 0.200
8400188 PAI23 124 5 3.66

I have tried various INDEX/MATCH combo’s but can only get the first instance of the data.

copied across.  Column H is where WC resides from the original table.

Of course for even more flexibility (and yet more complicated formula), I would like to make the columns variable by using drop down list headers and have the data match that as well.

I need all instances of the data sorted on. This is on 3000+ rows.

Thanks in advance.

dear all,

I need your kind help to create an critical macro in which i have 2 files, 1. rawdata in which i need to filter for certain conditions to match in the summary file and paste it to the corresponding matching cell..

here is my creteria...
1. raw data
i have columns like item, supplier, region, sourcecontrol & business group under which i need to filter for 3 or more criteria in the supplier column and one criteria in region and one criteria in business group and take that sum of value and open the summary file in which contains in columns it contains global ops, subtotal, cpc,mrp,irp,smi as header .... and in ops it contains it contains vi and nonvi and total, under which i need to filter the value in rawdata file and then sum that and paste it in summary corresponding to global ops, cpc, crp, mrp (these are headrs of my summary file)and first column:global ops contains in cell a4 VI AND in A5 NVI...S
how do i do that please help... it is very critical and i am very new to macro

My requirement is like this.. in rawdata file filter for region column"ASIA" and then filter in business group column "globalops" and then filter in supplier column"SHARP","SAMSUNG","SONY" as "VI" value for asia and then get the sum of this filtered value and then activate the Summary file and then compare column A:global ops under "VI" and paste the summed value in the subtotal column... next go again to rawdata and then all filters same, only in supplier column exclude "SHARP","SAMSUNG","SONY" to get as "NVI" value.. and then again activate summary file and compare global ops column and paste it in subtotal column for valueand paste it.... next again go to rawdata file and then keep same filters for "VI" and then all other filter same then filter for "CRP" in sourcontrol column of rawdata and sum the value for this filter then activate summary file then compare the value for column global ops "VI" and then compare for same "CRP" and corresponding to VI paste the value in "CRP" column value.. and same continuous for CPC,MRP,IRP,SMI...

Kindly do the needfulll.
It will be great help for me.. because i have to do the same for 120 files by commodity wise.... kindly help me.. it will be great help for me

Hello there,

I am new to Excel Macro Programming. I need help to create a template that will help out with compiling production records.

1) The first master worksheet will let the user key in the number of pallets and the rolls per pallet that the job requires.
Eg. " let No. Of Pallets = X. No. of Rolls/Pallet = Y."
2)By doing this, the macro will create X more number of worksheets labelled "Pallet 1.....X" with a template with Y number of rolls. I have attached the necessary template for a clearer picture.
3)After which, there will be another worksheet to compile all the data into another sheet to calculate the grand total of the values keyed in.

In the attachment,
there are 3 worksheets
a)Mastersheet > where the X Y and Customer's name is keyed in.
b)Default Template > which will be cloned X number of times with the Label
c)Packing list > a list to compile all the data from the X Pallets and Y Rolls to find the grand total of respective values (length,weight)

The basic idea is to make it easy for the machine operators to use parts (a) and (b) and for the administrative staff to easily obtain necessary information from (c).

It would be great if anyone could help me out with the macro programming or guide me in a good direction to start writing the code.

Thanks and Regards,
Yi Sheng Chan

I'm using the following code:

 PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), _      TableName:="PivotTable1") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I want to do is to use the table from worksheet "summary", then use it to create a pivot table in "sheet1". I also want the pivot table to work on a range of data that can be changing... And I need to do with without using the offset tool (it has to all be VBA code) Unfortunately I keep getting a type mismatch error at the line

 PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _     SourceData:=PRange) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be appreciated

I recorded a macro to create a pivot table. Every time the data file is updated, it has different number of data lines than the file i used to record the macro. What is the correct way to record a macro thus the pivot table will work with data files with different # of data lines?


Please help . . .

In the example shown I am trying to use a formula in column D to create a list of unique colours that apear in column A - As can be seen I am getting it right to show the unique colurs but I would the result to be like the info in column F ( Like filter unique ).

The formula I am using in the cells in column D is :

=IF(AND(COUNTIF(A4:$A$25,A3)=0,COUNTIF($D$2:D2,A3)=0),A3,IF(AND(COUNTIF(A4:$A$25,A3)>=0,COUNTIF($D$2:D2,A3)=0),A3,IF(AND(COUNTIF(A4:$A$25,A3)>0,COUNTIF($D$2:D2,A3)>0)," ",IF(AND(COUNTIF(A4:$A$25,A3)=0,COUNTIF($D$2:D2,A3)>0)," "))))

I am getting circular reference when I try to change the formula:


******** ******************** ************************************************************************>Microsoft Excel - Unique values in a list.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD3D4D5D6D7D8D9D10D11D12D13=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I've used the following code on a previous post to consolidate data from many worksheets in the same workbook into one master table.

Sub consolidate() 
Dim i As Integer 
For i = 2 To Sheets.Count 
Sheets("Sheet1").Cells(i, 1) = Sheets(i).Range("F31") 
Sheets("Sheet1").Cells(i, 2) = Sheets(i).Range("F33")
Next i 
End Sub
However, I now want to modify the code to create the master table from different workbooks, all of which are contained in the same folder. The only problem I have is that I do not have the skills to adapt the code.

I've had a look at similar queries on the forum, but I don't understand the solutions and so cannot get them to work.

Could someone please help?


Hi folks! I've been trawling the internet looking for some help to create a macro to do the following.
I have 2 tables in one spreadsheet that I need to combine to produce another table and run a large amount of calculations on. Before I had been using the INDEX function on my report sheet and referenced the two indexes on each table to bring back the data but this is produced a very complicated bunch of statements on the report sheet that I didnt want users to access. The tables will vary in size as the first table index is the each plot number in a development and the second table is the various interest rates that can be applied against each plot. The numbers will vary from each spreadsheet but I want to create a master spreadsheet that users can reference and change according to their data while the other sheets will be locked once the macro is run and the "master" table calculated. The first table has 24 columns and can vary up to 100 rows while the second table has 11 columns and up to 20 rows. The "master" table can potentially have 2000 (100x20) rows and (including calculations that need to be performed) 70 columns. The calculations are the same for each row but I need to be able to search the "master" table to reference the right information.
I have been trying to work on it but my VB for macros is as rusty as it comes! Basically I think its a nested loop statement to copy all the data from table 1 against the first row in table 2 and perform calculations needed in the master table, then run through through the process again for all the data in table 1 against row 2 in table 2. I'm on a tight deadline so any help would be greatly appreciated

Thank you to whoever has the answers, you boss should double your salary!

James (a non coder but how life changes that!)

I have data that is downloaded on a weekly basis, and am trying to create a
macro to create a pivot table for an end user who is not very Excel fluent.
Because the size of the data will change each week, I used the information at
Debra Dalgleish's web site:

However, when I try to run the Pivot Table wizard, and select the named
range as the range to get the data from, it tells me "reference is not
valid". I feel that I must be missing something, but have no clue as to what
it is. Can someone please help?

Thanks in advance!!

Tried to nest more than 8 componets but it did not work. Any help on
creating a table to reference it would be appreciated. Never done it
before. Tried the Excel help, and it was worthless.

Here is my original post that explains mo

mcr1's Profile:
View this thread:

I am trying to create a table with a fixed heading so I can keep a record of all monthly phone bill statements while being able to see and print out the most recent statements.

I've seen spreadsheets where when you scroll down, the column titles are fixed and you can scroll up and down through the figures.

How do you format the spreadsheet to do this? HELP Please!!

Tried to nest more than 8 componets but it did not work. Any help on creating a table to reference it would be appreciated. Never done it before. Tried the Excel help, and it was worthless.

Here is my original post that explains more:


Can you please help me with the below query please:

Need VBA code that creates summary table for the given input data and then create the chart for the summary table; data always does not remain the same; columns wud be the same but data points might increase; so while creating the chart it has to automatically pick up the range of the summary table; and then copy-paste this chart in PPT.

thanks in advance.

No luck finding an answer? You could always try Google.