VBA for intersection of two named ranges

I'm sorry. The answer has got to be out here somewhere, but I feel like I am beating my head against the floor.

A one sheet workbook.

Four named vertical ranges of 50 items each (Lets pretend each of the four range names is a plant types).

50 named horizontal ranges of 4 items each (each range name is a 2 letter state abbreviations).

For simplicity, let's say that the resultant table is of the words "No", "Poor","Good" "Great".

The user selects a state and a "plant" from two validation set drop down lists. The workbook change event is triggered, and there goes the code to determine the word or value in the cell that is at the intersection of the two, let's say again for simplicity "MI" and "Banana Tree", which would probably result in a "NO".

I can write the names of the ranges separated by a space after an = in a cell and easily get the result, but I sure can't figure out how to transfer the result of two drop-down boxes into same formula (at least not so it works), and all the more worse to do so via VBA. I would LOVE to be able to do so both ways, but just one would save me. Please help before my head explodes.

Haven't we all beat our heads.

Are you using dropdown boxes on the sheet, or in a user created form?

I like creating vba forms with OK & Cancel buttons and list boxes and drop
down combos, you know.

Let me know if this would help.

"brucemc" wrote:

>
> I'm sorry. The answer has got to be out here somewhere, but I feel like
> I am beating my head against the floor.
>
> A one sheet workbook.
>
> Four named vertical ranges of 50 items each (Lets pretend each of the
> four range names is a plant types).
>
> 50 named horizontal ranges of 4 items each (each range name is a 2
> letter state abbreviations).
>
> For simplicity, let's say that the resultant table is of the words
> "No", "Poor","Good" "Great".
>
> The user selects a state and a "plant" from two validation set drop
> down lists. The workbook change event is triggered, and there goes the
> code to determine the word or value in the cell that is at the
> intersection of the two, let's say again for simplicity "MI" and
> "Banana Tree", which would probably result in a "NO".
>
> I can write the names of the ranges separated by a space after an = in
> a cell and easily get the result, but I sure can't figure out how to
> transfer the result of two drop-down boxes into same formula (at least
> not so it works), and all the more worse to do so via VBA. I would LOVE
> to be able to do so both ways, but just one would save me. Please help
> before my head explodes.
>
>
> --
> brucemc
> ------------------------------------------------------------------------
> brucemc's Profile: http://www.excelforum.com/member.php...o&userid=32871
> View this thread: http://www.excelforum.com/showthread...hreadid=528753
>
>

Just right there on the sheet, trying to make it as simple as possible. If I only made it more complicated I could pretend that there was good reason for me not being able to do something that should be so simple...

This is my interpretation of your request:

Where cells G1 and H1 have the dropdown lists. The code selects the cell
instead of returning the value. Change to suit:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, cc As Range

Set c = Me.Range("G1")
Set cc = Me.Range("H1")
If Intersect(Target, Union(c, cc)) Is Nothing Then Exit Sub
If IsEmpty(c) Or IsEmpty(cc) Then Exit Sub
On Error Resume Next
Intersect(Range(c.Value), Range(cc.Value)).Select
On Error GoTo 0
End Sub

Regards,
Greg


"brucemc" wrote:

>
> I'm sorry. The answer has got to be out here somewhere, but I feel like
> I am beating my head against the floor.
>
> A one sheet workbook.
>
> Four named vertical ranges of 50 items each (Lets pretend each of the
> four range names is a plant types).
>
> 50 named horizontal ranges of 4 items each (each range name is a 2
> letter state abbreviations).
>
> For simplicity, let's say that the resultant table is of the words
> "No", "Poor","Good" "Great".
>
> The user selects a state and a "plant" from two validation set drop
> down lists. The workbook change event is triggered, and there goes the
> code to determine the word or value in the cell that is at the
> intersection of the two, let's say again for simplicity "MI" and
> "Banana Tree", which would probably result in a "NO".
>
> I can write the names of the ranges separated by a space after an = in
> a cell and easily get the result, but I sure can't figure out how to
> transfer the result of two drop-down boxes into same formula (at least
> not so it works), and all the more worse to do so via VBA. I would LOVE
> to be able to do so both ways, but just one would save me. Please help
> before my head explodes.
>
>
> --
> brucemc
> ------------------------------------------------------------------------
> brucemc's Profile: http://www.excelforum.com/member.php...o&userid=32871
> View this thread: http://www.excelforum.com/showthread...hreadid=528753
>
>

Worksheetfunction version that returns the cell value of the intersection:

Where cells G1 and H1 have the dropdown lists.

=INDEX(INDIRECT(H1), ROW(INDIRECT(G1)))

Regards,
Greg


"brucemc" wrote:

>
> I'm sorry. The answer has got to be out here somewhere, but I feel like
> I am beating my head against the floor.
>
> A one sheet workbook.
>
> Four named vertical ranges of 50 items each (Lets pretend each of the
> four range names is a plant types).
>
> 50 named horizontal ranges of 4 items each (each range name is a 2
> letter state abbreviations).
>
> For simplicity, let's say that the resultant table is of the words
> "No", "Poor","Good" "Great".
>
> The user selects a state and a "plant" from two validation set drop
> down lists. The workbook change event is triggered, and there goes the
> code to determine the word or value in the cell that is at the
> intersection of the two, let's say again for simplicity "MI" and
> "Banana Tree", which would probably result in a "NO".
>
> I can write the names of the ranges separated by a space after an = in
> a cell and easily get the result, but I sure can't figure out how to
> transfer the result of two drop-down boxes into same formula (at least
> not so it works), and all the more worse to do so via VBA. I would LOVE
> to be able to do so both ways, but just one would save me. Please help
> before my head explodes.
>
>
> --
> brucemc
> ------------------------------------------------------------------------
> brucemc's Profile: http://www.excelforum.com/member.php...o&userid=32871
> View this thread: http://www.excelforum.com/showthread...hreadid=528753
>
>

I forgot to point out that you need to subtract an offset if the vertical
ranges don't start in the first row. The function would have been better
stated as:

Where OS is the row number of the top of the vertical ranges.

=INDEX(INDIRECT(H1), ROW(INDIRECT(G1)) - OS)

Regards,
Greg

"Greg Wilson" wrote:

> Worksheetfunction version that returns the cell value of the intersection:
>
> Where cells G1 and H1 have the dropdown lists.
>
> =INDEX(INDIRECT(H1), ROW(INDIRECT(G1)))
>
> Regards,
> Greg
>
>
> "brucemc" wrote:
>
> >
> > I'm sorry. The answer has got to be out here somewhere, but I feel like
> > I am beating my head against the floor.
> >
> > A one sheet workbook.
> >
> > Four named vertical ranges of 50 items each (Lets pretend each of the
> > four range names is a plant types).
> >
> > 50 named horizontal ranges of 4 items each (each range name is a 2
> > letter state abbreviations).
> >
> > For simplicity, let's say that the resultant table is of the words
> > "No", "Poor","Good" "Great".
> >
> > The user selects a state and a "plant" from two validation set drop
> > down lists. The workbook change event is triggered, and there goes the
> > code to determine the word or value in the cell that is at the
> > intersection of the two, let's say again for simplicity "MI" and
> > "Banana Tree", which would probably result in a "NO".
> >
> > I can write the names of the ranges separated by a space after an = in
> > a cell and easily get the result, but I sure can't figure out how to
> > transfer the result of two drop-down boxes into same formula (at least
> > not so it works), and all the more worse to do so via VBA. I would LOVE
> > to be able to do so both ways, but just one would save me. Please help
> > before my head explodes.
> >
> >
> > --
> > brucemc
> > ------------------------------------------------------------------------
> > brucemc's Profile: http://www.excelforum.com/member.php...o&userid=32871
> > View this thread: http://www.excelforum.com/showthread...hreadid=528753
> >
> >

Thank-you very much, this looks like it should work, if only I can fix whatever I have done wrong:

In my working VBA with Access I regularly used the ME. reference, but now with Excel it is not recognizing it for some reason and I have to change it to Worksheets(1). - Any idea what would cause this? (Office 2003)

Though the code runs fine (without errors prior to introducing "Indirect" and changing ME to WorkSheets(1)), it does not select the cell - thoughts?

I am getting an undefined variable for INDIRECT - any thoughts on the cause of this too?

Point 1:-
The Me reference should work if you put the code in the worksheet code
module (e.g. Sheet1(Sheet1)) instead of a standard module. I have it working
at my end. Granted, this is all in accordance with my interpretation of your
situation. I may have it wrong.

Point 2:-
There is no reference to "INDIRECT" in my VBA version. The INDIRECT
worksheet function is contained in the worksheet fucntion (wf) that I
intended to be entered in a worksheet cell. I do use "Intersect" however.
Possibly, you may have to qualify it as:

Application.Intersect

There are two references in the code. Try changing both.

Point 3:-
I had both the VBA code and the wf working provided the row offset issue is
accounted for. As usual, I blew the post re the wf version. OS should be the
row number of the top of the vertical ranges - 1. If the vertical ranges
start in row 5 then OS should be 4. You don't need it if they start in the
first row.

Point 4:-
The vertical range dropdown is assumed to be in G1 and the horizontal range
dropdown in H1. You may have to reverse these in the code and wf.

Regards,
Greg

"brucemc" wrote:

>
> Thank-you very much, this looks like it should work, if only I can fix
> whatever I have done wrong:
>
> In my working VBA with Access I regularly used the ME. reference, but
> now with Excel it is not recognizing it for some reason and I have to
> change it to Worksheets(1). - Any idea what would cause this? (Office
> 2003)
>
> Though the code runs fine (without errors prior to introducing
> "Indirect" and changing ME to WorkSheets(1)), it does not select the
> cell - thoughts?
>
> I am getting an undefined variable for INDIRECT - any thoughts on the
> cause of this too?
>
>
> --
> brucemc
> ------------------------------------------------------------------------
> brucemc's Profile: http://www.excelforum.com/member.php...o&userid=32871
> View this thread: http://www.excelforum.com/showthread...hreadid=528753
>
>

Great THANKS! With a little adjusting it works, and works very well.