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 - maybe?!
Hi,
I want to make use of a vlookup (i think thats what i need!) but not sure how to get it working: I have a drop down list - when user selects something from within list, the cell next to it would be populated with information relating to this from a lookup sheet i am using. This bit is simple, i have created the vlookup so once something is selected it looks up in the sheet where that info is stored and it returns back what is required in the cells next to it. But the problem is, the vlookup appears to be limited to returning one cell only. If i have info displayed as: a b c d So if 'a' is selected in the drop down list then it should display b,c,d in (which are all displayed in seperate cells in the lookup sheet), the lookup only returns the fisrt cell - which is 'b'. how can i get it to return the other items too? maybe something more comlex that a vlookup? please help. thanks. |
#2
|
|||
|
|||
Vlookup - maybe?!
=IF(ROWS(A$2:A2)=COUNTIF(Sheet1!B$2:B$126,A$1),IN DEX(Sheet1!A$2:A$126,SMALL(IF(Sheet1!B$2:B$126=A$1 ,ROW(Sheet1!A$2:A$126)-MIN(ROW(Sheet1!A$2:A$126))+1),ROWS(A$2:A2))),"")
A2 is where your drop down is. Sheet1 column B is where you have items that you are selecting in drop down. Sheet1 column A is where you have items that you want to return. This is an array formula and needs CTRL+SHIFT+ENTER not just ENTER. Copy down as far as needed. Hope this helps. "Zak" wrote in message ... Hi, I want to make use of a vlookup (i think thats what i need!) but not sure how to get it working: I have a drop down list - when user selects something from within list, the cell next to it would be populated with information relating to this from a lookup sheet i am using. This bit is simple, i have created the vlookup so once something is selected it looks up in the sheet where that info is stored and it returns back what is required in the cells next to it. But the problem is, the vlookup appears to be limited to returning one cell only. If i have info displayed as: a b c d So if 'a' is selected in the drop down list then it should display b,c,d in (which are all displayed in seperate cells in the lookup sheet), the lookup only returns the fisrt cell - which is 'b'. how can i get it to return the other items too? maybe something more comlex that a vlookup? please help. thanks. |
#3
|
|||
|
|||
Vlookup - maybe?!
Correction
A2 is where you are entering the formula. A1 is where your dropdown is. Sheet1 column B is where you have items that you are selecting in drop down. Sheet1 column A is where you have items that you want to return. "Gaurav" wrote in message ... =IF(ROWS(A$2:A2)=COUNTIF(Sheet1!B$2:B$126,A$1),IN DEX(Sheet1!A$2:A$126,SMALL(IF(Sheet1!B$2:B$126=A$1 ,ROW(Sheet1!A$2:A$126)-MIN(ROW(Sheet1!A$2:A$126))+1),ROWS(A$2:A2))),"") A2 is where your drop down is. Sheet1 column B is where you have items that you are selecting in drop down. Sheet1 column A is where you have items that you want to return. This is an array formula and needs CTRL+SHIFT+ENTER not just ENTER. Copy down as far as needed. Hope this helps. "Zak" wrote in message ... Hi, I want to make use of a vlookup (i think thats what i need!) but not sure how to get it working: I have a drop down list - when user selects something from within list, the cell next to it would be populated with information relating to this from a lookup sheet i am using. This bit is simple, i have created the vlookup so once something is selected it looks up in the sheet where that info is stored and it returns back what is required in the cells next to it. But the problem is, the vlookup appears to be limited to returning one cell only. If i have info displayed as: a b c d So if 'a' is selected in the drop down list then it should display b,c,d in (which are all displayed in seperate cells in the lookup sheet), the lookup only returns the fisrt cell - which is 'b'. how can i get it to return the other items too? maybe something more comlex that a vlookup? please help. thanks. |
#4
|
|||
|
|||
Vlookup - maybe?!
Sorry, i am a little confused.
Which sheet does the drop down list go into? i tried a few ways but i get no result. Also, where do i put the formula into? I have changed all the A's to B and the B's to A as it suits my needs better that way - i.e. i show the drop down list items first and then the results to be returned for them, like this: column A column B a 1 2 3 b 4 5 etc etc. sorry, a little new to this! thanks again. "Gaurav" wrote: =IF(ROWS(A$2:A2)=COUNTIF(Sheet1!B$2:B$126,A$1),IN DEX(Sheet1!A$2:A$126,SMALL(IF(Sheet1!B$2:B$126=A$1 ,ROW(Sheet1!A$2:A$126)-MIN(ROW(Sheet1!A$2:A$126))+1),ROWS(A$2:A2))),"") A2 is where your drop down is. Sheet1 column B is where you have items that you are selecting in drop down. Sheet1 column A is where you have items that you want to return. This is an array formula and needs CTRL+SHIFT+ENTER not just ENTER. Copy down as far as needed. Hope this helps. "Zak" wrote in message ... Hi, I want to make use of a vlookup (i think thats what i need!) but not sure how to get it working: I have a drop down list - when user selects something from within list, the cell next to it would be populated with information relating to this from a lookup sheet i am using. This bit is simple, i have created the vlookup so once something is selected it looks up in the sheet where that info is stored and it returns back what is required in the cells next to it. But the problem is, the vlookup appears to be limited to returning one cell only. If i have info displayed as: a b c d So if 'a' is selected in the drop down list then it should display b,c,d in (which are all displayed in seperate cells in the lookup sheet), the lookup only returns the fisrt cell - which is 'b'. how can i get it to return the other items too? maybe something more comlex that a vlookup? please help. thanks. |
#5
|
|||
|
|||
Vlookup - maybe?!
Okay. in Sheet1 you have something like this.
a 1 b 4 a 3 a 2 b 5 b 6 In Sheet2, column A you have a dropdown in A2. In B2 you will enter the following formula. =IF(ROWS(B$2:B2)=COUNTIF(Sheet1!A$2:A$50,A$2),IND EX(Sheet1!B$2:B$50,SMALL(IF(Sheet1!A$2:A$50=A$2,RO W(Sheet1!B$2:B$50)-MIN(ROW(Sheet1!B$2:B$50))+1),ROWS(B$2:B2))),"") CTRL+SHIFT+ENTER "Zak" wrote in message news Sorry, i am a little confused. Which sheet does the drop down list go into? i tried a few ways but i get no result. Also, where do i put the formula into? I have changed all the A's to B and the B's to A as it suits my needs better that way - i.e. i show the drop down list items first and then the results to be returned for them, like this: column A column B a 1 2 3 b 4 5 etc etc. sorry, a little new to this! thanks again. "Gaurav" wrote: =IF(ROWS(A$2:A2)=COUNTIF(Sheet1!B$2:B$126,A$1),IN DEX(Sheet1!A$2:A$126,SMALL(IF(Sheet1!B$2:B$126=A$1 ,ROW(Sheet1!A$2:A$126)-MIN(ROW(Sheet1!A$2:A$126))+1),ROWS(A$2:A2))),"") A2 is where your drop down is. Sheet1 column B is where you have items that you are selecting in drop down. Sheet1 column A is where you have items that you want to return. This is an array formula and needs CTRL+SHIFT+ENTER not just ENTER. Copy down as far as needed. Hope this helps. "Zak" wrote in message ... Hi, I want to make use of a vlookup (i think thats what i need!) but not sure how to get it working: I have a drop down list - when user selects something from within list, the cell next to it would be populated with information relating to this from a lookup sheet i am using. This bit is simple, i have created the vlookup so once something is selected it looks up in the sheet where that info is stored and it returns back what is required in the cells next to it. But the problem is, the vlookup appears to be limited to returning one cell only. If i have info displayed as: a b c d So if 'a' is selected in the drop down list then it should display b,c,d in (which are all displayed in seperate cells in the lookup sheet), the lookup only returns the fisrt cell - which is 'b'. how can i get it to return the other items too? maybe something more comlex that a vlookup? please help. thanks. |
#6
|
|||
|
|||
Vlookup - maybe?!
Hi,
You may check my article at the following link http://office.microsoft.com/en-us/ex...260381033.aspx -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Zak" wrote in message ... Hi, I want to make use of a vlookup (i think thats what i need!) but not sure how to get it working: I have a drop down list - when user selects something from within list, the cell next to it would be populated with information relating to this from a lookup sheet i am using. This bit is simple, i have created the vlookup so once something is selected it looks up in the sheet where that info is stored and it returns back what is required in the cells next to it. But the problem is, the vlookup appears to be limited to returning one cell only. If i have info displayed as: a b c d So if 'a' is selected in the drop down list then it should display b,c,d in (which are all displayed in seperate cells in the lookup sheet), the lookup only returns the fisrt cell - which is 'b'. how can i get it to return the other items too? maybe something more comlex that a vlookup? please help. thanks. |
#7
|
|||
|
|||
Vlookup - maybe?!
Hi,
Sorry for keep having to bother you but ive tried exactly what you said and nothing is working. I dont suppose you could create a dummy excel sheet for me and attach here? If im asking for too much then you dont have to. Also the data is not as you stated: a 1 b 4 a 3 a 2 b 5 b 6 But it appears like i said earlier: CA CB a 1 2 3 b 4 5 So, one item in column A will have multiple corresponding values in column B and i want all of them to show when the formula is in action. Ashish, i went to the link you sent me but it just says how you do what i want rather than give me an example, being new to this i really am finding it hard to understand. thanks again. "Gaurav" wrote: Okay. in Sheet1 you have something like this. a 1 b 4 a 3 a 2 b 5 b 6 In Sheet2, column A you have a dropdown in A2. In B2 you will enter the following formula. =IF(ROWS(B$2:B2)=COUNTIF(Sheet1!A$2:A$50,A$2),IND EX(Sheet1!B$2:B$50,SMALL(IF(Sheet1!A$2:A$50=A$2,RO W(Sheet1!B$2:B$50)-MIN(ROW(Sheet1!B$2:B$50))+1),ROWS(B$2:B2))),"") CTRL+SHIFT+ENTER "Zak" wrote in message news Sorry, i am a little confused. Which sheet does the drop down list go into? i tried a few ways but i get no result. Also, where do i put the formula into? I have changed all the A's to B and the B's to A as it suits my needs better that way - i.e. i show the drop down list items first and then the results to be returned for them, like this: column A column B a 1 2 3 b 4 5 etc etc. sorry, a little new to this! thanks again. "Gaurav" wrote: =IF(ROWS(A$2:A2)=COUNTIF(Sheet1!B$2:B$126,A$1),IN DEX(Sheet1!A$2:A$126,SMALL(IF(Sheet1!B$2:B$126=A$1 ,ROW(Sheet1!A$2:A$126)-MIN(ROW(Sheet1!A$2:A$126))+1),ROWS(A$2:A2))),"") A2 is where your drop down is. Sheet1 column B is where you have items that you are selecting in drop down. Sheet1 column A is where you have items that you want to return. This is an array formula and needs CTRL+SHIFT+ENTER not just ENTER. Copy down as far as needed. Hope this helps. "Zak" wrote in message ... Hi, I want to make use of a vlookup (i think thats what i need!) but not sure how to get it working: I have a drop down list - when user selects something from within list, the cell next to it would be populated with information relating to this from a lookup sheet i am using. This bit is simple, i have created the vlookup so once something is selected it looks up in the sheet where that info is stored and it returns back what is required in the cells next to it. But the problem is, the vlookup appears to be limited to returning one cell only. If i have info displayed as: a b c d So if 'a' is selected in the drop down list then it should display b,c,d in (which are all displayed in seperate cells in the lookup sheet), the lookup only returns the fisrt cell - which is 'b'. how can i get it to return the other items too? maybe something more comlex that a vlookup? please help. thanks. |
#8
|
|||
|
|||
Vlookup - maybe?!
Hi,
I have tried a simpler way to bring across values, instead of using the formula below i have just used a simple vlookup. I have put the multiple values to be returned into one cell rather than a few different ones, which just complicated things. I have another problem now - the vlookup returns the values required fine but i require this cell to be editable so the user can delete or add to what was returned. But when clicking into the cell i just see the formula, no obvoius way to edit the contents of the cell - is there a way i can edit the contents of a cell in which a vlookup formula has been written? awaiting your response. many thanks. "Ashish Mathur" wrote: Hi, You may check my article at the following link http://office.microsoft.com/en-us/ex...260381033.aspx -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Zak" wrote in message ... Hi, I want to make use of a vlookup (i think thats what i need!) but not sure how to get it working: I have a drop down list - when user selects something from within list, the cell next to it would be populated with information relating to this from a lookup sheet i am using. This bit is simple, i have created the vlookup so once something is selected it looks up in the sheet where that info is stored and it returns back what is required in the cells next to it. But the problem is, the vlookup appears to be limited to returning one cell only. If i have info displayed as: a b c d So if 'a' is selected in the drop down list then it should display b,c,d in (which are all displayed in seperate cells in the lookup sheet), the lookup only returns the fisrt cell - which is 'b'. how can i get it to return the other items too? maybe something more comlex that a vlookup? please help. thanks. |
Thread Tools | |
Display Modes | |
|
|