Indirect function
 

A couple weeks ago I posted on here with two tables that I wanted related. I
did get one response but I can't seem to make it work.

Here was my original post:
Here are my two tables:

Table 1

Col A Col B Col C Col D Col E
Col E Col F
Row 1 Slope Bands (%) R1 RS RE9 RE11 RE15
RE20
Row 2 0 €“ 14.99 0.5 0.45 0.4 0.4 0.35
0.35
Row 3 15 €“ 29.99 0.45 0.4 0.35 0.35 0.3
0.3
Row 4 30 €“ 44.99 0.4 0.35 0.3 0.3
0.25 0.25
Row 5 45 €“ 59.99 0.35 0.3 0.25 0.25
0.2 0.2
Row 6 60 €“ 99.99 0.3 0.25 0.2 0.2 0.15
0.15
Row 7 100 + 0 0 0 0 0
0

Table 2
Col 1 Col 2 Col 3
Row 1 ZONE Area Slope
Row 2 R1-1 31.71 0-14.99%
Row 3 R1-1 32.72 15-29.99%
Row 4 R1-1 63.70 30-44.99%
Row 5 R1-1 17.29 45-60.99%
Row 6 RE9-1 6474.83 45-60.99%
Row 7 RE9-1 19602.34 15-30.99%
Row 8 RE9-1 4438.14 0-14.99%

What I need to be able to do is to create a formula that multiplies the
value in table 1 in columns 2-7 that corresponds to the slope and the zone
with the lot area in table 2. For instance, I need to find a formula that
will choose to multiply the value in table 1 Row 5, Col 4 with table 2 row,
6, column 3. I want it to know which value to pull from table 1 to multiply
with in table 2 so the zone and slope categories match up....Is this
possible?


RESPONSE:
I got two responses, but neither worked. However, I would like to try to
make the following work.

Scariest (and only) formula used:
=INDIRECT(Zone) INDIRECT(Slope)
http://www.mediafire.com/file/gdj2yo...12_23_09a.xlsx


There is a link to Herbert's solution, but I would like someone to walk me
through how you created the names and lists and how the indirect feature
works? I think my biggest trouble is figuring out what to do with both the
"Slope" names...and the relationship between the two indirect's. Basically
the slope name does not recognize the slope in both tables when I create a
name.

Thanks so much for your help.

Indirect function
 

Let's see if I understand this...

R1-1 31.71 0-14.99% = 31.71*0.45
R1-1 32.72 15-29.99% = 32.72*0.4
R1-1 63.70 30-44.99% = 63.70*0.35
R1-1 17.29 45-60.99% = 17.29*0.30

Is that correct?

--
Biff
Microsoft Excel MVP


"JD" wrote in message
...
A couple weeks ago I posted on here with two tables that I wanted related.
I
did get one response but I can't seem to make it work.

Here was my original post:
Here are my two tables:

Table 1

Col A Col B Col C Col D Col E
Col E Col F
Row 1 Slope Bands (%) R1 RS RE9 RE11 RE15
RE20
Row 2 0 - 14.99 0.5 0.45 0.4 0.4 0.35
0.35
Row 3 15 - 29.99 0.45 0.4 0.35 0.35
0.3
0.3
Row 4 30 - 44.99 0.4 0.35 0.3 0.3
0.25 0.25
Row 5 45 - 59.99 0.35 0.3 0.25 0.25
0.2 0.2
Row 6 60 - 99.99 0.3 0.25 0.2 0.2 0.15
0.15
Row 7 100 + 0 0 0 0
0
0

Table 2
Col 1 Col 2 Col 3
Row 1 ZONE Area Slope
Row 2 R1-1 31.71 0-14.99%
Row 3 R1-1 32.72 15-29.99%
Row 4 R1-1 63.70 30-44.99%
Row 5 R1-1 17.29 45-60.99%
Row 6 RE9-1 6474.83 45-60.99%
Row 7 RE9-1 19602.34 15-30.99%
Row 8 RE9-1 4438.14 0-14.99%

What I need to be able to do is to create a formula that multiplies the
value in table 1 in columns 2-7 that corresponds to the slope and the zone
with the lot area in table 2. For instance, I need to find a formula that
will choose to multiply the value in table 1 Row 5, Col 4 with table 2
row,
6, column 3. I want it to know which value to pull from table 1 to
multiply
with in table 2 so the zone and slope categories match up....Is this
possible?


RESPONSE:
I got two responses, but neither worked. However, I would like to try to
make the following work.

Scariest (and only) formula used:
=INDIRECT(Zone) INDIRECT(Slope)
http://www.mediafire.com/file/gdj2yo...12_23_09a.xlsx


There is a link to Herbert's solution, but I would like someone to walk
me
through how you created the names and lists and how the indirect feature
works? I think my biggest trouble is figuring out what to do with both
the
"Slope" names...and the relationship between the two indirect's. Basically
the slope name does not recognize the slope in both tables when I create a
name.

Thanks so much for your help.

Indirect function
 

Excel 2007
Same file, better annotated.
http://c0444202.cdn.cloudfiles.racks...12_23_09a.xlsx