Row & Column Intersection Value

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?

ADVERTISEMENT

Select Intersection Of Column And Row

Sep 11, 2008

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")...........

View 9 Replies View Related

Highlighting Column And Row Intersection Cells

May 1, 2008

Any 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..

View 9 Replies View Related

Display Value Of Intersection If Enter Row And Column Data?

Dec 19, 2013

Basically, 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.

View 1 Replies View Related

Insert Contents Of Text Box In Row / Column Intersection?

Jun 18, 2013

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.

View 3 Replies View Related

Cell/Range Names Based On Row & Column Intersection

Jun 15, 2008

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).

View 9 Replies View Related

How To Get Data At X And Y Intersection

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

Lookup Intersection Of Values?

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

Mark Intersection Of Two Items?

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

Sumif To Return An Intersection

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

Date Ranges Intersection

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

Cross Reference Maybe Intersection

Feb 4, 2010

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?

View 10 Replies View Related

Find Intersection From Known Values

Nov 20, 2013

I 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.

View 9 Replies View Related

Lookup Intersection Of 2 Headings

Nov 14, 2006

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 Related

Find Two Cells Then Return Value At Intersection

May 26, 2008

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 View Related

Intersection Point Return Data

Jul 24, 2006

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 Related

Look Up 2 Number Ranges And Find Intersection

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

Find & Return Intersection Of Matrix

Aug 20, 2008

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)

View 6 Replies View Related

Finding Intersection On Seperate Worksheet

Sep 10, 2006

I cannot figure the formula for finding an intersection of two points on a seperate worksheet. I have attached the workbook.

View 6 Replies View Related

Snap To Grid - End Of Line At Intersection Of Cells

Nov 23, 2011

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 Related

Intersection Of Date Range And Current Month

Jan 9, 2007

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.

View 9 Replies View Related

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 View Related

Excel 2010 :: Intersection Of Named Ranges (same Worksheet) Doesn't Work?

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

Convert Column B As Multiple Column Titles And Move Data In Column C Into New Columns?

Jun 30, 2014

I have a excelsheet that looks like this:

Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4

And I am trying to make it look like this:

Fire Dept
Health Services
Internal Services
Public Works
Social Services

Los Angeles
3
12

New York
8
22
100
7

Chicago

15

56
4

View 8 Replies View Related

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

Jan 31, 2008

I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

View 9 Replies View Related

If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right

Apr 22, 2009

What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.

View 4 Replies View Related

Count Values Of Column If Column Header Is Equal To Name In Column A?

Apr 25, 2014

For column "B" count all the 1's if column header is equal to name in column A.

For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.

View 5 Replies View Related

Compare Column B With All Rows In Column A If Match Place In Column C?

Aug 13, 2014

I want to compare two columns. I would like to see if the contents of column B appear anywhere in column A, for any amount of rows, and if it does, to place the match in col C.

So in the example below, red in B1 would be checked in A1 through A1000 or however long A is. When any row has red, place that match in that row for C.

This would be repeated until all rows in B are complete.

Example
Col A: red
Col B: red
Col C: red

Col A: red
Col B: blue
Col C: not found

View 6 Replies View Related

Set Of Column Data Move To Different Column And Delete Unwanted Column VBA

May 29, 2014

I have set of data Pasted in 4th row, in the top row 44 columns values assigned i want move data from set of data to different column among these 44 columns

Like "Service Order ID" is 1 column in set of data ,it move to second column of top row

Some of column need to delete. (Service Order Type,Service Order Description,Created By,Status,Contact,Expected Delivery Date,

Creation Date,Priority,Net Value,Currency,External Reference,Reference Date)

I want Get output result in same sheet (Actual).

View 3 Replies View Related

Subtracting Column B From A, And Leave Third Column Blank If No Value In Column B?

Oct 6, 2008

I have a spreadsheet with three columns (A,B,C). I want the third column C to be column A - B (A minus B) for each row, but only if there is a value in column B.

If there is no value in column B, then I want that row in Column C to just stay blank.

View 4 Replies View Related

Find Rows W/same Value In Column A And B And Offset Column C To Next Available Column

Aug 3, 2006

I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.

0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities
0014B23<@28>Coursey<@28>Ogles<@28>Shelocta

Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.

0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B23<@28>Coursey
0014B23<@28>Ogles
0014B23<@28>Shelocta
0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches)
0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches)
0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............

View 9 Replies View Related

Count Column A If Value Of Column B Is YES And Column C Is Equal To 5

Apr 27, 2004

=COUNTIF(A19:A28,(AND(B19:B28="YES",C19:C28=5)))

What is wrong with my formula above? I am trying to count Colunm A if the value of Column B is "YES" and Column C is equal to "5".

View 8 Replies View Related

Check One Column, Get Value From A Second Column And Display Sum In 3rd Column

May 27, 2007

I am tying to total all the sales for a given customer. This is what I have at present

Customer Sales
CustA 1000
CustB 500
CustA 1250
CustC 750

Elsewhere in the spreadsheet I have the following

Customer Total Sales
CustA
CustB
CustC

As you can probably see I need to get the sum of each customers sales and display this under Total Sales. I thought about using VBA but am not really sure where to start.

If anyone has any ideas how I could progress this I would be most grateful. I am using excel 2007

View 9 Replies View Related

Creating Single Column List From Multi-row / Column Table And Removing Blanks

Mar 7, 2014

I'm trying to come up with a single formula to create a single column list from a table with blanks.
a

b

c
d

e

f
g

To
a
b
c
d
e
f
g

I know I've done this before but having trouble visualizing today.

View 14 Replies View Related

Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.

Mar 13, 2009

I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.

I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.

View 2 Replies View Related

Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month

May 8, 2009

how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:

If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008

Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.

View 3 Replies View Related

Multiply Every Nth Cell By Another Column Whith Merged Cells, THEN Find Column Total

Aug 6, 2009

Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".

In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.

If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.

View 8 Replies View Related

Find Most Recent Date In Column Headings In A Range - Insert New Column And Heading

Apr 4, 2014

I have a 2 groups of column headings with a different month and year in each heading so

1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"

2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"

Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"

Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"

Because the ranges keep changing month over month, how do i do this.

View 4 Replies View Related

Adding Column Widths Of Merged Area To Match Single Column Width

Sep 19, 2012

I needed to match the width a merged area of seven columns to a single column width (for row autofitting). Adding the column unit values and setting the single column to that value produced a significantly narrower width.

The documentation mentions that the column width unit is scaled to the font type and size and the absolute width is given in points.

This is set by the normal style setting in Excel Options or by VBA application.standardfontsize = 8 (in this case).

For instance, ten columns of Arial font 8 at 8.5 units you would think to be equivalent to a single column of 85 units.
In points, the difference is 420 vs. 386.25, or 33.75 points.

Well, the standard character zero has a width at this setting of 4.5 points and 1 unit is 8.25 points, leaving 3.75 points for margins.

Then (10-1) margins allowances time 3.75 points resolves the difference.

Determining the margin allowances is straightforward, and reveals that the gradation with size is stepped by MS design.
For instance, sizes up to 11 use 3.75 points for margins and increasing points for characters (except between 9 & 10).
Sizes 12 through 18 use 5.25 points, 20 & 22 use 6.75, 24 & 26 use 8.25, etc.

I have created a table for this purpose, however I rarely use a "normal" other than 8, so I can probably use that set in programming.

View 1 Replies View Related

Excel 2010 :: Count Distinct Text Values In One Column If Another Column Equals A Value

Mar 5, 2014

I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.

For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5

The results shoud be: (2) Because I only want to count the P25 one time.

I'm using Excel 2010

View 2 Replies View Related

Formula To Pull Latest Date From One Column Based On Entry In Adjacent Column

Jun 6, 2014

Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed
Disposal Order
Latest Decision date for D.O.

06/05/2014
D.O.001

[Code] ........

View 6 Replies View Related

ADVERTISEMENT