I have attached a demo spreadsheet to define the problem.
I wish to populate column K (Risk Rating) with data retrieved from the table, based on the corresponding information from Columns I & J.
e.g. I5 + J5 = Short term illness or injury + Unlikely. This corresponds to cell E9 (11) in the table so required response in K5 would be 11.
Help Book1.xlsx
I have an engineering spec table. Down 12 columns I have height in metres of a post and across the rows I have square metres with 5, 10, 15, 20 and 25 being the sizes.
The engineering spec of the post type is in the table. There are some duplication of post types so post heights of 1m, 2.1m, 2.4m, 2.7m, 3m, 3.3m and 3.6m are the same, then they change. Its a similar thing in the other column headers for 10sq.m 15sq.m etc.
I need a formula so that if the post height of 3m was chosen and the sq.metres was 15 then it would tell me the post type from the table. Or if the post height was 4.8m and sq.metres was 25 it would tell me.
The post type is an engineering code of numbers and letters with no spaces.
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
comp1.PNG
Attached is a screenshot. Basically I need to know that if PersonA has say 13.74 CPH and $776 DPH - what the incentive would be ($575)...
I have a report to fill that looks like this:
Table 1:
HEMANITOBAMANITOBA SK12
I want to fill it up with data from this pivot table:
HEIntervalManitobaManitoba SK11-10522-8423-8224-6125-5826-6427-6828-5929-53210-47211-53212-63221-7522-8023-7924-7825-7726-7627-7728-8229-86210-86211-86212-722
So basically what I want to do is for example from Table 1: If I want to find the what goes in cell in B2 (1, Manitoba) then I want to go to the pivot table find 1 which is in the first column(HE), there are 12 intervals to each HE(as seen in column 12), I want to take the average of all the HE with 1 corresponding to Manitoba (the values in Column 3) and to be put into B2 in Table 1.
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies View RelatedI'm trying to figure a formula that provides a numerical value when certain criteria are met across a range of cells. The criteria are numerous though and here is where I'm having trouble.
For example, the formula needs to look at Cell A5, then at a range of cells (C5,G5, E5, I5) and depending on the value of those cells provide a predetermine result in Cell K5.
For example, IF (A5 = 'A', and C5=1 or G5=1 or E5=1 or I5 = 1, then P5 must = 15), or if (A5 = 'A', and C5 or G5 or E5 or I5 = 2, then P5 must = 10), or if (A5 = A, and either C5 or G5 or E5 or I5 = 3, then P5 must = 6), or if (A5 = A, and either C5 or G5 or E5 or I5 = 4 then P5 must = 2), or if (A5 = A, and C5 or G5 or E5 or I5 = 5, then P5 must = 1)…….this needs to be duplicated for a list of possiblities.
I'm making a schedule grid has varying start & stop times. I've made a table so the people using it can check that there is overlap when they are scheduling themselves and the members of their shift.
The rows have the employees and the columns has the hours, (one row for each hour in the year 2013)
Each employee has a 1 under each hour where they work and they can easily see where their shift overlaps onto the next persons and by how many hours. I also have a row on the bottom that checks how many people are on at any given time so everyone can check that we have a minimum number of people on and a maximum number as well.
As it is pretty difficult to look at this grid as it is, i made a 2nd table which we could print to see which days we shoud come in and what time. (with each column being the day/ date and the cell having the time frame one comes in.)
The problem i have is that if any adjustments are done to the first table, (with the hourly columns) it isn't reflected in the 2nd table.
I've created a LOOKUP table with each hour/ shift, but i do not know how to have lookup check the starting hour, the ending hour, and what date it is.
I was considering placing a look-up formula in each cel for each employee for each day for the entire year, i just don't know what the formula should look like.
I have a spread sheet with multiple columns. I need a formula to look at criteria from three specific columns in the spread sheet and spit out a description from a key I created. Here is an example.
This is the key I created:
Record Kind
Group
Group Name
Category
[Code] .....
The spreadsheet has the same columns so I need a formule to look in the spreadsheet and if it sees each combination as in the key to spit out the category name from the key.
I have a cell that contains a long string of text.
I want to be able to lookup in it to see if any word from a list is in it, if it is to return text dependant on which word is in that cell.
Seems like it should be easy but looking up the multiple values is making it difficult.
I've attached a sample of the data I'm using.
I have two spreadsheets (the samples for which I have shown side by side in Sheet 1 of the attached file).
Spreadsheet 1 is about 30,000 rows and too large for me to change the formatting and structure.
Spreadsheet 2 is the output I need and the format is required by other stakeholders.
In spreadsheet 1 I want to sum quantity in stock for Type 1, Type 2 and Type 3 for each product and allocate it to spreadsheet 2 according to the month in which the product expires. For example, there will be a total of 92 units of product 413302 which will expire in Nov, 2014. Therefore I want 92 to be placed in cell N6 of Spreadsheet 2.
Unfortunately the product number is not unique - there are multiple sub products in spreadsheet 1 but they all have the same quantities of stock. The sub products are referenced in other parts of the report so I can't consolidate by Product Number. This also prevents me from using the SUMIFS function as it will duplicate the number found in the sub products.
What I need, perhaps in a combination of functions, is to find the first instance of product 413302 in Spreadsheet 1 that is expiring in Nov 14, sum the product types and give the result in cell N6 of Spreadsheet 2.
i need to track tyres, i'll have to use the Serial Nos. of the tyres inscribed on them. What i require is to lookup the serial no. from Purchase/issue sheet to my Maintenance sheet. To do that there'll be 3 criterion to match to get the correct Serial No.
In my Purchase sheet suppose if a purchase a tyre and issue it to some vehicle on 11th September, i'll have its first inspection for maintenance after mebbe a month (no fixed intervals). So if i check on 10th October the lookup should actually find the greatest date lesser than the inspection date and keep it to that. I know that the lookup function would get the last unit in case of duplicate entries but that becomes dynamic and the serial no. would change once theres another entry matching the 3 criteria. And dat would mess up my previous record.
Attached is my sample workbook. There would normally be 600+ employees with multiple rows per employee. I would like Cell O3 in the Premium Calculation Worksheet to look at the Premium Contribution Report, and if Row A contains the employee number (A3) AND row C contains "H&D" I would like it to sum row E.
I included the sumproduct formula I tried to put together but I'm getting an error, so I'm not sure what I've done wrong. The reason I have it referencing "O2" instead of just inputting "H&D" is that O2 could be any number of plans - I have multiple rows with different plans and I need it to pull in all the data.
I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)
I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )
excelformula.gif
pricing.xls
I have two documents, and I need to search data in document 1 based on information given in document 2. On document 1 I have ID number and date range and on document 2 I have ID number and date. Date needs to be in the date range, and ID numbers must be the same.
Please see tables below for the reference.
Document 1
Document 2 ID Start date End Date Data
ID Date Data 11 01/01/2009 31/12/2010 Data 1
11 05/05/2011 22 01/01/2010 31/12/2010 Data 2
22 08/08/2010 22 01/01/2011 31/12/2011 Data 3
33 01/01/2012 33 01/01/2012 31/12/2012 Data 4
11 02/02/2009 33 01/01/2010 31/12/2011 Data 3
11 01/02/2011 31/12/2011 Data 2
I am trying to run a lookup on a rather large table.
Column A Column B Column C Column D Column E Column F
Postal code City Province Postal code City Province
I am trying to look up the City and Province based on the Postal code and can't figure out how to do this.
There are too many Postal codes to fit them all in Column A, I have tried V Lookup, Index Match and can't get it to work.
I want to lookup values from one rawdata worksheet and have values appear on another sheet, but using 3 lookup values as the criteria. I cannot do this function with Vlookup as it only takes one criteria. I have looked for similar threads in this forum and came across an Index/Match function, but I cannot get it to work. I am attaching file. The data is in the 'rawdata' worksheet. I want values to appear in 'Input' worksheet, grey area using the 3 criterias (Dept number, GL number, and Seg code number).
View 4 Replies View RelatedMy workbook is tracking how many products have been sold and to whom.
Customer Market Apples Oranges etc (10 products) POSTING
xyz South 100
xxx South East 600
I have a second workbook that works out how much it costs us to send the goods and this is a matrix based on where our customer is and which produce they have purchased.
South Apples Oranges Pear etc
South East $x $y $z
North etc
North West
etc
I would like to write a formula under the POST column that takes into account:
The Market. The Produce type (the column heading) based which cell contains a value
i.e using above Row 3 criteria would be Market = South and Produce = Apples. These two pieces of information are then to be used to go to the workbook containing our postage costs and picks up the relevant cost for Apples in the South region.
I have a workbook with 4 sheets with Carrier rates based upon weight breaks and cities. I.E. Carrier 1 services City 1 @ 1.30 per LB for 0 -100LBS and 1.55 for 101 - 199LBS. What I need is a forumla so that someone can input the city and weight on sheet 1 and it will search the 4 sheets and return the cheapest Carrier in the event that there is two carriers with the same rate it would return both. It would also need to return the rate for that city and weight.
For example
User inputs "City 1" "112 LBS" formula searches the 4 carrier sheets and returns values "Carrier 1" & "1.55" as this is the cheapest carrier and the rate they charge per LB for this city.
I'm getting cross-eyed from trying to find the source of the error and not having any success. I've successfully used this approach many times, but for some reason I'm getting an error I can't pin down.
The problem: INDEX-MATCH lookup formula returns # NAME error. Formula: =INDEX(Data,MATCH(H8,Date,0),MATCH($I$7,Line,0),MATCH("FPY",Hdngs,0)). I've done my best to verify the named ranges and cell references are correct and have stepped through the help file on the "potential source of the error", including going to Tools >Options and making sure "Accept Labels in Formulas" is checked, as well as having read a number of other posts.
So basically I am looking to build a formula that would take this information and return a text value.
if it could work anything like this sumproduct formula to bring back text that would be awesome:
=SUMPRODUCT(--(A1:A10="7654321"),--(B1:B10="ABCDE"),--(C1:C10="3"),D1:D10)
But instead of a formula that returns a sum of values, the return is a single value. The three criteria will always be unique. There will never be a case where there is a duplicate of all three.
I am looking to write a search function that searches through a whole document. The only examples I can find are of a could different search functions but they all are searching an specified individual column and even then I didn't really understand them. My worksheet has 6 different columns which are all different labels for a certain tool. I want the user to be able to input any of those 6 labels and have all the information in that row be returned.
View 4 Replies View RelatedI am trying to return a value from table below (column 4) based on values in the 1st 3 columns. I have data with the 1st 3 columns but need to lookup each and return the 4th so I have tried some formula's with if and vlookup statements but keep getting lost....
View 9 Replies View RelatedI have a giant lookup table with five columns. I have three variables, and I need to find the row in the lookup table where the variables match the first three columns of the lookup table. ie. Find the row where variable 1 matches the value in column 1, variable 2 matches the value in column 2 and variable 3 matches the value in column 3. If this row exists then return the value in columns 4 and 5 of that row. Otherwise, return a zero.
View 4 Replies View RelatedI am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant.
View 9 Replies View RelatedI'm trying to create a formula that will lookup multiple criteria within a table, however the problem I am having is that one of the criteria needs to fall within a certain a date range. I've used index & match to look up the dates when they match exactly, but how to return the same value for when they fall within a date.
I have attached the spreadsheet - Date Range.xlsx! The requirement is to get the correct "pay" from the "Contingent Workers" sheet to the Timesheet sheet. So it will match the Employee ID, but also the date on the Timesheet tab will fall between/or be the exact date indicated on the contingent workers tab.
I am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.
File is attached.
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.
View 7 Replies View RelatedI have a sheet, called "output", in which I need to complete column C "calculated values". I need to complete the table based upon a formula table (which is in sheet "formulas").
For the first row of data, cell C2, I need to take the price per order ($0.25; cell A2) and number of orders (40; cell B2) and copy data to cell B4 and B6, respectively.
Once the data has been copied to to cells B4 and B6 on the fomulas sheet, I need to copy the calculated value in Row N to the output sheet. Note that the value being copied from N can be N11, N12, N13, N14, or N15 (the one that is <> to null).