Determine Intersection Of Target To Multiple Ranges
Sep 29, 2007
This code works fine, but it does not look professional. I tried to write like: If Application.Intersect(Target, rng1, rng2, rng4, rng5) Is Nothing Then Exit Sub
it did not work, but as it is down here it worked
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range
On Error Goto NoValidation
Set rng1 = Range("B28:B55")
Set rng2 = Range("D28:D55")
Set rng4 = Range("H28:H55")
Set rng5 = Range("J28:J55")
If Application.Intersect(Target, rng1) Is Nothing Then
If Application.Intersect(Target, rng2) Is Nothing Then
If Application.Intersect(Target, rng4) Is Nothing Then
If Application.Intersect(Target, rng5) Is Nothing Then
Exit Sub
End If
End If
End If
End If
End If
If Target. Validation.InCellDropdown Then Application.SendKeys "%{Up}"
NoValidation:
Err.Clear
End Sub
View 2 Replies
ADVERTISEMENT
Nov 24, 2008
There is a database with work plans for a project cycle, which contains employee names, types of activities, planned working hours for each activity (not in time format, just number) and dates when these hours will take place. Each employee may have one or multiple activities and each activity has different number of hours planned to complete and different dates. My aim is to calculate the number of hours planned for each employee (to sum all activities) within a certain period of time.
So, what is the question? I need to calculate the number of workdays within the intersection of the planned period for the activity and my given period of time.
I'll give a small example to illustrate what I mean.
I'm calculating planned working hours for this week, which is 11/23/2008-11/29/2008. One of our employees, let's call him John, is going to work on three activities, A, B, and C. We have the following plans:
Activity A - 16 hours - 11/24/2008-11/27/2008
Activity B - 45 hours - 11/15/2008-11/25/2008
Activity C - 10 hours - 11/26/2008-12/01/2008
As you can see, activity A is going to be complete within this week, it's perfectly fine. Activities B and C should be counted separately, because they include days earlier or later than this week. Is there any way I can easily geet the number of workdays that are included in the plan period and this week?
Another thing to mention is that I'm working in Excel 2003 and it's not acceptable for me to install the Tools Pack, because I need this firmula to work on other employees computers and there is no chance they all will install it too.
View 14 Replies
View Related
Sep 28, 2009
How would I create a formula that would match a number in a range by column and another number in a range by rows, then fin the intersection of those 2 numbers?
View 9 Replies
View Related
Aug 19, 2013
I'm trying to make a range selection in a pivot table as per the snapshot below:
http:[url],....
The values I am trying to select are the ones that are both red and italic. (Mobile, Other Fix, Fix) from CBU_NA.
I've searched and found a way to select both the rows for CBU_NA category and also the column categ2.
However when I try to make an intersection out of the 2 ranges I receive an error. I'm using Excel 2010 on windows 7.Below the code I am using:
Code:
Sub FCST()
Dim r1, r2, r3 As Range
r1 = pt.PivotFields("categ2").DataRange.Select
Selection.Font.Italic = True[code]...
View 2 Replies
View Related
Aug 24, 2014
Data is;
83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.
So, my bullseye percentage = 43209/83300, or ~51.87%.
Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.
Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.
View 5 Replies
View Related
Mar 27, 2007
i'm having a really hard time figuring out how to use target. offset to grab a range of cells. For Example:
I am currently running a macro that searches column "D:D" for data. When the data is found, i want to be able to Grab columns D to AG for that row, and apply conditional formatting. It seems like using Target.Offset should be able to accomplish this, but i can't figure out how to specify a range using Target.Offset. Currently, i have to specify each cell i'd like to select, and apply formatting one at a time, which has bloated my code considerably (and causes me to run out of room very quickly).
View 4 Replies
View Related
Dec 13, 2011
What do I do if I want to determine whether an input is within a specified range, and then for there to be an output of a certain value from another cell?
For example, with tax brackets: if income (D) falls within B and C, return value A(from a separate column)?
Here is the data I have:
Column A
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
[code]....
Column D, cell D1 is where you would input a number (income) Column E, cell E1 is where I want the output value.
Basically, what I would like is a formula to put into column E that will do two things. First, to determine if the value in D1 is within either (B1-C1, B2-C2, B3-C3, B4-C4, or B5-C5). Second, to then output the value of either A1, A2, A3, A4, or A5, for each respective range.
View 9 Replies
View Related
Jun 5, 2007
The compliment of a set is those elements not in the set. Excel doesn't do compliments well.
In Automatic Filter, one can easily see the rows that match criteria, but selecting the compliment of those rows (the ones that don't match the criteria) is difficult.
Similarly, there are the range functions Intersect and Union, but no Symetric Difference.
(Def: the symetric difference of two sets, AB, is the set of those elements in A that are not in B. {1,2,3,4,5}{2,3,4,10,11} = {1,5}.)
Other than by looping through cells:
Given two ranges, aRange and bRange, how would one code for those cells that are in aRange, but not in bRange.
View 9 Replies
View Related
Sep 1, 2007
i am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.
View 2 Replies
View Related
Jan 20, 2014
I'm trying to run a macro anytime cells G2, J2, M2, O2, P2, S2, V2, Y2, AB2, AE2, AH2, AK2, AN2, AQ2 change.
Right now I have the below code which is working well, but I only have it set for G2. Do you know what the notation is to make the target range multiple cells?
Also, I use the xlDown command in my sorting code, but later on it reverts back to "A5:AT60"... is there any way I can remove these specific cell references? I want to avoid having to re-write the macro every time my selection shrinks or expands.
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("G2")) Is Nothing Then
Rows("5:5").Select
[Code]....
View 3 Replies
View Related
Jun 2, 2014
I have a target range for a worksheet change. Then when finished I highlight the data and press 'delete' I get an error within the code.
View 2 Replies
View Related
Jul 8, 2014
I'm struggling to come up with a formula that ill give me the following:
I have will have a row of data (cells A4:H4 on the attached example), in the respective cells below each value in row 4 I want formula that will give me a value of 10 if it sees 90% in the cell above reducing to a value of 0 if it sees 80% in the cell above. I think that there should be a relatively straightforward formulae for that, my problem is that I want to add a further criteria that says; if the value in row 4 increases above 90% and reaches 95% then I must return a value that is 10 for 90% but "stretches" to 12 for 95%. Anything below 80% should return a value of 0, and anything over 95% should return a value of 12.
View 8 Replies
View Related
Jan 15, 2014
I have a big file here : [URL] .....
As you can see, the second column contains abstracts which I want to use to determine categories for each document (each line).
Categories are as followed :
Diabetes prevention -> keywords : sugar, diabet*, insulin*
Obesity prevention -> keywords : weight-loss, fat*, LDL
etc. (5-8 categories)
To target one keyword, I found this formula :
=IF(IFERROR(SEARCH("*diabet*"; $B3); 0); "Diabetes prevention"; " ")
What I would like to know is how to add more than one keywords in this formula.
I would also like to know, if it is even possible, how to put every categories in this column, so that i don't have a category per column ... It's hard to explain.
I want to have in front of each line the corresponding column. It means that the formula has to search for every keywords and select one or more appropriate categories.
View 2 Replies
View Related
Jun 6, 2014
I tried to search the forum for my unique problem but had no luck. As you will see attached, I have a series of excel buttons I need to duplicate and have target a different set of cells. I am hoping there is a way to avoid manually doing this.
The goal of the document is to push the button when both colours in the row and column interact (research on birds). There are two additional behaviours with an exact same set of buttons but they need to target "Body Rush" and "Food Displacement" tables underneath. I need all the buttons on one page since multiple behaviours happen simultaneously that need to be recorded.
I have something like 100+ modules in VBA I have created, I am hoping there is an easier way to do this so I don't have to create another ~200 modules in order to get the last two behaviours setup.
I am hoping there is either an easier button system or way to make buttons adjust somehow.
I am not the most advanced VBA user
View 6 Replies
View Related
Mar 27, 2008
Below is the code I used in order to calculate the price of an item depending on the number of items ordered, where the items are in column "I" and their corresponding prices in column "J"
The "If" and all of the "ElseIf" statements work fine except for the last "ElseIf" statement. It's not any different from the ones above it though.
Private Sub cmdCalculatePrice_Click()
Dim strDish As String
strDish = Range("B21").Value
Dim intQuantity As Integer
intQuantity = Range("C21").Value
Dim intPrice As Integer
View 4 Replies
View Related
Dec 3, 2012
I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies
View Related
Jun 4, 2008
I am using a piece of track changes VBA code mentioned on this site, which among other things creates a new column that reports the number of any cell that has been changed (e.g., $K$32). What I would like to do is, next to that cell, report the title of the row in which that cell appears. In other words, if someone changes cell $K$32, for easier reference I'd like others to be able to see that this cell appears in a row titled "New Sales". I'll be happy to clarify with more specifics if need be.
View 2 Replies
View Related
Apr 21, 2013
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"),
Sheets("Sheet1").Range("C7").End(xlDown)).Copy
Sheets("Sheet8").Range("A5").End(xlDown)
End If
[Code]...
View 4 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
Jun 16, 2007
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
View 6 Replies
View Related
Jul 6, 2007
I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.
is there a way to track changes on multiple selected ranges on multiple sheets
View 9 Replies
View Related
Jun 15, 2008
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 Related
Oct 23, 2013
I have an excel document with hundreds of columns. Each column should not be empty; they must have at least one value. Right now I'm applying a filter and going through each column one by one to make sure that it is not empty. How can I perform this check without having to go through each column one by one?
View 6 Replies
View Related
May 19, 2014
File A has patients with discharge dates over two years. Each patient may have multiple dates. Example:
Patient Discharge Date
John Smith 7/1/2012
John Smith 10/1/2012
Judge Judy 7/1/2013
Judge Judy 12/1/2013
File B has office visit dates for the patients. Again, each patient has multiple office visits. I need to be able to see which office visits were within 15 days of a discharge date. Example:
Patient Office Visit
John Smith 6/1/2012
John Smith 6/15/2012
John Smith 7/10/2012
Judge Judy 7/20/2013
Judge Judy 12/12/2013
Is there any way to note next to the discharge date that for John Smith the 7/10/2012 office visit was within 15 days of the 7/1/2012 discharge? And the same for Judy with the 12/12/2013 visit?
View 8 Replies
View Related
Mar 12, 2014
Is there a way to get the data at the intersection of a column that meets a criteria using Index:Match and a row that meets criteria using Index:Match?
For instance, if my data is:
John
Jim
Kelly
Sept
0
0
10
Oct
10
0
0
Nov
0
10
0
How can I use an Index:Match combination to get the value at the intersection of Jim and Nov? Keeping in mind that my data is a bit more complicated than this, the table has 20+ rows and 10+ columns.
View 3 Replies
View Related
Aug 8, 2007
how to write a macro that reads the point where a cell and collumn intersect?
Below is my example:
1 6.25 3 9
7.25 10960700 10711700 10481100 10267000
7.5 10756600 10513300 10288000 10078800
7.75 10557200 10319500 10099400 9895000
8 10362400 10130100 9915100 9715400
8.25 10172200 9945200 9735100 9539900
How do I write a macro to find the intersection of 7.75 and 3, which is 10099400?
View 4 Replies
View Related
Apr 10, 2014
I am creating a Spreadsheet to work with a game which I am designing. I want to create a combat calculator which will generate formulas based on which class a person chooses to be, and their choice of weapons, armour and other equipment (makes things a lot easier for me to visualize). I have created the first worksheet which will act as the interface where users can input their character configurations and a second worksheet where all of the data is stored, included items and their values.
Assuming that on the interface I have cell A1 which will let the user select their class (Warrior, Archer, Sorcerer).
I also have cells B1 = Helmet, B2 = Body Armour, B3 = Leg Armour, B4 = Feet Armour, B5 = Hand Armour.
I would like cells B1:B5 to create a Data Validation based on the class which is selected in A1.
E.g.
IF A1="Warrior"; B1 = A list of Melee Helmets, B2 = A list of Melee Body Armour... etc
IF A1="Sorcerer"; B3 = A list of Magic Leg Armour...
I have managed to do this with the following formula, but only if the data is stored in the same sheet.
Cell B1
=IF(A1="Warrior", =meleeArmour1, =IF(A1="Archer", =rangeArmour1, =magicArmour1))
Cell B2
=IF(A1="Warrior", =meleeArmour2, =IF(A1="Archer", =rangeArmour2, =magicArmour2))
[NOTE]
=meleeArmour1 represents the range of cells where the Melee Helmets/Head Armour is stored.
=magicArmour2 represents the range of cells where the Magic Body Armour is stored.
The group cell names do not seem to work if they are on a different sheet, nor does it work if I replace the group cell name (=meleeArmour1) with the full formula (=Sheet2!A$1$:A$10$)
View 5 Replies
View Related
Mar 30, 2014
I am trying to create a table that references data in a much larger report that constantly has rows and columns added and deleted off of it. Here is an example:
---------------Juice Sell In-----Desert Sell in-----Total Sales
James--------34%---------------8%---------------22
Frank---------22%---------------2%---------------18
This is a simple example of what I would be trying look data up from. But there are hundreds or rows and columns in the actual report, so I am creating another document that just pulls the data I need to review off of the larger report. New rows and columns are added to the report on a daily basis. So I am trying to have a cell return a value as a result of an intersection of 2 other values that remain constant. For example: Looking up the intersection of the row that contains "James" and the column that contains "Total Sales" would return "22" as the result in the cell.
View 3 Replies
View Related
Mar 3, 2014
I am making a rough gantt chart in excel for a project schedule. Japanese at work love excel fro some reason that is why I am trying to do this. I have column headings for days ( 3/3, 3/4, 3/5, etc) and I want to find a way to mark the intersection when a date is input in another row. I tried to do some conditional formatting using symbols, but couldn't get anything to work.
see picture. I want to mark at correct date according to column headings when date is placed in column E or F. This example shows column E with 3/10 so I want to make a value appear at intersection AS and the row 3/10 is on. Would like to get a symbol to appear there if possible.
excel help.png
View 5 Replies
View Related
Jun 24, 2006
I have the following data sets
worksheet1
TRANCODES 234568 .........99
PARTS
003-0623-00
005-0030-00
worksheet2
PARTSTRANCODE SUM_QTY
003-0018-01 28 10
003-0018-01 40 10
003-0021-00 8 25
003-0021-04 4 63
In worksheet1 I need to compare the part number and the trancode then
return the quantity from worksheet2 -
View 14 Replies
View Related