"VLOOKUP??"

Hi All,

Is it possible to pull data from another sheet using the
VLOOKUP function? I've looked at "Indirect", but that
seems to rely on typing in the name of another Sheet.

What I'd like to do is to establish a range of answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N" inputs. I
would like to put this range on a seperate sheet, let's
say Sheet2. Then on Sheet1 call a VLOOKUP dependent on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This would
occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if I get
this much sorted out, if it can be done, I can add to the
list.

Reason for doing this is that Sheet1 is to be sorted in
several different ways for different Invoice functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions so
far, but in some cases they become rather lengthy. If
there's an easier way, please let me know.

Hope this makes sense......be easy on me guys/gals...rank
beginner here.

Don.....dang, this got lengthy

Yes, just prefix the lookup range with the sheet name

Sheet1!A110

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don" wrote in message
...
Hi All,

Is it possible to pull data from another sheet using the
VLOOKUP function? I've looked at "Indirect", but that
seems to rely on typing in the name of another Sheet.

What I'd like to do is to establish a range of answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N" inputs. I
would like to put this range on a seperate sheet, let's
say Sheet2. Then on Sheet1 call a VLOOKUP dependent on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This would
occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if I get
this much sorted out, if it can be done, I can add to the
list.

Reason for doing this is that Sheet1 is to be sorted in
several different ways for different Invoice functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions so
far, but in some cases they become rather lengthy. If
there's an easier way, please let me know.

Hope this makes sense......be easy on me guys/gals...rank
beginner here.

Don.....dang, this got lengthy

Thanks for the quick response Bob, but I must be doing
something wrong here. Below is what I inserted. By
adjusting the offset this calls up the various answers in
row 1 correctly, which is the Y response. But inserting
N in A1(sheet1) I get #NA. N is stored in A2 of the range
on sheet2...this should reflect the answers across row 2,
as I understand this......what am I missing here??

=VLOOKUP(A1,Sheet2!A12,3)

Don

-----Original Message-----
Yes, just prefix the lookup range with the sheet name

Sheet1!A110

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don" wrote in
message
...
Hi All,

Is it possible to pull data from another sheet using
the
VLOOKUP function? I've looked at "Indirect", but that
seems to rely on typing in the name of another Sheet.

What I'd like to do is to establish a range of answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N"
inputs. I
would like to put this range on a seperate sheet, let's
say Sheet2. Then on Sheet1 call a VLOOKUP dependent on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This
would
occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if I
get
this much sorted out, if it can be done, I can add to
the
list.

Reason for doing this is that Sheet1 is to be sorted in
several different ways for different Invoice functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions so
far, but in some cases they become rather lengthy. If
there's an easier way, please let me know.

Hope this makes sense......be easy on me
guys/gals...rank
beginner here.

Don.....dang, this got lengthy


.

Hi Don,
You asked for the third column of the range and you only have
two columns in the range.

More information on VLOOKUP in
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Don" wrote in message ...
Thanks for the quick response Bob, but I must be doing
something wrong here. Below is what I inserted. By
adjusting the offset this calls up the various answers in
row 1 correctly, which is the Y response. But inserting
N in A1(sheet1) I get #NA. N is stored in A2 of the range
on sheet2...this should reflect the answers across row 2,
as I understand this......what am I missing here??

=VLOOKUP(A1,Sheet2!A12,3)

Don

-----Original Message-----
Yes, just prefix the lookup range with the sheet name

Sheet1!A110

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don" wrote in
message
...
Hi All,

Is it possible to pull data from another sheet using
the
VLOOKUP function? I've looked at "Indirect", but that
seems to rely on typing in the name of another Sheet.

What I'd like to do is to establish a range of answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N"
inputs. I
would like to put this range on a seperate sheet, let's
say Sheet2. Then on Sheet1 call a VLOOKUP dependent on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This
would
occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if I
get
this much sorted out, if it can be done, I can add to
the
list.

Reason for doing this is that Sheet1 is to be sorted in
several different ways for different Invoice functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions so
far, but in some cases they become rather lengthy. If
there's an easier way, please let me know.

Hope this makes sense......be easy on me
guys/gals...rank
beginner here.

Don.....dang, this got lengthy


.

The value in A2 of Sheet2 is probably not N; perhaps N preceded or
followed by one or more spaces?

Alan Beban

Don wrote:
Thanks for the quick response Bob, but I must be doing
something wrong here. Below is what I inserted. By
adjusting the offset this calls up the various answers in
row 1 correctly, which is the Y response. But inserting
N in A1(sheet1) I get #NA. N is stored in A2 of the range
on sheet2...this should reflect the answers across row 2,
as I understand this......what am I missing here??

=VLOOKUP(A1,Sheet2!A12,3)

Don


-----Original Message-----
Yes, just prefix the lookup range with the sheet name

Sheet1!A110

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don" wrote in

message

.. .

Hi All,

Is it possible to pull data from another sheet using

the

VLOOKUP function? I've looked at "Indirect", but that
seems to rely on typing in the name of another Sheet.

What I'd like to do is to establish a range of answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N"

inputs. I

would like to put this range on a seperate sheet, let's
say Sheet2. Then on Sheet1 call a VLOOKUP dependent on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This

would

occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if I

get

this much sorted out, if it can be done, I can add to

the

list.

Reason for doing this is that Sheet1 is to be sorted in
several different ways for different Invoice functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions so
far, but in some cases they become rather lengthy. If
there's an easier way, please let me know.

Hope this makes sense......be easy on me

guys/gals...rank

beginner here.

Don.....dang, this got lengthy


.

hmmmmmmm.....I must be misinterpreting something
here....as I look at what I have, I have 4 columns....A
thru D, and two Rows 1 & 2. Why does the Y give me
correct answers across all four columns...Sorry, but
something is eluding me here.

I'll take a look at that site you gave Dave, thanks for
the input..

Don


-----Original Message-----
Hi Don,
You asked for the third column of the range and you only
have
two columns in the range.

More information on VLOOKUP in
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

"Don" wrote in
message ...
Thanks for the quick response Bob, but I must be doing
something wrong here. Below is what I inserted. By
adjusting the offset this calls up the various answers
in
row 1 correctly, which is the Y response. But
inserting
N in A1(sheet1) I get #NA. N is stored in A2 of the
range
on sheet2...this should reflect the answers across row
2,
as I understand this......what am I missing here??

=VLOOKUP(A1,Sheet2!A12,3)

Don

-----Original Message-----
Yes, just prefix the lookup range with the sheet name

Sheet1!A110

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Don" wrote in
message
...
Hi All,

Is it possible to pull data from another sheet using
the
VLOOKUP function? I've looked at "Indirect", but
that
seems to rely on typing in the name of another
Sheet.

What I'd like to do is to establish a range of
answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N"
inputs. I
would like to put this range on a seperate sheet,
let's
say Sheet2. Then on Sheet1 call a VLOOKUP
dependent on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This
would
occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like
this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if
I
get
this much sorted out, if it can be done, I can add
to
the
list.

Reason for doing this is that Sheet1 is to be
sorted in
several different ways for different Invoice
functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions
so
far, but in some cases they become rather lengthy.
If
there's an easier way, please let me know.

Hope this makes sense......be easy on me
guys/gals...rank
beginner here.

Don.....dang, this got lengthy


.



.

Hi Alan,

Nope, Double checked what you said, even deleted contents
and reentered N in A2....still get the #N/A answer when N
is inserted in A1 of Sheet 1.....thanks for the advice
though...as indicated above in my response to Dave, I
think I'm misinterpreting something here......

Don

-----Original Message-----
The value in A2 of Sheet2 is probably not N; perhaps N
preceded or
followed by one or more spaces?

Alan Beban

Don wrote:
Thanks for the quick response Bob, but I must be doing
something wrong here. Below is what I inserted. By
adjusting the offset this calls up the various answers
in
row 1 correctly, which is the Y response. But
inserting
N in A1(sheet1) I get #NA. N is stored in A2 of the
range
on sheet2...this should reflect the answers across row
2,
as I understand this......what am I missing here??

=VLOOKUP(A1,Sheet2!A12,3)

Don


-----Original Message-----
Yes, just prefix the lookup range with the sheet name

Sheet1!A110

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing
direct)

"Don" wrote in

message

. ..

Hi All,

Is it possible to pull data from another sheet using

the

VLOOKUP function? I've looked at "Indirect", but that
seems to rely on typing in the name of another Sheet.

What I'd like to do is to establish a range of answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N"

inputs. I

would like to put this range on a seperate sheet,
let's
say Sheet2. Then on Sheet1 call a VLOOKUP dependent
on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This

would

occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if I

get

this much sorted out, if it can be done, I can add to

the

list.

Reason for doing this is that Sheet1 is to be sorted
in
several different ways for different Invoice
functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions so
far, but in some cases they become rather lengthy. If
there's an easier way, please let me know.

Hope this makes sense......be easy on me

guys/gals...rank

beginner here.

Don.....dang, this got lengthy


.

.

Thanks all for the info. It turns out I have to have
the "N" column first in A1...then the "Y" in
A2....ordered sequence....it's working fine with this
arrangement......

Thanks again all,

Don


-----Original Message-----
The value in A2 of Sheet2 is probably not N; perhaps N
preceded or
followed by one or more spaces?

Alan Beban

Don wrote:
Thanks for the quick response Bob, but I must be doing
something wrong here. Below is what I inserted. By
adjusting the offset this calls up the various answers
in
row 1 correctly, which is the Y response. But
inserting
N in A1(sheet1) I get #NA. N is stored in A2 of the
range
on sheet2...this should reflect the answers across row
2,
as I understand this......what am I missing here??

=VLOOKUP(A1,Sheet2!A12,3)

Don


-----Original Message-----
Yes, just prefix the lookup range with the sheet name

Sheet1!A110

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing
direct)

"Don" wrote in

message

. ..

Hi All,

Is it possible to pull data from another sheet using

the

VLOOKUP function? I've looked at "Indirect", but that
seems to rely on typing in the name of another Sheet.

What I'd like to do is to establish a range of answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N"

inputs. I

would like to put this range on a seperate sheet,
let's
say Sheet2. Then on Sheet1 call a VLOOKUP dependent
on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This

would

occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if I

get

this much sorted out, if it can be done, I can add to

the

list.

Reason for doing this is that Sheet1 is to be sorted
in
several different ways for different Invoice
functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions so
far, but in some cases they become rather lengthy. If
there's an easier way, please let me know.

Hope this makes sense......be easy on me

guys/gals...rank

beginner here.

Don.....dang, this got lengthy


.

.

Sorry I got my rows and columns mixed up, I think I noticed
I was slipping yesterday when I indicated A2 when I meant B1.

To suppress N/A errors: with default False you are looking for
an exact match.

=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

wrote in message ...
hmmmmmmm.....I must be misinterpreting something
here....as I look at what I have, I have 4 columns....A
thru D, and two Rows 1 & 2. Why does the Y give me

Hi Dave,

Not sure in your case, but when I do that, I can blame it
on old age.....lol

Anyway, as I stated below, I found the answer thanks to
the link you put up. N has to come before Y in the
List. It's working fine now...I inserted an If
(A1="","",VLOOKUP.......... Works great now...

Again, thanks for the help.

Don


-----Original Message-----
Sorry I got my rows and columns mixed up, I think I
noticed
I was slipping yesterday when I indicated A2 when I
meant B1.

To suppress N/A errors: with default False you are
looking for
an exact match.

=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not
found",VLOOKUP(...,...,...,False))

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm

wrote in message
...
hmmmmmmm.....I must be misinterpreting something
here....as I look at what I have, I have 4 columns....A
thru D, and two Rows 1 & 2. Why does the Y give me



.