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...3&d=1187578589

Thank you in advance.

In Players,

Put in J4:
=INDEX(OFFSET(INDIRECT("'2007 Tourn
Reports'!A"&MATCH(TRUE,ISNUMBER(SEARCH(TEXT(H4,"mm mm"),'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,"mm mm"),'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,"mm mm"),'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,"mm mm"),'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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sean" wrote:
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...3&d=1187578589

Thank you in advance.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Thanks!

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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,"mm mm"),'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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you so much!

"Max" wrote:

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,"mm mm"),'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
Singapore
http://savefile.com/projects/236895
xdemechanik
---