Re: vlookup question

On Sat, Sep 10, 2011 at 9:30 PM, Wade Smart <[email protected]> wrote:

> On Sat, Sep 10, 2011 at 17:00, Wade Smart <[email protected]> wrote:
> > On Sat, Sep 10, 2011 at 16:43, Barrie Backhurst
> > <[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]> wrote:
> >>> > 2011/9/10 Wade Smart <[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]
> >>> > For additional commands send email to [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]
> >> For additional commands send email to [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]
> For additional commands send email to [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]
-- 
-----------------------------------------------------------------
To unsubscribe send email to [email protected]
For additional commands send email to [email protected]
with Subject: help