[users] Re: vlookup question
On Sat, Sep 10, 2011 at 9:30 PM, Wade Smart wadesm...@gmail.com wrote: On Sat, Sep 10, 2011 at 17:00, Wade Smart wadesm...@gmail.com wrote: On Sat, Sep 10, 2011 at 16:43, Barrie Backhurst barrie.backhu...@ntlworld.com wrote: On Saturday 10 Sep 2011 15:29:55 Wade Smart wrote: On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. I don't follow your spreadsheet layout, but appending the style function should do what you want. -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Thats cool. But, I dont think that will work. Column #1 Mark #2 Brad #4 Tim #5 Kim #6 Jennifer #8 Ryan #9 John - Capt #10 Steve In the above, the captain is in the 7th row but the team to the right the captain might be in row 1 or 10. Capt is not written in the cell (space requirements) so I was going to add a row to the right of each team but that messes with the vlookup. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 Tried this: =STYLE(Bold) + VLOOKUP(G10,$Teams.A2:B8,2,0) from the OO examples and I just get errors. This doesnt help with finding my capt issue but as for formatting, its a problem. Wade -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Is there a particular reason why you want to have the individual team info repeating horizontally across the data sheet? If not, you could set up columns: Team name, Team number, Player type (cap. or player), Jersey number, etc. (whatever other info you need). And enter all the player information in that data sheet (in those 5 or 6 columns). Then you should be able to sort and/or search your data sheet any way you like, and output the results on your roster tab. You could also designate, which team is Home/Away on the roster tab, so that the formulae put them in the appropriate locations or to apply your conditional formatting to each team roster. Just a shot in the dark. M -- Martin du Saire Biological Sciences Technician USDA-ARS 470 Borlaug Hall 1991 Upper Buford Circle St. Paul, Minnesota 55108 612-625-9747 mdusa...@umn.edu -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
Why are you sending these e mails to me? Richard On 9/12/2011 9:00 AM, Martin du Saire wrote: On Sat, Sep 10, 2011 at 9:30 PM, Wade Smart wadesm...@gmail.com mailto:wadesm...@gmail.com wrote: On Sat, Sep 10, 2011 at 17:00, Wade Smart wadesm...@gmail.com mailto:wadesm...@gmail.com wrote: On Sat, Sep 10, 2011 at 16:43, Barrie Backhurst barrie.backhu...@ntlworld.com mailto:barrie.backhu...@ntlworld.com wrote: On Saturday 10 Sep 2011 15:29:55 Wade Smart wrote: On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com mailto:gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com mailto:wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org mailto:users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org mailto:sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. I don't follow your spreadsheet layout, but appending the style function should do what you want. -- - To unsubscribe send email to users-unsubscr...@openoffice.org mailto:users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org mailto:sy...@openoffice.org with Subject: help Thats cool. But, I dont think that will work. Column #1 Mark #2 Brad #4 Tim #5 Kim #6 Jennifer #8 Ryan #9 John - Capt #10 Steve In the above, the captain is in the 7th row but the team to the right the captain might be in row 1 or 10. Capt is not written in the cell (space requirements) so I was going to add a row to the right of each team but that messes with the vlookup. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 Tried this: =STYLE(Bold) + VLOOKUP(G10,$Teams.A2:B8,2,0) from the OO examples and I just get errors. This doesnt help with finding my capt issue but as for formatting, its a problem. Wade -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org mailto:users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org mailto:sy...@openoffice.org with Subject: help Is there a particular reason why you want to have the individual team info repeating horizontally across the data sheet? If not, you could set up columns: Team name, Team number, Player type (cap. or player), Jersey number, etc. (whatever other info you need). And enter all the player information in that data sheet (in those 5 or 6 columns). Then you should be able to sort and/or search your data sheet any way you like, and output the results on your roster tab. You could also designate, which team is Home/Away on the roster tab, so that the formulae put them in the appropriate locations or to apply your conditional formatting to each team roster. Just a shot in the dark. M -- Martin du Saire Biological Sciences Technician USDA-ARS 470 Borlaug Hall 1991 Upper Buford Circle St. Paul, Minnesota 55108 612-625-9747 mdusa...@umn.edu mailto:mdusa...@umn.edu -- - To unsubscribe send email to
[users] Re: vlookup question
2011/9/12 Richard Saunders richa...@well.com: Why are you sending these e mails to me? Richard On 9/12/2011 9:00 AM, Martin du Saire wrote: On Sat, Sep 10, 2011 at 9:30 PM, Wade Smart wadesm...@gmail.com wrote: On Sat, Sep 10, 2011 at 17:00, Wade Smart wadesm...@gmail.com wrote: On Sat, Sep 10, 2011 at 16:43, Barrie Backhurst barrie.backhu...@ntlworld.com wrote: On Saturday 10 Sep 2011 15:29:55 Wade Smart wrote: On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. I don't follow your spreadsheet layout, but appending the style function should do what you want. -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Thats cool. But, I dont think that will work. Column #1 Mark #2 Brad #4 Tim #5 Kim #6 Jennifer #8 Ryan #9 John - Capt #10 Steve In the above, the captain is in the 7th row but the team to the right the captain might be in row 1 or 10. Capt is not written in the cell (space requirements) so I was going to add a row to the right of each team but that messes with the vlookup. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 Tried this: =STYLE(Bold) + VLOOKUP(G10,$Teams.A2:B8,2,0) from the OO examples and I just get errors. This doesnt help with finding my capt issue but as for formatting, its a problem. Wade -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Is there a particular reason why you want to have the individual team info repeating horizontally across the data sheet? If not, you could set up columns: Team name, Team number, Player type (cap. or player), Jersey number, etc. (whatever other info you need). And enter all the player information in that data sheet (in those 5 or 6 columns). Then you should be able to sort and/or search your data sheet any way you like, and output the results on your roster tab. You could also designate, which team is Home/Away on the roster tab, so that the formulae put them in the appropriate locations or to apply your conditional formatting to each team roster. Just a shot in the dark. M -- Martin du Saire Biological Sciences Technician USDA-ARS 470 Borlaug Hall 1991 Upper Buford Circle St. Paul, Minnesota 55108 612-625-9747 mdusa...@umn.edu Richard, if you look at the email that they are coming from - users@openoffice.org, its because you are signed up to a usergroup. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
2011/9/10 Wade Smart wadesm...@gmail.com: On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. Okay, then I guess I don't understand your question. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
On Saturday 10 Sep 2011 21:30:13 Wade Smart wrote: On Sat, Sep 10, 2011 at 17:00, Wade Smart wadesm...@gmail.com wrote: On Sat, Sep 10, 2011 at 16:43, Barrie Backhurst barrie.backhu...@ntlworld.com wrote: On Saturday 10 Sep 2011 15:29:55 Wade Smart wrote: On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. I don't follow your spreadsheet layout, but appending the style function should do what you want. -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Thats cool. But, I dont think that will work. Column #1 Mark #2 Brad #4 Tim #5 Kim #6 Jennifer #8 Ryan #9 John - Capt #10 Steve In the above, the captain is in the 7th row but the team to the right the captain might be in row 1 or 10. Capt is not written in the cell (space requirements) so I was going to add a row to the right of each team but that messes with the vlookup. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 Tried this: =STYLE(Bold) + VLOOKUP(G10,$Teams.A2:B8,2,0) from the OO examples and I just get errors. This doesnt help with finding my capt issue but as for formatting, its a problem. Wade If I understand your teams layout correctly, it is col A =player number col B =player details for team1 col C =captain marker for team1 col D =player details for team2 col E =captain marker for team2 continuing accross the sheet? That being the case and using the layout you have suggested, the following formula should work =VLOOKUP($E18,$Sheet2.$A$12:$N$26, (2*$H$10),0)T(STYLE(IF(ISBLANK(INDIRECT(ADDRESS(ROW()-6,2*$H$10+1, , ,sheet2))),Default,BOLD))) though I think you will need to expand the search array for the captain columns? Barrie -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
On Sat, Sep 10, 2011 at 1:35 PM, Wade Smart wadesm...@gmail.com wrote: I have two tabs: tab1 is my game form and tab 2 is my player roster. When I put in the team name on tab1 it populates the players from tab 2. This year the league mandated rosters have sequential numbering of players on the game forms. The captains are in bold on tab 2. When they appear on the game form though, that formatting does not carry over. How can I carry over that bold formatting from tab 2 to tab 1 with vlookup? Have you tried conditional formatting? If you have a column that expresses whether the given row is a captain or not (value of 1 or 0) then you could use conditional formatting to make the copied values be bold or not based on that column. More info on conditional formatting here: http://openoffice.blogs.com/openoffice/2007/01/conditional_for.html -Rob Wade -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
On Sat, Sep 10, 2011 at 12:58, Rob Weir r...@robweir.com wrote: On Sat, Sep 10, 2011 at 1:35 PM, Wade Smart wadesm...@gmail.com wrote: I have two tabs: tab1 is my game form and tab 2 is my player roster. When I put in the team name on tab1 it populates the players from tab 2. This year the league mandated rosters have sequential numbering of players on the game forms. The captains are in bold on tab 2. When they appear on the game form though, that formatting does not carry over. How can I carry over that bold formatting from tab 2 to tab 1 with vlookup? Have you tried conditional formatting? If you have a column that expresses whether the given row is a captain or not (value of 1 or 0) then you could use conditional formatting to make the copied values be bold or not based on that column. More info on conditional formatting here: http://openoffice.blogs.com/openoffice/2007/01/conditional_for.html -Rob Wade -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
On Saturday 10 Sep 2011 15:29:55 Wade Smart wrote: On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. I don't follow your spreadsheet layout, but appending the style function should do what you want. -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
On Sat, Sep 10, 2011 at 16:43, Barrie Backhurst barrie.backhu...@ntlworld.com wrote: On Saturday 10 Sep 2011 15:29:55 Wade Smart wrote: On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. I don't follow your spreadsheet layout, but appending the style function should do what you want. -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Thats cool. But, I dont think that will work. Column #1 Mark #2 Brad #4 Tim #5 Kim #6 Jennifer #8 Ryan #9 John - Capt #10 Steve In the above, the captain is in the 7th row but the team to the right the captain might be in row 1 or 10. Capt is not written in the cell (space requirements) so I was going to add a row to the right of each team but that messes with the vlookup. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
[users] Re: vlookup question
On Sat, Sep 10, 2011 at 17:00, Wade Smart wadesm...@gmail.com wrote: On Sat, Sep 10, 2011 at 16:43, Barrie Backhurst barrie.backhu...@ntlworld.com wrote: On Saturday 10 Sep 2011 15:29:55 Wade Smart wrote: On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg gurus.knu...@gmail.com wrote: 2011/9/10 Wade Smart wadesm...@gmail.com: Im trying to figure out how to make that work. on Tab 2 = teams. ColumnA Row 12 - 14 = number of players ColumnB Row 12 - 14 = Player Jersey Number Player name ColumnC I put a 1 next to the captain Comumn D - the next team Ok. On Tab 1 = Game form. H10 = I type in the Home team (ex: 1) K10 = I type in the Away team (ex: 4) K18 is my vlookup. =VLOOKUP($E18,$Teams.$A$12:$N$26,($H$10+1),0) So it looks at which team Ive put down and then gets that roster of players. So, from ColumnC above - if I insert that column, when I type in say 2 on my game form, its only going to pull a 1 because that is the captain column. Im not sure how to get around that yet. And where is your conditional formatting? You did explore [Format → Conditional formatting], didn't you? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Well, I didnt get to the conditional formatting because of the vlookup layout problem that I posted above. I don't follow your spreadsheet layout, but appending the style function should do what you want. -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help Thats cool. But, I dont think that will work. Column #1 Mark #2 Brad #4 Tim #5 Kim #6 Jennifer #8 Ryan #9 John - Capt #10 Steve In the above, the captain is in the 7th row but the team to the right the captain might be in row 1 or 10. Capt is not written in the cell (space requirements) so I was going to add a row to the right of each team but that messes with the vlookup. -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 Tried this: =STYLE(Bold) + VLOOKUP(G10,$Teams.A2:B8,2,0) from the OO examples and I just get errors. This doesnt help with finding my capt issue but as for formatting, its a problem. Wade -- -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- - To unsubscribe send email to users-unsubscr...@openoffice.org For additional commands send email to sy...@openoffice.org with Subject: help
