First Players of the Day?

[MarvinP]

Hi,

I have a Sign-Up sheet for Tennis Players for a single day. There are lots of courts to assign them to and times they want to play. I need to pull up the first time on each court (by the court name) and list the 4 players assigned to play at that time. I'm sure this is some Offset Match combination. The first players of the day might be found using
Match("*",Column,0). I'd like all the calculations based off the Court Name. See the attached.

I find some days are "dumb days" for me and nothing works. Other days nothing fails. I'm on a dumb day...

Also - these guys are stuck in 2003 Excel so no Countifs or IfError ... Sorry.

[Fotis1991]

Hi Marvin.

Just an idea..

[MarvinP]

Hi Fotis,

There are 31 sheets in this workbook and I'd hate to put Array formulas into the mix. It might slow things down. Your answer works but is there a non CSE, array answer?

I like the Time formula with the Offset(Indirect(Address(,Match)))). That shows you are a true pro at building these. I'm much better at Pivots than Offsets.

[twiggywales]

try my idea.

here you go

:D (no array formulas (I did label the table for simplicity)

MHCC Tennis Sheet to Display (2).xlsx

also no offset (no volatile functions here)

[twiggywales]

also if you check Fotis formula it will work without being arrayed (I'm not sure why he did) I hadn't actually checked his answer but he basically covered it all for you (slightly different to mine but not greatly)

[MarvinP]

Hey TwiggyWales,

We are so close. I added some names to Clay 3 at 8 am and changed the court in your formula area. It is so close but not there yet... See attached...

[Fotis1991]

I just opened to see if you found some solution, Marvin. I saw twig's opinion about my formula. I am too tired(so maybe i am wrong) but as twig's said my formula works as not ARRAY and works for Clay 3, too...

[twiggywales]

the formula was still referencing the clay 1 from the list above check now


MHCC Tennis Sheet to Display almost.xlsx

[MarvinP]

Hey,

Thanks for the help. See what I finally ended up with. The half trick was to use named ranges. Twiggywales put me on to that. The final part of making it easier was to make a TopBox which is one off the Courts so the index comes out correct. See my final answer... (If you care..)

[twiggywales]

glad to have helped. looks good btw

[Fotis1991]

... See my final answer... (If you care..)

Of course i care. Always is a very good exercise, issues like this.

Glad that you found your solution and thanks for the reb*.