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.
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!!
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example:
Sheet1 looks like this:...................
I need to merge data from sheet 2 to data on sheet1.
Sheet 1 data is in the format given below with start date in G column and campaign ID in column H.
Start Date
Campaign ID
17-May-14
3834177
[Code].....
I need to look up Campaign ID of sheet 1 in sheet 2 and add all insertions and platform for that campaign to next columns i.e. I and J in sheet 1. For ex Campign ID 3834177 of sheet 1 has 5 insertions corresponding to it in sheet 2 hence the insertions and their Platform would transfer to sheet 1. Since Campign ID 3834177 is only in 2 rows on sheet 1, 3 more rows would need to be inserted in sheet 1 to accommodate 5 items and it would look like.
Start Date
Campaign ID
Insertion ID
Platform
[Code].....
I have a 5K Excel 2010 workbook with VBA code that until yesterday, took about 30 sec. to run.
Today, after installation of WIndows7 Home Edition automatic updates, the running time has increased to about 4 min.
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
I am using the following code, slightly modified, written by Tom Urtis.
I would like to enhance the code such that it will filter on two criteria to enable more specific results to obtained e.g. from the possibilities below I would like to get "Jones Manchester".
Data list:-
Jones Blackburn
Jones Liverpool
Jones York
Jones Manchester
Jones London EC1
Jones London N12
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address "$D$1" Or .Cells.Count > 1 Then Exit Sub
ActiveSheet.AutoFilterMode = False
If Len(.Text) > 0 Then
Dim myVal$, myVal2$
myVal = .Value
myVal = "*" & myVal & "*"
If WorksheetFunction.CountIf(Columns(1), myVal) > 0 Then
I wrote this code inside the excel spreadsheet itself. The code sits in BA1 to BA2000. I would then copy and paste this code to a VB module, then run it. As the data changes, the SchemeColor number changes. My question... is there a way to run write a macro in VB and have it use the code in cells BA1 to BA2000 so that I do not have to copy and paste it into the VB Module and run it? Manually running it is ok with me, but I do not want to copy and paste the code into VB each and every time.
Here is my code:
Sub Macro2_()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
[Code].....
I use this code to delete whats written in a Range of cells ...
View 6 Replies View RelatedI have amended the code below and have got it working. The problem I have now is that every time it loops it overwrites the data it wrote the previous loop
Offending line being ActiveSheet. Range ("A1: D30") = ValuesArray
I have known that somehow it should remember the last row and copy below this one but I cannot get it to work
Code:
Sub Basic_Example_1()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long, Fnum As Long
Dim mybook As Workbook, BaseWks As Worksheet
Dim sourceRange As Range, destrange As Range
[Code] ..........
I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.
View 2 Replies View RelatedI 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?
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].....
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.
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
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.
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.
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 RelatedI 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
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?
I'm trying to create an autofilter that can filter each category that gets listed in a specific cell.
I have some code from another sheet which I'm trying to modify to accept more than 1 filter criteria.
[Code] .......
Where W2 lists something like:
"Blasting, Flotation, Drilling" and I want it to filter all three categories in the location J24 to the last row with data in it.
I believe I need to apply "*" in locations but I'm not sure where?
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..
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.
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').
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].....
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..
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.
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?
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
(File is attached here)
I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.