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 RepliesPart 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 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.
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
[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 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 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?
I want to put a drop down list in Cell D3 with the list of names from column B. There are 14 different names, but when i try and create the list it gives everyname from Column B.
Also once the drop down list works i would like the table to be populated with the managers supplier, so if Dan was chosen in D5, Suppliers 1 to 9 would appear below D3.
I have a dynamic set of data, loaded from a server. The columns are FIXED. I will attach a sample of how the data looks.
site_code
report_goup
asm_week
[Code].....
I don't know how to attatch a file here, but the first row is the title row, under it are the values. The columns here are fixed. What i NEED to do, is somehow create something that will make a seperate graph for every different PLATFORM , showing the FPY chaging over time. Time here is the weeks which will always be a rolling 12 weeks. I'm thinking that i need to use MACROS to do this, but i'm not sure where to start.
Recently our IBS provider change something in their data. As a result after the export I can no longer use
HTML Code:
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion)
I had to change the SourceData to:
HTML Code:
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
Since I have done this when I get to this statement
HTML Code:
Cells(FinalRow - 1, FinalColumn).ShowDetail = True
It no longer creates "Table1". Which sends a Subscript out of Range error on this statement
HTML Code:
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=10, Criteria1:= _
">0", Operator:=xlAnd
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=12, Criteria1:= _
"
I have the code listed below where i keep getting a compile error. This is from a You Tube (Pivot Macro - Episode 1211 - YouTube) episode 1211. why this would not work? Basically trying to set up a macro that creates a Pivot Table that will always have the same number of columns, but each set of data will have different number of rows.
ActiveWorkbook.PivotCaches.Create(SourceType: xlDatabase, SourceData:
DataSheet & "!R1C1:R" & FinalRow & "C37", Version: xlPivotTableVersion14).CreatePivotTable
TableDestination: NewSheet & "!R3C1", TableName: "PivotTable2", DefaultVersion: xlPivotTableVersion14
I would like to create Bill of material from single table. I need to select multiple parameters, to expend it till the lowest level, so I will try to explain:
I have one table (ODBC) and there's all data we need. Parent number col A, Item number col B and quantity col C. First level I select main Item number A1234 (this is only thing that I should choose, everything else should be automatically), I get table with all items that are parent A1234 (let's say 10 items). Now I need to look again one level lower in same table for items that have Parent item in list of those 10 items listed earlier (let's say 30 items) and multiply their quantities with quantity of their Parent Item (total qty could be in column D). Then one level lower for items with parent items in those 30 and so on and so on. So when I choose main Item I would like to get table like below (take notice that real table has over a 100.000 items, but I want to show only Bill of material for the main item till the lowest level).
Parent Item
Item
QTY
Total QTY
A1234
B1111
5
[URL] ...
I'm flexible and ok to use VBA, SQL, Excel functions, multiple tables (how to select multiple parameters??)
In creating a macro and creating a pivot table as one of the first steps, I get this runtime error in visual basic:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"original working!R1C1:R1000C13", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet4!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion14
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out.
I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook.
I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
I am trying to create a pivot table using multiple sheets. I looking for some code that will replace the "consolidated ranges" in the Pivot Table Wizard. I am looking for code because I am writing a macro that will create a different number of worksheets in the Workfile, depending on which dataset I use. i.e File 1 may have 75 worksheets, whereas File 2 may have 120 worksheets. '
The ranges on each of the worksheets will be the same. Range("A2:Av48")
I am creating 10 pivot tables for data in one worksheet and putting them in a separate work sheet. i used the macro recorder but the cod eit worte stop at this line ...
View 9 Replies View RelatedSee the attached:
1/ Need to be able to populate a table with the following data (or as illustrated in ' Sheet 1'.
Company Name, Year, Manufacturing, Non-Manufacturing
This data will need to come from the lists for each of the variables as opposed to from the table, as this will be hidden from the user. Also, more company details will be added over time.