If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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 |
#2
|
|||
|
|||
"VLOOKUP??"
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 |
#3
|
|||
|
|||
"VLOOKUP??"
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 . |
#4
|
|||
|
|||
"VLOOKUP??"
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 . |
#5
|
|||
|
|||
"VLOOKUP??"
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 . |
#6
|
|||
|
|||
"VLOOKUP??"
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 . . |
#7
|
|||
|
|||
"VLOOKUP??"
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 . . |
#8
|
|||
|
|||
"VLOOKUP??""Found the Answer"
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 . . |
#9
|
|||
|
|||
"VLOOKUP??"
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 |
#10
|
|||
|
|||
"VLOOKUP??"
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 . |
|
Thread Tools | |
Display Modes | |
|
|