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 <[email protected]
> <mailto:[email protected]>> wrote:
>
>     On Sat, Sep 10, 2011 at 17:00, Wade Smart <[email protected]
>     <mailto:[email protected]>> wrote:
>     > On Sat, Sep 10, 2011 at 16:43, Barrie Backhurst
>     > <[email protected]
>     <mailto:[email protected]>> wrote:
>     >> On Saturday 10 Sep 2011 15:29:55 Wade Smart wrote:
>     >>> On Sat, Sep 10, 2011 at 14:15, Johnny Rosenberg
>     >> <[email protected] <mailto:[email protected]>> wrote:
>     >>> > 2011/9/10 Wade Smart <[email protected]
>     <mailto:[email protected]>>:
>     >>> >> 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
>     [email protected]
>     <mailto:[email protected]>
>     >>> > For additional commands send email to [email protected]
>     <mailto:[email protected]>
>     >>> > 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 [email protected]
>     <mailto:[email protected]>
>     >> For additional commands send email to [email protected]
>     <mailto:[email protected]>
>     >> 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 [email protected]
>     <mailto:[email protected]>
>     For additional commands send email to [email protected]
>     <mailto:[email protected]>
>     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
> [email protected] <mailto:[email protected]>

-- 
-----------------------------------------------------------------
To unsubscribe send email to [email protected]
For additional commands send email to [email protected]
with Subject: help