Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue ( not working and im tearing my hair out)
Okay , well i cant discuss the actual info in my sheet but ill come up with a good analogy of it all . Ok basically on sheet 2 i have all the teams in my NHL ( my company) listed under the immediate manager. I also have the players last name, first name and number set out as such ; Team A Shivers Paul 14 Jacobsen John 25 Tucker Tanya 98 Team B Pitt Brad 22 Murray Bill 28 Clinton Hillary 55 You get the point, ok , on sheet 1 ( my main page accessible to my company ) , i have this format... Managers last name Players last name Player first name Players last name Players number When you type in the managers name and players last name the other three should auto populate. So first thing i did was set up a formula to allow the page to search for players first name as this; =IF(managers name=team a,VLOOKUP(players last name,list of players,2,FALSE),D1) this states that whatever name is put in the managers name it will search the list of that team for the last name and populate the first name. this is set up for each team, oh and by the way d1 is just set to popoulate the field as NONE. I have changed the cell names to better explain this. That formual works great , no issues there. I have this setup on page 2 at the end of all the teams. Second thing was extreamly easy players last name on page 1, just had it equal the type in field above it. Players number wont be hard i can do a Vlookup for it as well to search the team page similar to how i searched for the palyers first name. Now here in lies my issue, after all my teams are set up on page 2 , with all the corresponding =if lines to calculate the players first name are set up i end up with a list generally looking like this ( when searching for let say manager team b and Murray) None Bill None None and so on ......my issue is , and im sure its just somthing simple im not doing, but how do i do a lookup in that colum for any field not equaling the word none. I have tried everything, and have even went through the help database. If i have to alter my setup im ok with that, just please help . THANK YOU IN ADVANCE Jason -- me+excel=crazy ------------------------------------------------------------------------ me+excel=crazy's Profile: http://www.excelforum.com/member.php...o&userid=28868 View this thread: http://www.excelforum.com/showthread...hreadid=486165 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue ( not working and im tearing my hair out)
Given what you are trying to do, I would use a helper column for the lookup, being after the manager and player names, insert a combined column =A1&B1 and look up where (say) the manager name is in G1, and the player is in H1, and sheet2 has Column A=team, B=player surname, C=A1&B1 formula copied down), and D-E-F-etc contain player details, as =VLookup(G1&H1,Sheet2!C1:Sheet2!E99,2,False) Hope this helps me+excel=crazy Wrote: Okay , well i cant discuss the actual info in my sheet but ill come up with a good analogy of it all . Ok basically on sheet 2 i have all the teams in my NHL ( my company) listed under the immediate manager. I also have the players last name, first name and number set out as such ; Team A Shivers Paul 14 Jacobsen John 25 Tucker Tanya 98 Team B Pitt Brad 22 Murray Bill 28 Clinton Hillary 55 You get the point, ok , on sheet 1 ( my main page accessible to my company ) , i have this format... Managers last name Players last name Player first name Players last name Players number When you type in the managers name and players last name the other three should auto populate. So first thing i did was set up a formula to allow the page to search for players first name as this; =IF(managers name=team a,VLOOKUP(players last name,list of players,2,FALSE),D1) this states that whatever name is put in the managers name it will search the list of that team for the last name and populate the first name. this is set up for each team, oh and by the way d1 is just set to popoulate the field as NONE. I have changed the cell names to better explain this. That formual works great , no issues there. I have this setup on page 2 at the end of all the teams. Second thing was extreamly easy players last name on page 1, just had it equal the type in field above it. Players number wont be hard i can do a Vlookup for it as well to search the team page similar to how i searched for the palyers first name. Now here in lies my issue, after all my teams are set up on page 2 , with all the corresponding =if lines to calculate the players first name are set up i end up with a list generally looking like this ( when searching for let say manager team b and Murray) None Bill None None and so on ......my issue is , and im sure its just somthing simple im not doing, but how do i do a lookup in that colum for any field not equaling the word none. I have tried everything, and have even went through the help database. If i have to alter my setup im ok with that, just please help . THANK YOU IN ADVANCE Jason -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=486165 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue ( not working and im tearing my hair out)
I like this example
contextures.on.ca/excelfiles.html ScenarioAlt.xls it uses INDEX/MATCH FORMULA, when you look through the list in the web page you will find many other ideas |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue ( not working and im tearing my hair out)
I actually solved it anopther way staying with my current setup and formulas , basically for anyone that has this issue , what i did was, on the column of names that shows up : a 1 NONE 2 NONE 3 NONE 4 BILL 5 NONE 6 NONE I made a if string that said if the first is NONE go to the next column if its not none display the cell, it was a really long string but hey it works. Was sorta like this. =if (a1=none,if(a2=none,if(a3=none,NONE,a3),a2),a1) It works great.. -- me+excel=crazy ------------------------------------------------------------------------ me+excel=crazy's Profile: http://www.excelforum.com/member.php...o&userid=28868 View this thread: http://www.excelforum.com/showthread...hreadid=486165 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue ( not working and im tearing my hair out)
The formula as shown doesn't work, the 'none' needs to be in quotes, however the test and use of A3 remains unneccassary The formula works equally for =IF(A1="none",IF(A2="none",A3,A2),A1) me+excel=crazy Wrote: I actually solved it anopther way staying with my current setup and formulas , basically for anyone that has this issue , what i did was, on the column of names that shows up : a 1 NONE 2 NONE 3 NONE 4 BILL 5 NONE 6 NONE I made a if string that said if the first is NONE go to the next column if its not none display the cell, it was a really long string but hey it works. Was sorta like this. =if (a1=none,if(a2=none,if(a3=none,NONE,a3),a2),a1) It works great.. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=486165 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue ( not working and im tearing my hair out)
what happens if more than cell doesn't equal "none"
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue ( not working and im tearing my hair out)
The data is taken from the first encountered cell within the range that does not equal 'none' More to the point is that, based on the original data, and then on the second shown data, 'Bill' will appear as a player for the first team. The first data showed a blank row between teams, in which case the formula will show the desired results. The second data shows no detectable break between teams and the formula does not test for same-team-person. The OP's explanation is a little vague when said that "-made a if string that said if the first is NONE go to the next column-" but appears to mean the next row. Those points could just be in the presented test data, and although not a method that would be recommended the item may work for the OP's requirement. damorrison Wrote: what happens if more than cell doesn't equal "none" -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=486165 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup not working in 2000 - worked in 97! | Excel Discussion (Misc queries) | |||
Vlookup Calculation Issue | Excel Worksheet Functions | |||
Vlookup datatype(?) issue | Excel Worksheet Functions | |||
Pulling hair out with VLOOKUP | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |