Prev: Repost - Functions on filtered data
Next: convert vertical to horizontal
From: sjoseph371 on 25 Mar 2010 13:13
I have a spreadsheet with several different worksheets. On Worksheet 1, I
have the following:
(Col A) Structure Number, (Col B) Opening Height 1, (Col C) Opening
Direction 1 (N, S, E, W), (Col D) Opening Height 2, (Col E) Opening Direction
2 (N, S, E, W), (Col F) Opening Height 3, (Col G) Opening Direction 2 (N, S,
E, W), (Col H) Opening Height 4, (Col I) Opening Direction 4 (N, S, E, W)
There are over 100 rows, 1 for each structure. Some structures have just 1
opening, and some have multiple openings.

On Worksheet 2, the user will enter:
(Col A) Structure Number and (Col B) Opening Direction. I want Column C to
search Worksheet 1 and give the Opening Height automatically.

For example, Worksheet 1 has:

(A) (B) (C ) (D) (E) (F) (G)
(row) STR # OPENING1 OPENING1 OPENING2 OPENING2 OPENING3 OPENING3
HGT DIR HGT DIR HGT DIR
1 95-01 2.400 NE 2.300 SE
2 95-02 3.050 SW
3 95-03 4.900 E 4.900 W
4 95-04 4.880 SW 4.880 E 5.730
NE

On Worksheet 2, when the user enters 95-01 in Col A and SE in Col B, I want
the program to automatically give me 2.300 in Col C. Is there a function (or
combination of functions) that lets me do this.
From: Eduardo on 25 Mar 2010 13:46
Hi,

=index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000))

change range to meet your needs

"sjoseph371" wrote:

> I have a spreadsheet with several different worksheets. On Worksheet 1, I
> have the following:
> (Col A) Structure Number, (Col B) Opening Height 1, (Col C) Opening
> Direction 1 (N, S, E, W), (Col D) Opening Height 2, (Col E) Opening Direction
> 2 (N, S, E, W), (Col F) Opening Height 3, (Col G) Opening Direction 2 (N, S,
> E, W), (Col H) Opening Height 4, (Col I) Opening Direction 4 (N, S, E, W)
> There are over 100 rows, 1 for each structure. Some structures have just 1
> opening, and some have multiple openings.
>
> On Worksheet 2, the user will enter:
> (Col A) Structure Number and (Col B) Opening Direction. I want Column C to
> search Worksheet 1 and give the Opening Height automatically.
>
> For example, Worksheet 1 has:
>
> (A) (B) (C ) (D) (E) (F) (G)
> (row) STR # OPENING1 OPENING1 OPENING2 OPENING2 OPENING3 OPENING3
> HGT DIR HGT DIR HGT DIR
> 1 95-01 2.400 NE 2.300 SE
> 2 95-02 3.050 SW
> 3 95-03 4.900 E 4.900 W
> 4 95-04 4.880 SW 4.880 E 5.730
> NE
>
> On Worksheet 2, when the user enters 95-01 in Col A and SE in Col B, I want
> the program to automatically give me 2.300 in Col C. Is there a function (or
> combination of functions) that lets me do this.
From: Eduardo on 25 Mar 2010 14:03
opps I missed something use

=index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000,0))

"Eduardo" wrote:

> Hi,
>
> =index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000))
>
> change range to meet your needs
>
> "sjoseph371" wrote:
>
> > I have a spreadsheet with several different worksheets. On Worksheet 1, I
> > have the following:
> > (Col A) Structure Number, (Col B) Opening Height 1, (Col C) Opening
> > Direction 1 (N, S, E, W), (Col D) Opening Height 2, (Col E) Opening Direction
> > 2 (N, S, E, W), (Col F) Opening Height 3, (Col G) Opening Direction 2 (N, S,
> > E, W), (Col H) Opening Height 4, (Col I) Opening Direction 4 (N, S, E, W)
> > There are over 100 rows, 1 for each structure. Some structures have just 1
> > opening, and some have multiple openings.
> >
> > On Worksheet 2, the user will enter:
> > (Col A) Structure Number and (Col B) Opening Direction. I want Column C to
> > search Worksheet 1 and give the Opening Height automatically.
> >
> > For example, Worksheet 1 has:
> >
> > (A) (B) (C ) (D) (E) (F) (G)
> > (row) STR # OPENING1 OPENING1 OPENING2 OPENING2 OPENING3 OPENING3
> > HGT DIR HGT DIR HGT DIR
> > 1 95-01 2.400 NE 2.300 SE
> > 2 95-02 3.050 SW
> > 3 95-03 4.900 E 4.900 W
> > 4 95-04 4.880 SW 4.880 E 5.730
> > NE
> >
> > On Worksheet 2, when the user enters 95-01 in Col A and SE in Col B, I want
> > the program to automatically give me 2.300 in Col C. Is there a function (or
> > combination of functions) that lets me do this.
From: sjoseph371 on 25 Mar 2010 14:16


"Eduardo" wrote:

> opps I missed something use
>
> =index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000,0))
>
> "Eduardo" wrote:
>
> > Hi,
> >
> > =index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000))
> >
> > change range to meet your needs
> >
> > "sjoseph371" wrote:
> >
> > > I have a spreadsheet with several different worksheets. On Worksheet 1, I
> > > have the following:
> > > (Col A) Structure Number, (Col B) Opening Height 1, (Col C) Opening
> > > Direction 1 (N, S, E, W), (Col D) Opening Height 2, (Col E) Opening Direction
> > > 2 (N, S, E, W), (Col F) Opening Height 3, (Col G) Opening Direction 2 (N, S,
> > > E, W), (Col H) Opening Height 4, (Col I) Opening Direction 4 (N, S, E, W)
> > > There are over 100 rows, 1 for each structure. Some structures have just 1
> > > opening, and some have multiple openings.
> > >
> > > On Worksheet 2, the user will enter:
> > > (Col A) Structure Number and (Col B) Opening Direction. I want Column C to
> > > search Worksheet 1 and give the Opening Height automatically.
> > >
> > > For example, Worksheet 1 has:
> > >
> > > (A) (B) (C ) (D) (E) (F) (G)
> > > (row) STR # OPENING1 OPENING1 OPENING2 OPENING2 OPENING3 OPENING3
> > > HGT DIR HGT DIR HGT DIR
> > > 1 95-01 2.400 NE 2.300 SE
> > > 2 95-02 3.050 SW
> > > 3 95-03 4.900 E 4.900 W
> > > 4 95-04 4.880 SW 4.880 E 5.730
> > > NE
> > >
> > > On Worksheet 2, when the user enters 95-01 in Col A and SE in Col B, I want
> > > the program to automatically give me 2.300 in Col C. Is there a function (or
> > > combination of functions) that lets me do this.
From: sjoseph371 on 25 Mar 2010 14:19
Thanks for the speedy reply, however, 2 things:
1. I received a REF! error
2. The formula you suggested would not work if the user entered in Str
91-01 Opening Dir NE on Sheet 2. Is there a formula that would check the
whole spreadsheet?
Thanks

"Eduardo" wrote:

> opps I missed something use
>
> =index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000,0))
>
> "Eduardo" wrote:
>
> > Hi,
> >
> > =index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000))
> >
> > change range to meet your needs
> >
> > "sjoseph371" wrote:
> >
> > > I have a spreadsheet with several different worksheets. On Worksheet 1, I
> > > have the following:
> > > (Col A) Structure Number, (Col B) Opening Height 1, (Col C) Opening
> > > Direction 1 (N, S, E, W), (Col D) Opening Height 2, (Col E) Opening Direction
> > > 2 (N, S, E, W), (Col F) Opening Height 3, (Col G) Opening Direction 2 (N, S,
> > > E, W), (Col H) Opening Height 4, (Col I) Opening Direction 4 (N, S, E, W)
> > > There are over 100 rows, 1 for each structure. Some structures have just 1
> > > opening, and some have multiple openings.
> > >
> > > On Worksheet 2, the user will enter:
> > > (Col A) Structure Number and (Col B) Opening Direction. I want Column C to
> > > search Worksheet 1 and give the Opening Height automatically.
> > >
> > > For example, Worksheet 1 has:
> > >
> > > (A) (B) (C ) (D) (E) (F) (G)
> > > (row) STR # OPENING1 OPENING1 OPENING2 OPENING2 OPENING3 OPENING3
> > > HGT DIR HGT DIR HGT DIR
> > > 1 95-01 2.400 NE 2.300 SE
> > > 2 95-02 3.050 SW
> > > 3 95-03 4.900 E 4.900 W
> > > 4 95-04 4.880 SW 4.880 E 5.730
> > > NE
> > >
> > > On Worksheet 2, when the user enters 95-01 in Col A and SE in Col B, I want
> > > the program to automatically give me 2.300 in Col C. Is there a function (or
> > > combination of functions) that lets me do this.
 |  Next  |  Last
Pages: 1 2 3
Prev: Repost - Functions on filtered data
Next: convert vertical to horizontal