Populating A Table For Gear Calculations.
Jan 12, 2010
I have a list of numbers (each corresponding to a different available gear). I have four columns and am trying to populate those columns with all the possible variations without using the same gear twice in any one row. Is there any way of doing this automatically or with a script or something? There are 16 different gears so it could be rather time consuming to input all variations manually.
The attached worksheet has what I am trying to do begun. The possible number of iterations or variations is apparently enormous and am hoping for an automated way to populate those cells.
View 9 Replies
ADVERTISEMENT
Jul 21, 2013
I have a table thats acting as a database analysing player data and I have a second table in a report sheet based on the database table. In my report table I have a drop down with the player names at the top and I want the data cells underneath to populate based on that particular players data from the database.
View 1 Replies
View Related
Nov 16, 2013
I have been trying to produce a spreadsheet that works out the correct gear and rpm depending on shift limits across six gears.
In column M I have all of the rpm values for 1st gear, which I require in column S until the shift limit for 1st gear is reached, now I wish to have the rpm values for 2nd gear (Obtained now from column N) displayed in column S up until the 2nd gear rpm limit is reached and so forth until 6th gear. I have tried an IF() statement down the entirety of column S but this only seems good for taking into account two columns at a time, whereas I require six all dependent on the respective gear shift rpm. Also the position of the shift rpm will change relative to other functions in the spreadsheet, so I can't simply have different IF() statements lined up appropriatly in column S.
So if my shift rpms are in column B I wish column S to read column M until M>B1 then it will need to choose values from column N until N>b2 where it then picks column o and so on.
View 6 Replies
View Related
Jul 5, 2014
Attached example.
I trying to populate table on sheet "1" from the training log sheet. Based on person name. Eg on sheet 1 i select Chris Williams and it will populate the table with courses he done from table on training sheet.
I tried and failed using vlookup and index match, but think that's what i need to be using.
example.xlsx
View 2 Replies
View Related
Dec 19, 2007
I am trying to link one table 1 with another table 2, and these are in different tabs.
In first table (1) I have to do some calculations, and in the second table (2) I have contacts (name, fax and contact name).
So I want from first table once I write the name of Trade (which I did with data validation to pick only the trades that are in table 2 from A1 to A50) to populate table 1 - columns B with Contact Name and Column C with Fax # and all these data should come from table 2.
I tried with Vlookup but the problem is that I can fill only column B and I need to fill column B and C in the same time?
Is this possible in Excel or the only way is to go to MS Access?
View 9 Replies
View Related
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.
*
D
E
F
G
H
I
J
7
Spreadsheet I have now
*
*
*
*
[code]....
View 3 Replies
View Related
Nov 3, 2013
Ok so I have made a self populating Data Table to show me which employee is on what shift that updates daily from our roster.
This works and it's brilliant, however I want to steam line my finished product further, but haven't been able to find an easy solution.
I have several employees per position and my data table currently self populates so that every employee has their own line. I want to populate it so that all employees with the same position all apear in 1 row across the columns under their current shift.
So this is what my end result currently looks like
Table 1POSITION
SHIFT 1
SHIFT 2
SHIFT 3
POSITION 1
EMPLOYEE 1
POSITION 1
EMPLOYEE 5
[Code] ......
This is how I want it to look.
Table 2POSITION
SHIFT 1
SHIFT 2
SHIFT 3
POSITION 1
EMPLOYEE 1
EMPLOYEE 5
EMPLOYEE 8
[Code] ......
We have upto 250 employees and upto 50 positions so you can understand why I need to stream line the table.
I can not change the roster as it is our orginal data as requires to be set up as it is. When adding a new employee into the roster I must enter them into there designated crew. "inserting a row midway down the original data".
If I use the CONCATENATE formula and add a new employee into the roster "half way down the original data" then it throws out what the CONCATENATE formula is looking up. It looks up the same cell but the names have all moved down by a cell therefore the populated information is wrong.
I am hoping to make my end result look like Table 2 even if it means adding another spread sheet in somewhere between the roster and the end product.
Realisticly I want a Pivot table that produces names not a tally of employees per shift.
View 5 Replies
View Related
Nov 2, 2012
I have two queries regarding pivot table.
I would like to include additional columns with formula in to a pivot table
I would like to get ride of the Items in a pivot table with Zero values without editing the source data.
I have attached a work file in to the below link, [URL]....
View 1 Replies
View Related
Apr 25, 2014
I am trying to get my data to be visualized on a monthly basis. A Pivot Table works perfectly in getting the right arrangement, except that it performs a calculation in the values summary field. Is there a solution that allows the Pivot Table to plop the actual text values that it is pulling from? Maybe Excel Pivot Tables aren't the right solution, so maybe another type of software might be useful? Or perhaps a change in the VBA script? The original table looks like this:
ID Description Date Code Building
10223 Package/Split 02/01/2015 ALRM1 A
10223 Package/Split 02/01/2015 PLMB1 A
10992 Pump Test 03/13/2015 ALRM1 B
10992 Pump Test 03/13/2015 PLMB1 B
10992 Pump Test 03/13/2015 ALRM1 B
11432 Inspection 03/13/2015 ALRM1 C
The way I would like for it to look is this:
January February March
Building ID Description Building ID Description Building ID Description
A 10223 Package/Split B 10992 Pump Test
C 11432 Inspection
In the original report, there can be multiple codes for the same ID (ALRM1 and PLMB1 for 10223). In such cases, Pivot Table works by summing up the values, therefore displaying the ID in just one row. I would like for it to do the same thing (spit a multiple row ID as one row) but display the actual text values of Building, ID, and Description as shown. I don't need the code in the table, but it's there in the original data simply to differentiate between the two IDs.!
View 14 Replies
View Related
Jul 9, 2014
I have a worksheet that tabulate from several sheets. I have need to a field populate from a table when the value is not exact and i cannot arrange a formula correctly. The cell issues is highlighted in yellow. When a value is placed in cell r2 i need it to correctly place the value in s2 using the table from the other sheet. I have attached for review.
View 3 Replies
View Related
Feb 9, 2010
I'm trying to condense a table of calculations down to a single array formula, but am getting stuck on one piece of it.
The table data is very simple, and can be in just two columns:
1, value
2, value
3, value
4, value
5, value
Where 1 thru 5 are time periods, and values are various numbers.
I want to perform the GammaDist function on each value. It requires a time period input, and in this case it is the 1 thru 5 in the table. So at the end of period 5, the formula for the first value would be
=Gammadist(5,x,y,TRUE) (x,y values not important here)
and the formula for the second value would be:
=Gammadist(4,x,y,TRUE)
The only thing that changes is the period number.
So, my goal is to write an array formula that will sum the GammaDist for each of the 5 rows, for all timeperiods (which is 5 in this case)
I can get this far:
={SUM(B1:B5*GAMMADIST(ROWS(1:5)-1,C1,C2,TRUE))}
But this passes 1 thru 5 to all rows, I only want 1 thru 5 passed to row 1, 1 to 4 passed to row 2, etc.
View 9 Replies
View Related
Dec 6, 2012
I am trying to populate a large data table with data sourced from multiple tabs.
Each of the tabs is, for the most part uniform.
They have column labels of "Invoice Number", "Schedule Dates," and "Amount". Their cell references are A6, B6, and C6, respectively.
Is there any way to congregate this data into one massive data table? It doesn't matter the order of the data table. I will be using sort/sumifs/pivot tables to analyze the data.
I cannot copy and paste as there is too much data that changes on a daily basis.
View 1 Replies
View Related
May 6, 2013
I am attempting to populate multiple rows in one column with data from another table. I need to get the correct street address using multiple values, i.e. first name, last name and city, as some of the names double up.
Is there a way to do this? I have pasted below an example of what I need done as reference.
Last Name
First Name
Address
[Code]....
View 4 Replies
View Related
Sep 5, 2013
I have built a model which has approx 33,000 different combinations (output) based on user selection. There are 15 different options for user selection which gives rise to the number of outputs (2^15 -1) = 32,767.
I have inbuilt a data table that gives the output for all of the 32,767 scenarios. However, it seems like for each change the calculations takes forever and I am not sure when the calculations are complete. Saving takes forever and stuffs up excel. The numbers keep changing in front of my eyes. The file is 10Mb big/small.
View 3 Replies
View Related
Mar 21, 2014
I'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.
View 5 Replies
View Related
Jan 21, 2014
I am looking to create a table with ranked projects from a different tab that is able to pull data from the same tab and change based on value updates. It is difficult to explain, so I have attached the file below.
What I am looking to do is pull the ranked projects from tab #7 - Scoring to tab #5 - Final list. Each project has a unique ID#, but a lot of the columns I need populated are not unique. I tried using a combination of arrays, but it always returned a #value into the cells.
P.s: In tab #5 - Data sheet, the formulas in I5 and P5 work, but are pretty ridiculous, and I'm pretty sure there must be an easier way. I was looking up values from the 8-budget tab to try and determine which group column H values were from. The only complication I was having was that a project can be allocated to either a functional unit, or group so I need to have the "group" total as independent, not just as a sum of the functional unit totals. Like I said, these formulas work,
View 4 Replies
View Related
May 8, 2013
I have a problem as I need to present data with different data sources. I'd like the calculations to be as "automatic" as possible. The data consists of countries and regions and their sales pipe. The pivot table doesn't give me all the data that I need to present so I need to add extra columns with calculations and data from other data sources.
The problem I meet is that the 'total row' within each group needs to be reflected in my columns as well (F:G) It works as long as the data I've imported has the same amount of States within each group of Contry but if the number of states differs with the newly imported data - my "manual" columns are wrong.
(A;B;C;D;E;F;G;H)
Country;State; Red; Amber; Green; Quota; Total PV; Coverage;
I've created a Pivot table on the first 5 columns [Country; ...; Risk] (A:E)
The following columns are manually entered based on the pivot (F:H)
Quota (data source from another sheet)
Total PV (a sum of Red; Amber; Green in the Pivot)
Coverage (returns % of TotalPV/Quota)
For each group Quota and Total PV needs to summarize the above data within the group. I've 'hard coded it' today (SUM(F3:F5)) - F3:F5 can as well be F3:F8 or F3:F4 depending on the imported data. I have 8 different Countries (groups) with different amounts of States and 3 different sheets for each Region of countries so I need this summary to be automatic based on what group (Country) it belongs to. How can I make the calculation different so that it's dynamic as well as the Pivot table? Today it's not dynamic and it needs my 'hands on'.
Quota =IF(ISNUMBER(SEARCH("total";A6)); SUM(F3:F5); IF(ISNA(VLOOKUP(B6;Quota!B:F;3; FALSE));" ";
IF(VLOOKUP(B6;Quota!B:F;3; FALSE)=0; " ";(VLOOKUP(B6;Quota!B:F;3; FALSE))))).
View 1 Replies
View Related
Jun 24, 2014
I have an issue whereby I have thousands of lines of data. I need to bring back the "previous month" worth of data (along with some additional information) but not sure how to go about this.
The attached shows the example file. I need the following :
1. Take the data to populate the "Populate" tab
2. Using Column F (Invoice Date Created) to only bring back the "previous month data" - in this case it would be May 2014
3. The following formula is what I have currently for Column B of the "Populate" tab: =SUMPRODUCT(SUMIFS('Data'!$N:$N, 'Data'!$M:$M, "Invoice Payment Processed", 'Data'!$A:$A, A2))
4. The above formula brings back all the values - I only want the previous month.
5. I am then not sure how to calculate Column C - which is a count of the amount of invoices that make up the total
View 3 Replies
View Related
Aug 6, 2014
For my project , I am using Excel as a frontend and Access as a backend. Now, I want to write the code in workbook_Open event that will copy data in Column L,M,N,O from Access table. The query should select data from Access table where Date=Todays Date. All the Queue Names and corresponding Queue Numbers are stored in Sheet2 of the attached workbook. In Access table , there are three fields named as Type,Type1,Type2 which make up Queue numbers.
In Sheet1 , There are Queue Names and we have to store Total Batches,Totl Envelopes,total Documents and total Pages for the corresponding Queue Names in Sheet1. SO for this we need to check the Queue Numbers of the Queue names in SHeet2 but in table Queue Numbers are equal to Type & Type1 & Type2.
See attached workbook : WBExcel.xls
View 2 Replies
View Related
Apr 26, 2014
I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.
On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.
On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".
View 2 Replies
View Related
Mar 27, 2009
I have 2 sheets - one called 'Lookup' that has a table of recipes in the rows and ingredients in the columns. The cross reference of each is the required quantity of that ingredient, if any (obviously..). Both rows and columns are sorted alphabetically.
In the second sheet - Recipe - I have a subset of the recipes (e.g. 'Cakes') along with the applicable subset of ingredients. I've input these by hand, so I assume they can be referenced directly in the function. What I need to do is grab the quantity out of the table for each recipes ingredient. E.g. A6 will have the recipe 'Jamrolypoly' and H1 will have the ingredient 'Jam'. H6 needs to return the value from the table in Lookup.
I'm still (very) new to this but looking around I found the following which seemed to describe the function I am after:
=VLOOKUP(H1, Ingredients, MATCH(A6, Ingredients, 0))
(Ingredients is the name of the table in the Lookup sheet)
I then need to be able to use the returned values in separate equations on sheet 2 to work out costs. This bit I've done already but I've heard that some of the methods of data retrieval return 'N/A' if there is no value, and that would not allow any subsequent calculations using the value.
View 4 Replies
View Related
Feb 9, 2010
if you look at attachment, in Cell J2, if cell I2 is greater than 2 minutes but less than two minutes, the I need the value of I2 in Cell J2 otherwise 0:00:00 then K2 between 3 & 4 Mins and so on.
View 5 Replies
View Related
Jan 27, 2010
I am making a spreadsheet that tells us the following information about prints that we do:
View 6 Replies
View Related
Feb 2, 2010
I have attached a sample file. All the data is inserted into this file via a text file, except one column "G". Someone in this forum was kind enough to help me in being able to include the city name 'G' to a parcel number 'A'. However, the page is constantly trying to complete 'Calculations' and won't let me do anything without first hitting 'Control Break'.
This is fine except that when I try to save the file into .txt, I don't have the option of 'Control Break' and I have to end up closing the file. As the 'real' file has over 100,000 rows, nothing happens very fast. I have tied changing the 'Calculation Options' but that doesn't seem to change anything.
View 7 Replies
View Related
Jul 11, 2012
Is it possible to have an input box for a range of calculations and then in my formulas set the range to anchor + variable ?
Something roughly like:
Range = 20
Product( F13:F & Range_Variable_Cell_Value)
View 2 Replies
View Related
Mar 19, 2007
I'm working working with this spreadsheet that is moving incredibly slow. Every time I enter anything, it takes anywhere from 10 seconds, to a couple minutes to calculate and let me proceed. It is a pretty big file (4.60 MB), but I also work with another spreadsheet that is a little smaller (2.95 MB) that has never taken more than a fraction of a second to calculate anything. What could I do to spead up the spreadsheet?
View 9 Replies
View Related
Sep 15, 2007
My problem is the following:
I'm trying to model straight line depreciation over 5 years for certain investments. This could for example be done like this: ....
View 9 Replies
View Related
Sep 2, 2008
Does anyone know of a site or some code that can make changes to add some items to the status bar calculations?
Such as right now you have Sum, Count, Count Nums, Max, Min... just to name a few.
I'd like to be able to add some other ones down there if it can be done. I'd like to count #N/A's or possibly sum only positive numbers? I can do it with a quick keyboard shortcut and message box with a macro, but I'd like to just be able to highlight a range and have it show up like sum or the others do.
Just thought I'd psoe the question.
View 9 Replies
View Related
Feb 21, 2009
im in the process of designing a userform but it needs to have some calculations in it, and im lost on how to do it if anyone can help
ie
in textbox1 there is a value of 5 and in textbox2 there is a valuve of 10 and in text box 3 i want to appear textbox1 multiplied by textbox2 so 50 should appear
this is a basic example but as soon i have mastered that i can adapt everything else to my userfom
will the above be done automatically as soon as i enter values or would i have to "make the calculation" via a button or something
and also i want to have a enter button or something like that that copies the data that i have put in the various text boxes to the excel spreadsheet and clear the userform cells
phew thats best i can explain any questions just reply to this post as per the norm
View 9 Replies
View Related
Feb 21, 2010
I have inventory... with starting product at a certain cost, received product at a new cost, and used product. I want to assume that we are using FIFO.
What I need is a total cost (what I have paid) for what is sitting in my freezer.
Column A is START (5)
Column B is RECEIVED (6)
Column C is USED (2)
Column D is END (9) or (A1+B1-C1)
Column E is OLD COST ($12.20) cost per unit of those 5
Column F is NEW COST ($13.50) cost per unit of the 6 i got in
So I need in Column G a FIFO formula for total cost of what I have in the fridge.
View 8 Replies
View Related
