Intersect in a formula

[Jeffrey Mahoney]

I learned today that I can return a range from the intersect of two ranges by doing this
=3:14 A:W
Returns A3:W14
There is a space separating those two ranges

Now, how do I get the entire row from a range? If I want to return 3:20 if I have a range A3:A20. More important, I want to give it a named range and have it return the entire row of that range.

 

[awoohaw]

that is pretty neat!

Try:

=3:20 3:20

or just

=3:20

or stripping away the column letters:

=INDIRECT(SUBSTITUTE("A3:A20","A",""))

i'm not sure if that is what you want, though.

 

[Jeffrey Mahoney]

So here is how I want to use it. It would be similar to VBA code like this:
Rng = Intersect(Range("LE_UpperIDCol").EntireRow,Range("LE_Adj[Auto Adjust]"))
This would give me the same set of rows as LE_UpperIDCol but in the LE_Adj[Auto Adjust] column

I keep getting surprised by Excel, so I thought there must be somebody out there that has done this.

I tried getting the row of the upper cell and the row of the lower cell in the LE_UpperIDCol range and use it like this:
=MIN(ROW(LE_UpperIDCol)):max(ROW(LE_UpperIDCol))
But Excel won't let me use formulas to get the rows like =4:738

Jeff

 

[awoohaw]

So here is how I want to use it. It would be similar to VBA code like this:
Rng = Intersect(Range("LE_UpperIDCol").EntireRow,Range("LE_Adj[Auto Adjust]"))
This would give me the same set of rows as LE_UpperIDCol but in the LE_Adj[Auto Adjust] column

I keep getting surprised by Excel, so I thought there must be somebody out there that has done this.

I tried getting the row of the upper cell and the row of the lower cell in the LE_UpperIDCol range and use it like this:
=MIN(ROW(LE_UpperIDCol)):max(ROW(LE_UpperIDCol))
But Excel won't let me use formulas to get the rows like =4:738

Jeff

why don't you post a sample of your data? And then below that an example of expected results.

Have you tried using the range reference feature of the INDEX function?

 

[felixstraube]

Is this maybe what you want?:

Book1
	A	B	C	D	E
1	1	2
2	3	4
3
4				1	2
5				3	4
Sheet2

Cell Formulas
Range	Formula
D4:E5	D4	=INDIRECT("1:2") INDIRECT("A:B")
Dynamic array formulas.

But as @awoohaw said if you explain what you want to accomplish we may come up with other (maybe better) solutions.

 

[felixstraube]

But Excel won't let me use formulas to get the rows like =4:738

Why do you say this? =4:738 should work just fine. You may have problems if there is a spill or a circular reference.

 

[Jeffrey Mahoney]

My data is too sensitive to post.

My table has an upper half and a lower half. I want to be able to quantify the values in the top half separately. I have a dynamic named range in the left most column that contains project IDs called LE_UpperIDCol which is only one column wide. The number of rows in this dynamic range will change. There are 20 fields in this table. I didn't want to create 20 different named ranges for each field in the upper section. So . . . .

I was needing the intersection of each column related to the rows in the dynamic range LE_UpperIDCol. What I have done to solve it is to create another dynamic named range called LE_Upper which has the same rows as the other range but also includes all the columns in the table. Now I can create an intersect formula that will quantify the top half of the table like this:
=SUM(LE_Upper LE_Adj[DATA])
and
=COUNTIF(LE_Upper LE_Adj[Auto Adjust],"X")

Thank you everybody for helping.