Creating Tables Correctly To Get Data
Aug 12, 2006
I've attached what I've done so far. I'm trying to figure out a way to capture everything. For instance, instead of using separate boxes for the different groups, isn't there a way that I can put the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend, and list the different units, and different groups. I'd like it to be sort of like the very last box where I have the different sessions and the different groups in there, but I'm not sure where I could add the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend, because the last box only formulates the percent attendance for each session by the different groups. I need it to also formulate the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend. Is there a way this can be done without using separate boxes for each group.
View 3 Replies
ADVERTISEMENT
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
Jul 1, 2013
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
View 2 Replies
View Related
Mar 22, 2013
how to do this, but I know it can be done.... I want to use one drop down list created via the Data Validation 'List' Criteria to then lookup data based on the selection made in this list...... this will require a number of reiterations to get to the final result......
So:
Drop Down No:
1. List: Department Function:
2. List: Sub Function List Based on Selection from 1.
3. List: Job Code, Title and Pay Grade based on Selection from step 2
how to ensure that we are able to minimise the overall workbook size due to the complexity that is required here as this is just the basics.... this will need to applicable further once this basic requirement has been fulfilled.
View 9 Replies
View Related
Sep 17, 2011
I'm working on a national survey with valuable data from students. The plan is to turn around, 100 different reports to the departments with their respective students.
The issue that I have is creating multiple tables from multiple sheets in excel into a word table, any way of automating it.
What the tables look like in excel:
What I want them to look like in word, with a chart if possible:
I realize that I can just copy and paste easy table into word from excel and with a few clicks of a button, get what I want. But i'm looking for something to automate and simplify this process. There are about 300-400 variables that I need to crosstab with certain demographics.
If it matters any, I bought a software package (Q Market Research) but it doesn't make the tables look exactly how I want them to look. The data is from SPSS, those tables are flat out.
View 9 Replies
View Related
Apr 17, 2014
sample worksheet Antligen.xls
I have a spreadsheet that I need to add new rows everytime I have a new client. I a formula in the N column and a formula in the F column that I would like to automatically insert itself into the new rows.
The problem is this: I have arranged my table so that I can look at clients alphabetically, and according to other date... BUT now it wont let me insert a new row when I have a new client!!!
Is there anyway of keeping it so that I can insert a new row into my table, but preserve all of the formulas that I want automatically put into both the F and N column?
Attached is the sample spreadsheet.
View 1 Replies
View Related
Apr 1, 2009
I am having a lot of trouble with pivot tables in Excel 2007 VBA. I am trying to create pivot tables using macros (connected to buttons the user can press to create the pivot table) - please don't ask why, but i need to do this!!!
I used the record fuction in excel 2007 to produce macro code which will produce the required pivot tables when the user presses a button.
Unfortuanately the coding seems to work fine when i have one pivot table in a file but breaks down if i record code to produce another pivot table.
I have attached some code below (which was produced by the record function) and is intended to produce 2 seperate pivot tables (the macro submacro2 produces the 1st pivot table and the macro submacro4 lower down the page produces the 2nd pivot table). I have also indicated the point in sub 4 where the code breaks down - basically submacro4 just doesnt run!
View 14 Replies
View Related
Oct 29, 2013
I am trying to solve in VBA how to create pivot tables in new sheets using objects instead of relying on the sheetcounter, which errors out.
I found an old Tip on Mr. Excel: Excel Create and name a new worksheet with VBA
But I can't figure out how to apply the object to the rest of the script. Here is what I have so far:
Sub Macro2()
'
' Macro2 Macro
'
Dim WS As Worksheet
Set WS = Sheets.Add
'
[Code]....
View 1 Replies
View Related
Jun 28, 2013
I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.
My department processes payments, both for internal clients and external clients. My Payments table looks like:
Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity
Additionally, I have a table for Mail Opening, which looks like:
Date | Employee | Operation | Quantity
My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.
View 3 Replies
View Related
Jun 28, 2012
I have created the code below to create a Pivot Table using VBA, so that it populates from a list box option.
I would prefer if it didn't show any subtotals and was in a tabular form. It keeps dropping off at that point for each row field.
CODE
Sub trail()
'
' trail Macro
'
'
Dim wksPivot As Worksheet
Dim wksData As Worksheet
Dim pc As PivotCache
Dim PT As PivotTable
Set wksPivot = Sheets("PIVOT")
[Code] ......
View 2 Replies
View Related
Sep 14, 2006
Is there an way to make an YES and NO button where when i click YES it pop-up a box asking for confirmation on the action and if you confirm it then the data wrote into A1, A8, D1, D8 will be saved into "Product Exit" worksheet then the data into A1, A8, D1, D8 will be cleared.
aswell as a NO button but the NO button will save into Product not sold IF confirmed TRUE and then cleared and if confirmed FALSE will just clear A1, A8, D1, D8. if it cant be done with the confirmation can it be done without it example ?
View 3 Replies
View Related
Jul 12, 2009
I am putting together multiple worksheets with dumped data that should sort themselves by the press of a button. Each entry has a 'code' and a value and they are sorted by the 'code'.
At the moment i have the first worksheet sorting correctly and i am trying to program the second worksheet to sort data into the existing worksheets if they exist or create a new worksheet if the data doesn't have it's own worksheet.
Here is the code i am working with.
View 12 Replies
View Related
Mar 26, 2014
I was given a task to build a userform and all the info collected from the userform will then transfer to a worksheet named 'Promotion'. I was copying the code from last year's UserForm and with my 0 VBA background.
With the below code (I just extract part of it), the ideal case would be to transfer data to Cell A4 (which is the first blank cell on the worksheet), and when a second form is recored, it will start from A5, so on and so forth. But somehow, the code transfer the data to Cell A1001, and the columns don't match with the info either.
Private Sub cmdAddPromo_Click()
Dim RowCount As Long
Dim r As Long
Const LastRow = 2000
[Code]...
The other thing is I don't really know why there is 'Me.' before each common buttons...
View 9 Replies
View Related
May 4, 2006
I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?
View 2 Replies
View Related
Apr 30, 2013
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
View 8 Replies
View Related
Feb 18, 2009
Let's assume, starting at A1, that i have table (8c x 30r)
and lets assume that in each of the columns i have 5 cells filled in with data at random.
What is the best way for me to produce a list of the data in each of the columns without having to go in and delete out all the blanks myself? I.e. i want to reproduce the table but without any of the cells which do not have any data in.
View 12 Replies
View Related
Jan 25, 2012
I have been trying to find a way to consolidate data in two tables, but can't seem to locate a good response. I have been toying with EXACT, VLOOKUP, HLOOKUP, MATCH, and SUMPRODUCT, but none seem to work for my issue.
Two Files - Call them acrendo.xlsx and office.xlsx
Both have a single sheet and contain first name, last name, and date columns. Office also has other information that I'm trying to merge into the corresponding row of acrendo.
Basically, I am trying to take the combined first name, last name, and date column from a row in acrendo as a single record, look for an exact match in office, and if it finds one, return the corresponding values in Coumns D, E, F back to acrendo. Someone suggested using the following sumproduct formula:
=SUMPRODUCT(--(A2=[ofc.ally.xlsx]ofc.ally!$A$2:$A$9329),--(B2=[ofc.ally.xlsx]ofc.ally!$B$2:$B$9329),--(C2=[ofc.ally.xlsx]ofc.ally!$C$2:$C$9329),E:E)
This always returns a value of 0, even if there is data in the matching row to be copied over.
One small complicating matter is that there may be duplicate entries in office (i.e. one row in acrendo may match to four rows in office). I can cleanse that if necessary, or preferably, if multiple matches are found, it would simply insert the additional rows into the acrendo file.
Also, is there a way to go through the acrendo file and check for duplicate records and delete them (e.g. if there is the same last name, first name, and date, delete the duplicates).
Can't do this manually as I have acrendo has almost 18,000 rows and office has almost 10,000 rows.
View 1 Replies
View Related
Jan 12, 2014
I am trying to merge data from 2 tables into a master sheet. Column goes like this 1 = customer ID, 2 = allocated number, 3 = age
The first table has around 2000 entries and 2nd table has about 1000, and they both contain some overlapping entries. I wonder if there is a simple way to go around this rather than manually looking at each individual entries as I will be more tables to fit in later on.
View 9 Replies
View Related
May 24, 2007
I have recently discovered the "data table" tool on Excel and have found it to be extremely useful for sensitivity analysis. The Excel help describes how one can create 1 and 2 variable data tables. Now I would like to create a data table using three variables, however. (I would like to vary three different variables and see what effect they have on one formula.) Is this possible with Excel? Can pivot tables be used this way?
View 2 Replies
View Related
Jan 21, 2008
I need to check data from two tables(A and B). They are supposed to match each other and my job is to check if there's error in it. Each row contains several criteria, and all criteria need to be matched. I need to give a report on the info that
1) contains in table A but not in B
2) contains in table B but not in A
3) contains in A and B but some of these criteria do not match..
I have basic knowledge of VBA. Forget to say, they are in different order. And even after sorted, the name may still unmatch with each other, so can not use formular like "=Sheet1!A1=Sheet2!A1"
Example:..........
View 3 Replies
View Related
Apr 16, 2013
I have a pivot table i created to pull in data in a tabular format so i can have all of my data fileds listed in a columns. On another page I am doing a dashboard that is using Countifs to pull in data to my report. What I am running into is the countifs are only working when i filter my pivot table to a certain value that matches, otherwise it will not pull the counts in..
View 5 Replies
View Related
Dec 22, 2013
I have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
How to use the data tables function under the what-if analysis.
My sheet and calculations are all set up
But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV.
Ive done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
View 1 Replies
View Related
Oct 17, 2009
I've encountered is the spreadsheet attached. Basically I am trying to create a "complex" array (at least it's complex for me lol) to basically extract some values from tables and put them in one together. The array I thought would work is on the table with BRIGHT YELLOW BACKGROUND, whereas the table that could do the same work but more "manually" is with a RED border around it. I know I can do it manually as the red border table shows, but I've been trying all day to make it work with a complex array.
View 4 Replies
View Related
Nov 11, 2009
I need to find the most frequent number for each name in a large datatable, eg. Name1=12.
ColumnA / ColumnB
Name1 / 12
Name1 / 11
Name1 / 12
Name2 / 100
Name2 / 105
Name2 / 105
Name2 / 98
Name3 / 14
Name3 / 14
Name3 / 2
NameX / ..
Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.
View 10 Replies
View Related
Jan 10, 2013
I need to put together a number of worksheets, each worksheet is to record project information that's happening at our production sites and all the tables are uniform in design. There also needs to be one 'master sheet' in which all the information added on the lower sheets is automatically added.
So basically I need to have four tables on separate sheets that feed information in to one table on a further sheet.
View 13 Replies
View Related
Aug 30, 2013
I'm having a problem with data tables. Actually, not with data tables, but links to data tables. Calculation is set to be automatic and values within data tables update alright, but links to those values do no update. So you may have a certain value in one cell within a data table, for example cell B5, and a different value in another cell which is just a link to cell B5.
View 5 Replies
View Related
Dec 4, 2013
I have multiple data tables that are linked to a site. My issue is that there are breaks in the data every 15 cells with a row that shows the column categories. I locked my top row so I don't need the recurring column categories at all, but when I delete them and refresh the data they reappear.
How to delete these rows? Could I possibly run a macro that deletes the rows upon hitting refresh? Or is there a simpler way?
View 1 Replies
View Related
Mar 10, 2014
I have a VBA code that goes to a specific Folder, grabs data from an Excel file, and then puts (data) in another Excel file (template with VBA) file as "Raw Data" tab.
Then my VBA refreshes 30+ Pivots from that "Raw Data" tab.
Now "Raw Data" is stored together with 20+ Pivots in one Excel file.
Challenge: Is there a way to keep my "Raw Data" in the external Excel file and simply refresh my Pivots by only "pointing"/linking that source?
View 3 Replies
View Related
Apr 4, 2009
I have 2 columns of data:
Column A is SKU #
Column B is Bin Capacity
I would like to take each SKU and its corresponding bin cap and arrange them into a 16 column and 9 row table on another sheet.
For example A1, B1, A2, and B2 of the old sheet, should become A1, B1, C1, D1 of the new sheet. Also I need there to be a line gap between each table.
Anyone know how to do this?
This is what I got so far:
View 10 Replies
View Related
Oct 18, 2009
I have a Excel Table that will be sorted/identified by the first column of every row. In the Table, there are numerous columns calculating values for each unique item across the row. This will be a large inventory table. The calculations will adjust based on a unit value of 1, and if the unit value is changed calculations go through the rows
I want to accomplish several things:
1) Pull data to another portion of the sheet based on,
a) Selecting a unique item that comes from a drop down list based on unique item identifier in the table
b) pull values related to the unique item and put in cells near the drop down list (I tried an =if function but did not have any luck
=IF(G1=ZillaRac[Item Name],ZillaRac[MSRP],"MSRP Not Calculated") but it resulted in #value!
2) If possible by selecting my unique identifier, insert a unit multiplier that would go into the Excel Table and adjust the values calculated in the table to be brought back up to the cells in part 1.
View 4 Replies
View Related
