Lookup Multiply Based On Certain Criteria
Apr 18, 2007
I have two worksheets, one is setup with a part number only and the months Jan-Dec. The other worksheet has those same part numbers, but it also has the quantity and the months Jan-Dec. I need to do a lookup to find the part number, multiply it by the quantity on that given line, and then multiply it by the quantity in the month. I will show you how my worksheets are setup:
Worksheet1:
Qty P/N Jan
1 123456 100
1 123456 100
2 456789 50
4 123456 25
3 345678 500
1 456789 75
Worksheet2:
P/N Jan
123456 Sum of ALL Jan Demand * the Quantity of each item
345678 Sum of ALL Jan Demand * the Quantity of each item
456789 Sum of ALL Jan Demand * the Quantity of each item
View 9 Replies
ADVERTISEMENT
Aug 9, 2007
I have a Bill of Materials (BOM) that has been flattened from a database into an Excel format. There are many BOM levels and I need to multiply the quantity of a component by the quantity of the "parent" it reports to. I have attached a sample file with raw data and the expected result (calculated manually). My goal is to create a formula that calculates the "Ext. Qty" column in my expected result. EXT_QTY.xls
Here are some of the rules for the raw data structure:
1. First BOM level at very beginning is always "1"
2. All children of BOM level "1" have a BOM level of "2"
3. All children of BOM level "2" have a BOM level of "3"...
4. There are potentially many BOM levels (approx. 15 levels for full set of data)
5. All of the BOM level "2" items are children of the most recent BOM level "1" assembly and there will be multiple level "1" items, level "2" items, etc.
6. Data is always sequential so the children (and grandchildren) will always come after the parent
View 9 Replies
View Related
Jan 24, 2014
I have a workbook with several different worksheets which I am copying from the individual sheets and pasting into a summary sheet, and multiplying the numeric numbers by minus 1 to reverse the signs.
This is all working fine, but I now want to copy an additional worksheet called "Plan" and paste this into the same summary worksheet, however I do not want to reverse the signs for this data.
All of the other worksheets begin with "Total" in the worksheet name, and this is how I am specifying which sheets to copy. I can re-name the "Plan" sheet to "Total Plan" if that makes the logic easier, the import thing is that the signs are not reverse for this sheet.
Column Z of the summary worksheet "RDBMergeSheet" identifies which sheet the data was originally copied from, so could I somehow use this to say if column Z = Plan, skip the multiplication by minus 1?
My current code is this:
VB:
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
[Code]......
View 3 Replies
View Related
Jan 23, 2012
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G)
2.) Multiply the % by the Revenue (Column H)
3.) Sum up the total for each of the names (Locatedin Columns B, D, F)
4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
View 3 Replies
View Related
Jun 6, 2014
I have two workbooks. I'll call them wkbk1 and wkbk2.
I am looking at three cells in the same row in wkbk1.
I need to identify which row in wkbk2 contains those values and then return a value from a cell in the same row in wkbk2.
How do I structure this look up?
View 5 Replies
View Related
Apr 29, 2014
trying to lookup data based on 2 criteria. On sheet A I need to lookup "Date of Reason" and "AMT" based on Employee Name and if there is something listed in the Reason Codes Column from Sheet A. So on sheet A employee Vandiver has a reason code = Absent so I need to pick up the date and amount from sheet B = 4/21/2014 and 8. I tried the formulas listed below but can't seem to get them to work. Also need to note that sometimes the Reason Code column in Sheet A may have multiple listings so it would be better if formula used a "not blank" in it. That's what I tried with the first "IF" formulas using "=" as there are formulas in the reason codes column in Sheet A.Sheet A
Employee
Mon
Tue
[Code].....
View 8 Replies
View Related
Dec 1, 2007
I need to take a value from an adjacent cell and automatically enter that value in a specific cell in table on a second worksheet. The cell must match two criteria specified in the previous two ceels to the value.
The table is made up of dates and colours, it seems simple in my head but I'm not sure where to start, i would like to use a macro so when a specific key is pressed the information is automatically entered into the relevant cell.
View 9 Replies
View Related
May 31, 2008
I have two pivot tables (for sales and purchases) with three columns each, ie: company 1, company 2 and amount
I need to reconcile that for the internal purchases and sales the sale value is the same as the purchase.
So in first pivot table (purchases) I have one row with internal transactions where company 1 = AZS, company 2 =YDR, value 100
In second pivot table (sales) I have one row with internal transactions company 1= YDR, company 2 = AZS, value 95
I would like to develop a macro that would check automatically the combinations of companies and find out if there are differences between sales and purchase. So in the example it should show that there is 5 usd more in purchases. Hopefully normally is zero
View 3 Replies
View Related
Jan 15, 2012
I have the following in sheet 1:
Sheet1 *BCDEFGHIJKL1NameLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA2AMMB $ * * * 5.76 $ * * * *17,361.71 12.8861.633 $ * * * * * *7.05 $ * * * * * 5.30 10%1.20.42%3CIMB $ * * * 7.27 $ * * * *54,036.27 14.8372.156 $ * * * * * *9.01 $ * * * * * 6.56 3%20.61%4RHBCAP $ * * * 7.31 $ * * * *16,022.91 11.0761.435 $ * * * * *10.40 $ * * * * * 6.53 6%3.20.83%5HLFG $ * * 11.70 $ * * * *12,120.91 7.2581.576 $ * * * * *13.74 $ * * * * * 8.44 4%1.711%
I have the following in sheet 2:
Sheet2 *BCDEFGHIJKL2Parameters**********3Last Done**********4Market Cap (M)**********5PE**********6Price/NAV**********7High, 52 Wks**********8Low, 52 Wks**********9Revenue Growth**********10Current Ratio**********11Debt to Equity**********12ROA**********13***********14StockLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA15***********
1)I need to create a formula to populate all stocks that satisfies the parameters that I will input in cell C3 to C12 in sheet 2 based on the data in sheet 1.
2)The stocks should then be populated in cell B15 downwards in sheet 2. If there are 20 stocks that satisfies the parameters in cell C3 to C12 in sheet 2, then all 20 stocks should be populated in cell B15 downwards in sheet 2.
3) The respective details of the stocks should also be populated in cell B15 to L15 for all stocks that satisfy the parameters entered in cell C3 to c12 in sheet 2.
View 4 Replies
View Related
Apr 29, 2013
I have 2 sheets.
Sheet one column A has random dates from 1 jan 2013 to present in order base on fuel purchase dates for our company.
sheet one column F has the purchase price i.e 3.98 gallon
Sheet one column A can have a date entry of lets say 2/4/13 with no data in F because no fuel was purchased just the meter was being read.
Then Sheet one column A can have 2/5/13 where column F has 3.78 where fuel was purchased. (these are 500 gallons tanks that are being refilled as needed)
Sheet 2 row B has just the month formatted as Jan 13, Feb 13, Mar 13, .... you can use B1, B2, B3...ECT for the remaining months
What I am looking for is on sheet 2 in cell C1 a way to look at B1 for the month (i.e Jan 13) look at a range of cells on sheet 1 column A for any dates that fall in the month of Jan, then look for data in corresponding F range and if no data is found then use the last months data (Dec 12)
I can make sure I place a dollor figure in the first row of Jan on the sheet, so by default we can use that if no data exist for Feb.
View 9 Replies
View Related
Oct 24, 2013
I need to change the value returned in one cell based on the criteria of a 2nd cell. In short, if the letter 'C' is entered into A1 and B1 contains a vowel the lookup formula needs to return the value found in column C of a separate spreadsheet, but if the 'C' is entered into A1 and B1 contains a consonant then return the value of Column B. I want this to be applied to C, O, R & S only. If A1 contains any other letter, than the lookup formula should return only values found in column B of the separate spreadsheet.
View 3 Replies
View Related
May 28, 2008
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2.
I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
View 4 Replies
View Related
Apr 5, 2014
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
see my attached workbook..
View 9 Replies
View Related
Aug 22, 2008
I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.
I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.
So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.
View 14 Replies
View Related
Feb 17, 2009
I'm looking for a formula for return a result only when one other criteria is met. I've looked at SUMPRODUCT and VLOOKUP as options but haven't been able to make them work. Here is what I need:
I want cell E33 to look up cell B33 in 'Test Result Table' and return the 12th column of information, but only when cell C33 and 3rd column in 'Test Result Table' match. If the first result isn't a match I want the lookup to skip it and return the first available match.
I don't want to add anything, I just need a match returned based on C33 and the 3rd column being equal (cell C33 is a VLOOKUP of the 3rd column in 'Test Result Table').
View 4 Replies
View Related
Dec 21, 2011
I am getting an error i have list (sheet4) i want to lookup the data from different table (Name range) based on the location in column A i am using the small function copy down, in cell C6 i want the range ROWS($A$2:B6) go back to ROWS($A$2:B2) since the criteria in column A has changed from China (A5) to US (A6)
Sheet4ABC1Locationproductsamount2ChinaA2954.63ChinaB1565.284ChinaC3772.055ChinaD2027.76US#REF!#REF!
Array FormulasCellFormulaB2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:A2)),1)C2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:B2)),2)B3
[Code].....
View 1 Replies
View Related
Aug 31, 2006
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.
For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
View 3 Replies
View Related
Sep 8, 2006
I have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:
Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.
View 3 Replies
View Related
Mar 8, 2008
currently have this: = SUMIF(D8:E8: F8:G8:H8,1,D29:E29:F29:G29:H29)
Range is multiple rows
need to get to here:
"multiple" Criteria needs to be able to multiply amount in sum range when greater than 1 and then summed in column I, across each row, then totaled at bottom of column I.
I realize sumif won't suffice.
View 4 Replies
View Related
May 26, 2009
I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.
With that said....
1. How do you put in the drop down box that references information on a different sheet?
2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?
View 14 Replies
View Related
Aug 4, 2009
I have three reference cells (a1, a2, a3).
Below, starting from b4 to l4 I have years from 2005 to 2015, row 5 for the same range contains data, row 6 (c to l) contains a growth rate.
I need to come up with a formula that can multiply (x * y * z *... [product-like formula]) the growth rates for all the years where the current year (row 4) is bigger than reference the reference year in a1 or smaller than reference year in a2. A3 is then used to multiply that total.
View 6 Replies
View Related
Jul 17, 2009
Please see the attached .xls.
I've tried to replicate my formula (result is in cell J5) via an array formula but keep getting stuck.
Basically there is a 2 dimensional array of weights (range1, limits)
each range in range1 and "limits" has a midpoint
a constant value (Fixed Amt) is added to each midpoint in range1
then multiplied by another constant value (Inflation Index)
The value is then capped by corresponding value in the limits range
this is repeated for all points in the 2 dimensional array (range1,limits) (essentially weighting the results)
and the sum is returned.
I've been able to get most of the way with an array formula, but end up collapsing the array when I use the min function.
View 6 Replies
View Related
Jun 17, 2014
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
Test2014.xlsx
View 3 Replies
View Related
Jan 8, 2009
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.
Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.
View 2 Replies
View Related
May 11, 2009
1 . Now i need amount to be written in sheet 1 from taking value from sheet2 based on the code. The code & description column has the same value. but it will be placed in different order
Ex:
the amount should get filled up only for the status yes & no . others it should return null.
View 3 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
Apr 21, 2006
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View 7 Replies
View Related
Oct 30, 2013
Excel (2000), what formula would I use to accomplish the following?
If A1 equal or less than 1000 value, multiply by B1, otherwise multiply by C1.
View 9 Replies
View Related
May 31, 2014
The first tab has the following values in one table: ordinal of car manufacturer, car manufacturer, number of cars sold in 12 months (seperated in different columns)
The second table has: car manufacturer ordinal, car manufacturer and the price of one unit.
Second tab has the following: one table with the following columns: ordinal of manufacturer, car manufacturer and all 12 month's names seperated, this table is the same as the first table in the first tab.
Now, when i type for ex. ordinal 1 in this tab it should give me the car manufacturer, and it should multiply the value of number of cars sold in all months, for ex: if there are 5 chevrolets sold in january, then multiply 5 x n (where n is the price of one unit) and print that value in column january.
View 10 Replies
View Related
Apr 27, 2009
I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.
Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.
The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.
In the next post I will include the Amortization schedule.
In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).
Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 10 Replies
View Related
