I have a code which locates a value in a column, and locates a word in a row. I've then tried to return the value that intersects these to findings. eg. The code finds a value in "A10", and a value in "E5", The code then needs to return the value in "A5".
View 9 Replies
I have a 10 x 15 matrix named "Range1"
I have named the range of Column headings in that matrix as "Weight", and the range of Row headings as "Height". And then all the matrix cells contain some values.
Now I need to find the value from intersection of height and weight (please see the attached excel file)
Attention: I need to use excel VBA code to do the trick. (Not Worksheet formula using for example INDEX and MATCH)
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 -
I have 100 rows and 100 columns, where row 1 is dates, and column A is times, and within are numbers. On a second sheet I have a column (A) with random dates and another (B) with times. What I need is a third column (C)on the second sheet that looks at the date and time, finds the intersection on the first sheet, and returns the number found there.
View 14 Replies View RelatedI have store numbers (#3, #5, #6) (not consecutive, but ascending right to left) across the top, and dates (11/19/2013) ascending down the left.
I know date, I know store number. I need VBA code (excel 2000) to move cursor to the intersection of the two.
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 RelatedAny code or formula to highlight the intersected cells between a column and a row for any selected cell ?
Ex: if i select E25 the cells of column E1:E25 as well as the cells of Row A25:E25 to be highlighted in a certain color , and so on for any selected cell..
There used to be a function called "snap to grid". What happened to it? I want to draw a line and make it "snap" so that the ends of the line are at the intersections of cells.
View 3 Replies View RelatedI'm looking for a VBA Macro that will do the following:
1. Count number of cells in a given row that contain special character "*"
2. If there is an asterisk in a given row, return the column header(s) contained in row 1 of the spreadsheet that correspond to each instance of cell(s) that contains the "*".
Is there a way to do this? I've attached an example of what I'm talking about, (Column A shows the desired result.)
Formula Question.xlsx
On Pipe worksheet, pipe numbers 1-203 are listed in column A. Each pipe number has a flow rate that is listed in column E. I need it to look up the pipe number in column A on the Job worksheet and report the flow rate (from the Pipe worksheet) in column D for each pipe.
View 5 Replies View RelatedHi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedIs there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies View RelatedIs 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.
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?
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.
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
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.
I am having to figure out a particularly painful excel formula. What i need is a formula that will count the number of times an issue label in text shows up, but it has to match a certain time.
we can get the excel sheet to round the time to the nearest thirty minutes, so if a call comes in at 3:12pm it rounds it to 3pm. We label the calls that come in with a issue description also like "dvr issue." So i need a formula that will allow me to count the number of instances where if it says 3pm AND dvr issue it will only count it and give me a total in a specified cell. Is that possible?
First although it returns the correct column, it always returns Row 1. Beyond me! Secondly I could really use some help in a line or two of code to select the cell at the intersection of the column and row.
Sub test()
Dim name As String
Dim number As String
Dim col As Integer
Dim row As Integer
Dim RowNumber As Integer
Dim ColNumber As Integer
Dim rng As Range
ActiveWorkbook.Sheets("Sheet1").Activate
name = InputBox("type a name from Column A")...........
I am trying to find out if i can reference to a table on Excel. For example; if i enter in one cell the word 'january' (a header on the left of the table) and 'week 1' (a header on the top of the table) in another cell, can i set it to produce the value in the table that coresponds to the two headers.
View 2 Replies View RelatedI cannot figure the formula for finding an intersection of two points on a seperate worksheet. I have attached the workbook.
View 6 Replies View RelatedBasically, I have a simple - but large (48Mb) sheet with UK mileage distances between towns. As the are 1,000 towns and therefore 1,000,000 cells of data it is a little tedious to scroll to the desired town. see sample attached (this is not the real file obviously!)
What I would like to do is type in a start and end point, and have Excel look for the intersection and display the value of this.
I have a large spreadsheet that has row and column headers. I am trying to build a user form that will basically have a row combo box (already populated with the row headers), a column combo box (already populated with the column headers) and a text box. I need what ever I type into the text box to be inserted into the cell where the row of what I pick in the row combo box and the column of what I picked in the column combo box intersect.
For Example:
If I pick "Apple" in the row combo box which is in cell B1 and "Red" in the column combo box which is cell A4 to insert what ever I put in the text box into cell B4.
I need to compute the number of days that a given range of dates has in common with any given month. So an example might be:
Cell A1: Range Start Date (say it's 1/1/07)
Cell A2: Range End Date (say it's 2/15/07)
Cell A3: Month Indicator (say it's 2, meaning February)
Cell A4: Days of Intersection (should be 15 in this case, meaning that 2/1-2/15 were the days of February that were also in the range 1/1-2/15)
Cell A4 is what I'm trying to create the formula for.
I'm in Excel 2000 and am having trouble installing the add-on.
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
i have a table in excel with 36 rows and 36 columns i.e. 36x36=1296 individual cells. Columns and rows have headers/labels i.e. 36 rows with row label headings and similarly 36 columns with column label headings. I would like to automatically create unique cell names for the 1296 cells based on where the columns and rows intersect using the row and column labels. The cell name format I would like to create is [ColumnLabel_RowLabel].
I've manually created the cell names in the attached file as an example of what i would am seeking (i've greyed the cells that I've done this for).
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]...
I need find the date 1/1/2014 in Row A and from the same colum get the average from row B:z.
View 1 Replies View RelatedI have a huge data base (daily temperatures dating back to 1872), but for simplicity I have limited the values to just a month for this question. What I want to do it list the ten warmest temperatures and then return the date in which it occurred. I am using the following LARGE function to get the ten warmest temperatures (Column B - high temperatures).
=LARGE($B$2:$B$31,$D2)
Column B = High Temp in Data Table
Column D = Rank in Results Table
This works great. However when I use the following Index function to get the date in which it occurred (Column A - Date), it keeps returning the first date in which it occurred when there are multiple occurrences of the same temperature.
=INDEX($A$2:$A$31,MATCH(LARGE($B$2:$B$31,$D2),$B$2:$B$31,0))
Column A = Date in Data Table
Column B = High Temp in Data Table
Column D = Rank in Results Table
In some cases, I have more than 2 occurrences of the same temperatures. For example, the high temperature of 23 degrees occurs 3 times during the month (1/4/2013, 1/11/2013, and 1/12/2013), but only the first one 1/4/2013 shows up in my results table. I would like the other dates to show up. These tables are listed below.
Data TableResults Table
DateHigh TempRankHigh TempDate
1/2/201311271/5/2013
[Code].....
I have a bunch of domain names (including subdomains) in column B
In column A i need just the raw domain name...
example
a1 [domain.com]
b1 [domain.com]
a2 [domain.com]
b2 [mail.domain.com]
a3 [domain.com]
b3 [subdomain.domain.com]
