Re: vlookup question

On Saturday 10 Sep 2011 21:30:13 Wade Smart 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

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 [email protected]
For additional commands send email to [email protected]
with Subject: help