Input Data Looking Up Other Tables - 3 Variables?
May 24, 2014
How can Excel input automatically the "total price" in the first table after I enter "type", "width" and "height" and looking up the other reference tables (below). The referenced tables will have fixed width and height but in the input table will be entered any number within the minimum and maximum range. So the idea is to get the price of the closest highest combination of "width" and "height" on the referenced tables and apply the price:
Input table (I will input "type" by drop down menu, and width and height)
type
width
height
total price
[Code].....
View 6 Replies
ADVERTISEMENT
Oct 28, 2009
I have a spreadsheet where raw data is entered ("RAW_DATA") for each month it is collected. There are 7 questions that are tallied and each of those responses are transfered to a separate data table for analysis (7 individual tabs within the spreadsheet). I'm currently manually data entering the values from the raw data tab to the other seven tabs.
I would like to have VBA code that could transfer the data from one month ("RAW_DATA") onto the other 7 tabs based on the date and facility identified in the raw data tab. I have not attempted the VBA since I do not know where to start with searching on two variables.
View 2 Replies
View Related
Mar 20, 2008
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)
I plan to use command button " CommandButton1" on input sheet to start the calculations
Here's what I want to do after pressing the " CommandButton1" :
1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.
2.for empty cell in the input table paste 0 (zero)
3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output
4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1
5.Do this for all cases (in actual workbook there will be more than 100 cases)
View 9 Replies
View Related
Jul 12, 2011
How do I set the datatypes and columnwidths as variables? I get invalid procedure call or argument. Will import different text files and set dt and cw based on names.
Dim dt As Variant
Dim cw As Variant
dt = "2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2"
cw = "7, 7, 5, 2, 7, 16, 3, 15, 11, 9, 9, 9, 7, 3, 3"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;B:BOMOEBTRfro1.txt", Destination:=Range("A1"))
[Code] ........
View 8 Replies
View Related
Jul 27, 2009
In column H: =IF(ISERROR(L4-J4-K4),"",IF(OR(K4="0", K4=""), L4-J4, L4-J4-K4))
So, if formula is going to come up with an error, I don't need to know. If it's not an error: L - J - K. Except that sometimes there isn't information in J or K, and the formula won't calcluate L - 0 - 0, unless I physically enter in zeros.
=IF(ISERROR(L4-J4-K4),"",IF(K4=""), L4-J4, L4-J4-K4))
and
=IF(ISERROR(L4-J4-K4),"", L4-J4-K4))
I don't want the people using this to have to enter in a plethora of zeros in order to force the calculation. What am I doing wrong? I've attached workbook with the relevant information in it. You can see in the middle that if there is nothing entered, it won't work right.
View 5 Replies
View Related
May 15, 2014
On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:
Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)
The allocation percentage is based on the below; anything outside of this should result in "Other".
Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%
Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%
Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%
Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%
View 1 Replies
View Related
Apr 13, 2014
i have a macro which takes the results generated and puts them into a table and filters the data so that zeros does not show and arranges the table from largest to smallest. Now i have a problem where i need to select the power, fuel oil consumption, weight and area from one table and the other power, fuel oil consumption, weight and area from the other table and combine them together so that i can have all possible combinations of the data, but i don't want the zeros to show in the combinations. i need the combined data to show on a separate sheet. The data of table 1 varies with rows as shown:
The Data of Table 2 varies with rows as shown:
I would like to achieve a result like this in the following columns and rows without having to input them manually:
View 1 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
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 5, 2009
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
View 2 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
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
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
May 8, 2013
Here is what i want:
It's something like updating data by merging 2 tables. New names adding in end of table but peoples who has new mails need to be changed. Clear explanation in attached image.
People 1 and People 2 are same persons in both tables.
View 1 Replies
View Related
Aug 20, 2008
I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
View 5 Replies
View Related
Jul 31, 2006
I want to a macro or a function that can search for the value which corresponds to a cell and copy it to another cell. please refer to the attachment for more detail.
View 9 Replies
View Related
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
View Related
