Refering to other tabs easily

[Guest]

I'm not a 100% newb to Excel, I've used it for some pretty basic stuff in the
past.

I'm trying to set up a basic tournment reporting for monthly card games some
buddies and I have going on. I'd like the players tab to refer to the
tournament report tab so we only have to fill in information after each game
in the tournament tab.

Now I can go through each cell for each player and tie it to the tournament
tab but it would take for ever. I wanted to know if anyone could give me some
tips on doing this faster because copy and paste wont work since the tabs are
in a pretty complex order.

I've attached my sheet and highlighted how I've set it up to refer to the
reporting tab.
http://www.excelforum.com/attachment.php?attachmentid=13163&d=1187578589

Thank you in advance.

 

[Guest]

In Players,

Put in J4:
=INDEX(OFFSET(INDIRECT("'2007 Tourn
Reports'!A"&MATCH(TRUE,ISNUMBER(SEARCH(TEXT(H4,"mmmm"),'2007 Tourn
Reports'!$A$1:$A$215)),0)),MATCH(INDIRECT("A"&(INT((ROWS($1:1)-1)/34)+1)*34-33),'2007
Tourn Reports'!A:A,0)-1,,,8),MATCH(I4,OFFSET(INDIRECT("'2007 Tourn
Reports'!A"&MATCH(TRUE,ISNUMBER(SEARCH(TEXT(H4,"mmmm"),'2007 Tourn
Reports'!$A$1:$A$215)),0)),1,,,8),0))

Put in K4:
=INDEX(OFFSET(INDIRECT("'2007 Tourn
Reports'!B"&MATCH(TRUE,ISNUMBER(SEARCH(TEXT(H4,"mmmm"),'2007 Tourn
Reports'!$A$1:$A$215)),0)),MATCH(INDIRECT("A"&(INT((ROWS($1:1)-1)/34)+1)*34-33),'2007
Tourn Reports'!A:A,0)-1,,,8),MATCH(I4,OFFSET(INDIRECT("'2007 Tourn
Reports'!A"&MATCH(TRUE,ISNUMBER(SEARCH(TEXT(H4,"mmmm"),'2007 Tourn
Reports'!$A$1:$A$215)),0)),1,,,8),0))

Select J4:K4, copy down to K25. This does it for the first name. Copy the
range J4:K25, then just right-click & paste into each of the other name's top
left cell, ie paste it on J38, then on J72, etc.

 

[Guest]

Forgot to mention that the 2 formulas in J4 and K4 need to be array-entered,
ie press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing
ENTER. Ensure that the array-entering is done correctly; you should see Excel
insert curly braces { } around the formula in the formula bar.

---

 

[Guest]

Max, can you email it to me or host it some where? My email is baker2gs at
hotmail.com

I can't get the formula to paste correctly because of the line breaks in the
post...

Thanks.

 

[Guest]

Also do you have a formula for the dues column too? That's the M column.

Thanks!

 

[Guest]

Also do you have a formula for the dues column too? That's the M column.

In Players,

To get the dues (col M), put in M4, array-enter (as before with CSE):
=INDEX(OFFSET(INDIRECT("'2007 Tourn
Reports'!A"&MATCH(TRUE,ISNUMBER(SEARCH(TEXT(H4,"mmmm"),'2007 Tourn
Reports'!$A$1:$A$215)),0)),MATCH(INDIRECT("A"&(INT((ROWS($1:1)-1)/34)+1)*34-33),'2007 Tourn Reports'!A:A,0)-1,,,8),2)

Copy down to M25. Then copy M4:M25 and paste correspondingly into M38, M72,
etc for the rest of the names.

Here's an implemented sample with the formulas for cols J, K & M
pasted for a couple of names:

http://www.savefile.com/files/987258
Multi Criteria Lookup.xls

---

 

[Guest]

Thank you so much!

In Players,

To get the dues (col M), put in M4, array-enter (as before with CSE):
=INDEX(OFFSET(INDIRECT("'2007 Tourn
Reports'!A"&MATCH(TRUE,ISNUMBER(SEARCH(TEXT(H4,"mmmm"),'2007 Tourn
Reports'!$A$1:$A$215)),0)),MATCH(INDIRECT("A"&(INT((ROWS($1:1)-1)/34)+1)*34-33),'2007 Tourn Reports'!A:A,0)-1,,,8),2)

Copy down to M25. Then copy M4:M25 and paste correspondingly into M38, M72,
etc for the rest of the names.

Here's an implemented sample with the formulas for cols J, K & M
pasted for a couple of names:

http://www.savefile.com/files/987258
Multi Criteria Lookup.xls

---

 

[Max]

welcome, Sean.

 

[Guest]

Max,

I'm trying to add second set of games for the month of November, when I add
new lines in the players tab it gives me an error and then it turns the rest
of the players stats into errors. Can you please help?

Here's a link to the workbook for reference:

http://www.mediafire.com/?1jn2tv1ndw9

Thanks!

 

[Max]

I'm trying to add second set of games for the month of November

That's not possible. You cannot have 2 "November" tables in that sheet. Each
of the 12 monthly tables must be unique as MATCH can only return the 1st
matching instance.

For your intents, I'd suggest that you simply use* another copy of the
earlier file that worked to record the 2nd set of games. And if you have a
3rd set of games, use yet another copy of the file.
*File > save as ..

 

[Guest]

Is it possible to use the month name "November2" so this way it is unique?

 

[Max]

Is it possible to use the month name "November2" so this way it is unique?

Not in the context of your set-up, sorry.

---