Shane,

That's not working, it doesn't appear to be looking at sheet4
('Scorecard'X,Y)

I can change the information in 'Scorecard X4 5, 6, etc and the information
on Sh2 doesn't change;
The column with the 'even' numbers starts with 12 and increases by 16 as you
go down the column.
The column with the 'odd' numbers starts with 4 and increases by 16 as you
go down the column.

The letter "X" designation works ok for dragging down (always has) but
trying to get it to look at 'Scorecard'X4 and then increase by two, X6, X8,
etc as it's dragged down is the issue.

I need the formula to start with 'Scorecard'X4 and go from there in the
'even' column (golfer #1)
eg. Sheet2 e4 needs to show the hcp from Sheet4 x4 (ScorecardX4)
I need the formula to start with 'Scorecard'X5 and go from there in the
'odd' column (golfer #2)
eg. Sheet2 e5 needs to show the hcp from Sheet4 x5 (ScorecardX5)

Sheet 4 Column W = week 1
....row 4 = team 1 player 1
....row 5 = team 1 player 2
....row 6 = team 2 player 1
....row 7 = team 2 player 2
etc.
Sheet 4 Column X = week 2
etc.

Sheet 2 is the League Standings / Handicap "printed" sheet
Team / Player information is listed by row across
eg. Team# Player1Name P1Phone Player1Hcp TeamPoints Player2Hcp
Player2Name P2Phone
1 Bob 123-4567 8 10
6 Bill 234-7890

Player1Hcp column pulls from ScorecardWx(even number starting with 4)
Player2Hcp column pulls form ScorecardWy(odd number starting with 5)

I hope that helps.
Brad


"Shane Devenshire" wrote in
message ...
Hi,

For the even number use

=INDIRECT("Scorecard!X"&2*(ROW()-1)+2)

for the odd number rows use

=INDIRECT("Scorecard!X"&2*(ROW()-1)+1)

add TRUNC as needed
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Brad" wrote:

I have a spreadsheet setup for a golf league with multiple sheets, sheet1
is
for input.
Sheet3 & 4 have formulas pulling the information from Sh1. Sh2 pulls
information from Sh3 and Sh4.
Sh1=input, Sh2=league standings(printout), Sh3=team wkly score,
Sh4=individual score & handicaps

Question for Sh2 and problem I have is;
team1 player1's hcp(e4) =TRUNC(Scorecard!W4)
team2 player1's hcp(e5) =TRUNC(Scorecard!W6)

and then,
team1 player 2's hcp(g4) =Trunc(Scorecard!W5)
team2 player 2's hcp(g5) =Trunc(Scorecard!W7)

I would like to beable to drag down this formula "after" changing the
Letter(Column) to X and have the Letter(column) designation change to X
"and" keep the Number(row) designation increasing by 2.

If I change the "W" to an "X" and drag the formula down I'd like it to
look
like this for player#1 for each team;
e4 =Trunc(Scorecard!X4)
e5 =Trunc(Scorecard!X6)
e6 =Trunc(Scorecard!X8)
e7 =Trunc(Scorecard!X10)
etc. for 10 teams, 20 players

for #2 player for each team
g4 =Trunc(Scorecard!X5)
g5 =Trunc(Scorecard!X7)
g6 =Trunc(Scorecard!X8)
etc. for 10 teams, 20 players

I have two columns to do this to, team1 player1(e) and team1 player2(g).
I would like to make this change each week for approx. 20 weeks.

Each week we printout a League Standings sheet with everyones current
handicap, team score and placement. The "W" references the handicap for
the
individual player based on their weekly scores. Each week there's a new
formula with the additional weeks score added in, so the standings sheet
needs to reference a different cell(column) each week.

I hope that makes sense,
Brad