Sum Based Conditions In Different Sheets?
Dec 11, 2012
I'm creating stockportfolio's based upon historical stock data. I have 300 stocks with monthly observations. Stocks are assigned to a portfolio based upon their return in the previous 6 months. I need the top 10% stocks in terms of return in previous 6 months (so 30 stocks) in one portfolio and the lowest 10% stocks in terms of returns in previous 6 months returns (so again 30 stocks) in another portfolio. Each portfolio is just a sum of the returns of the stocks that are in there.
First sheet: rows contain the return a stock had in the 6 months before the month in the left column (so january 2006 contains the return a stock would have had from july2005 -december 2005). This is done for 300 stocks. Second sheet: contains the return the stock had in that actual month (so january 2006 just contains the january 2006 return) The third sheet is where I want to create the decile portfolio's. This means that for every month i am creating 10 portfolio's: Each month "portfolio 1" should contain the sum of the returns of the stocks with the 30 highest returns in the past 6 months. "Portfolio 10" should contain the sum of the returns of the stocks with the 30 lowest pas 6 month returns. (30 is 10% of 300: that's why it is called decile portfolio)
So I should sum things from sheet 2 with the condition referring to sheet 1 where the returns of the past 6 months are displayed. I am trying formula's like =SUMIF(RANK...) but am getting no results. Somehow excel should select the right stocks and sum the 30 returns. I'm totally lost after a lot of trials.
View 1 Replies
ADVERTISEMENT
Dec 29, 2006
i am not able to solve this problem.
1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table
2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month.
3- Reference table has the list of the same data record ( numbers ) .
Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following.
a- Check for the record in the reference table , if doesnt exist then add the record in the reference table
b- create another sheet same as the sheet " A" (duplicate of
A) and name it the new record number
b- Plug the new record number in the cell B2 of the new sheet
c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to
O8)from sheet A
View 5 Replies
View Related
May 11, 2011
I need a code to select sheets with pages less than 15 to print out. Also, a code to print out just the first 5 and last 5 pages if its more than 15 pages.
I've tried this so far but no success:
For X = 1 To Worksheets.Count
If Y Is Nothing Then
Set Y = Sheets(X)
Else
If ExecuteExcel4Macro("Get.Document(50)") < 10 Then
Set Y = Union(Y, Sheets(X))
End If
End If
Next X
Y.Select
View 6 Replies
View Related
May 15, 2012
How to get the sum of a range if 3 conditions are met.
1 after this date
2 before this date
3 Column G is "OTH"
Ranges are on sheet1
Date reference is on sheet2
range to sum is on sheet1
V14.
Present formula is:
=SUMIF('sheet1'!$P:$P,">="&EOMONTH($A2, -1)+1,'sheet1'!$H:$H)
-SUMIF('sheet1'!$P:$P,">"&EOMONTH($A2,0),'sheet1'!$H:$H)
This works fine but lacks the 3rd condition 'sheet1'!$G:$G ="OTH"
Advise given was to go on SUMPRODUCT but unless with (OFFSET(INDIRECT, SUMPRODUCT wouldn't work on multiple sheets. And this is for version 14...
View 5 Replies
View Related
Apr 12, 2008
I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.
In an excel workbook i have a sheet that has rows and rows of Sales Leads.
In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.
I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.....
View 9 Replies
View Related
Nov 13, 2006
I'm creating a summary sheet that adds up all the data on sheets between start and finish where A2-A25 is SU and B2-B25 is 004*04. Background info: SU stands for SetUp, and 004*04 is a workcenter number (its not 004 times 04) I'm trying to calculate the total number of Setups for each workcenter.
=SUM((start:finish!A2:A25="SU")*(start:finish!B2:B25="004*04"))
gives me a #NAME? error.
Example Data on each spreadsheet:
A B
SU 004*02
SU 004*04
SU 004*04
SU 004*02
SU 004*04
View 9 Replies
View Related
Dec 26, 2013
I'll need to pick up the rates from sheets "SYD" & "BNE" by info from column A of "Summary", which means if the info in column A is "sydney", the formula should pick up the rates from sheet "SYD", if it's "brisbane", the formula should pick up the rates from sheet "BNE".Also, the rate should be matched by the column B,C & D from "summary" page.
View 8 Replies
View Related
Dec 27, 2012
What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.
What I am after is, when I combine the data, from all sheets into the one, the data should be
1. Automatically Sorted by Patient Name
2. Group, results of the same patient from all sheets one below the other in seperate rows
3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3).
Page 1 (Page 2 and Page 3 are exactly the same)
Patient Name
Patient Number
New Mole
Clinically suspicious
Changed from mapping Photo
Level of Suspicion: Score
Monitor
[code].....
View 3 Replies
View Related
Jan 29, 2009
I want to auto fill certain columns in sheet A from sheet B & sheet C based on conditions
For Eg:
Sheet A:
Position#
Position Name
Personnel Name
Personnel No.
AB1
Manager
AB2
I want to auto fill in Sheet A for the columns:
Personnel Name
Personnel No.
Conditions:
If the employee is active in Sheet B then fill in Sheet A to the related position No.
Else
If the employee is mapped to org. (Yes) in Sheet C then fill in Sheet A to the related position No.
Else
Vacancy
i have written the below formula but not working properly:
=IF(VLOOKUP(SheetA!A4,Direct!A$2:K$16,5,FALSE)="Active",VLOOKUP(SheetA!A4,Direct!A$2:K$16,3,FALSE),IF(VLOOKUP(SheetA!A2,Contractors!A$2:K$15,5,FALSE)="Yes",VLOOKUP(SheetA!A2,Contractors!A$2:K$15,3,FALSE),"Vacancy"))
View 9 Replies
View Related
Mar 24, 2014
I have a workbook with three sheets: Collections, Client and Interest New sheets created from a range in Sheets("Interest"). The range in Sheets("Interest") holds the client names.
Then the template in Sheets("Client") is copied and pasted into the new sheets
For each new sheets cell B6 holds the client name and .Range("A10:A1317") holds the dates
Sheets("Collections").Range("D10:D1317") holds the dates and .Range("D8:WC8") holds the Client Names
if Sheets("Collections").Range("D8:WC8") = new sheet.cells(6, 4) then
if Sheets("Collections").Range("D10:D1317") = new sheet .range("A10:A1317") then
copy the value in Sheets("Collections").Range("D10:WC10").offset(0, 3) to new sheet .Range("C10:C1317")
end if
move down on row and repeat the steps.
View 8 Replies
View Related
Dec 1, 2007
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime.
Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)
View 4 Replies
View Related
Aug 16, 2013
My challenge is pulling in a data element from a file to an exisiting file based on two criteria.
Example: give me the value in cell e1 if cell b1 matches AND
File 1
A B C
1 H12377 03/05/2013 123
2 H12377 03/27/2013 276
3 H32389 05/03/2013 335
File 2
H12377 03/27/2013 _________
how do I get C2 value from file 1 (276) into file2 since column A is not unique but column A plus Column B is unique?
View 5 Replies
View Related
Jul 1, 2014
I have attached a file where there is information in the sheet Weekly with the first row showing the same month for several weeks. add a formula in the sheet Monthly which would calculate average for each month based on the first row in Weekly sheet. Plus the grouping in the Weekly sheet is done by shops and in the Monthly sheet by food, which would I imagine make the formula more complex.
View 5 Replies
View Related
Mar 13, 2008
I am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).
View 9 Replies
View Related
Aug 28, 2009
I am trying to sum numbers based on three counditions.
My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*
The yellow cell contains the formula I am using.
View 4 Replies
View Related
Mar 6, 2013
I am doing a financial analysis for a hospital. I want to know how to add something to a number based on conditions. Base pay is $300 for the first 10 patients. If more then ten patients >11-20 add 40 dollars per visit if greater than >21 add 50 dollars per patient
View 3 Replies
View Related
May 20, 2009
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.
The following is a table which shows a salesmans sales. He or she has sold to 8 customers.
What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.
1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40
2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25
3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
View 6 Replies
View Related
Jul 13, 2009
I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1.
The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H.
So I need to return something like this: ...
View 14 Replies
View Related
Aug 3, 2012
I am tasked with making a matrix that I cant seem to solve.
I have solved it on a small scale using
=SUMPRODUCT($E$5:$E$19,--ISNUMBER(SEARCH(O11,$C$5:$C$19)),--ISNUMBER(MATCH($D$5:$D$19,{"One","Two","Three"},0)))
The issue is that I need where {"One","Two","Three"} is to contain between 3 and 334 conditions.
Basically we have a data table that contains "channels" that have multiple affiliates under it. Then we have a data table by affiliate by country and revenue... I want revenue by Country By Channel...
View 3 Replies
View Related
Sep 25, 2012
I have this vb script which basically states if a value in column G is less than 0 then show a message box as below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
If Target.Value < 0 Then
MsgBox "You have entered a negative figure - are you sure this is correct?"
End If
End If
End Sub
What I would like to do is extend this script so that another message box pops up based on two conditions
i.e. if a cell in column F shows string "accounts use only" AND value in the adjacent cell in column G is > 0 then MsgBox "you have entered a positive figure - are you sure this is correct?"
View 5 Replies
View Related
Aug 10, 2013
I'm trying to adjust my macro to fill the interior cell color of a row if there's a non-blank row above and below it (i.e. if there are 3 or more non-blank rows together, then fill the 2nd, 4th, etc. rows. If there are only 1 or 2 rows together, then don't fill the cells.
However, what I'm getting is filling ever other row, with this code. There seems to be an error in how I'm trying to use and If condition with 3 criteria.
Code:
Sub colorin()
Dim LastCol As Long
Dim r As Integer
r = 6
With ActiveSheet
LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column
[Code] ....
Here are some sample results (pretend cells with red text are actually cells with interior color, black text is an unfilled cell).
Rank
Name
1
Jones, Some
[Code] ......
What I'm trying to achieve is: (again, red text actually represents filled cells - can't get sample shot of actual filled cells to copy into forum post).
Rank
Name
1
Jones, Some
[Code] ...........
View 6 Replies
View Related
Nov 6, 2009
I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course.
The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....
View 9 Replies
View Related
Dec 22, 2006
In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.
View 4 Replies
View Related
Sep 25, 2012
I created a master data sheet to enter data manually to analyze. It contains 20 columns, and rows(continue change, because we enter data all of the days). I need to extract data to another sheet, when the user enters data in the master data sheet, but with some conditions, when the column 6 contain the word ASQ, and then extract all of the data, but the new sheet do not contain 20 columns, because the data that contains the word ASQ have only 16 columns.
View 6 Replies
View Related
Sep 28, 2012
I need to create a macro to extract data from sheet1 to sheet2.
The sheet1 contains 15 columns, and the data starts at row 5.
The sheet2 contains 15 columns, and the data starts at row 5.
We enter data all of the days, so we have like 500 rows in the sheet1.
Only 2 conditions to extract data from sheet1 to sheet2:
if the column9 of the sheet1 contains the word "NO" and column11 of the sheet1 contains the word "ASQ".
View 4 Replies
View Related
Jan 4, 2013
I am creating a document log that tracks all excel files sent and received.
I use RDBmerge to get the filenames and data from the file batches.
My current macro edits the data down to the last stage of data needed to create the log.
My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"
Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.
I have highlight the cell range on the "Final_Data" Sheet that I need to complete.
filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.
The criteria is this:
A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")
I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.
i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method
[Code] .....
I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.
View 9 Replies
View Related
Apr 3, 2013
I'm trying to hide rows in an excel sheet based on two stipulations. I want to hide the row if it finds a particular value in column B and a different particular value in column K, otherwise i want it to do continue looping until it has hidden all rows that meet both stipulations.
VB:
Sub hide_loop()
Dim bl As Excel.Range
Dim blrange As Excel.Range
[Code]....
Currently I have tried different approaches, like a Do Loop, but I could not make that work, and this seems closer. The problem I'm having right now is that with this loop it hides everything found in column B regardless of what is in column K. I suspect this is because of the code following the if statement.''
View 9 Replies
View Related
Sep 24, 2013
I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".
View 1 Replies
View Related
Jan 6, 2014
Current simplified situation:
Department A
Employee_1
Employee_2
Employee_3
(empty cell)
Department B
Employee_4
Employee_5
Department C
Employee_6
Employee_7
Employee_8
Employee_9
Employee_10
With the VLOOKUP function, I have looked up the row numbers of the departments. Subsequently, I'd like to find the last employee of that department, based on the department's name and/or the department's row number.
A department-typed cell is non-empty and never contains an underscore. An employee-typed cell is non-empty and it always contains an underscore.
The simple and non-sufficing formula is the VLOOKUP of the row number of next department minus one. But with adding, moving and subtracting departments rapidly, this is not an option.
View 4 Replies
View Related
Jan 18, 2009
I'm using 2007 but I need this to work with Older versions so I tried to combine the condition for red
View 14 Replies
View Related
