drag formula down making chgs on the way (Q2)

[Brad]

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

 

[Shane Devenshire]

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

 

[Brad]

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]

I have to say I'm lost! You said you wanted to increment by 2's but then you
say you want to start at 12 and increase by 16? Then you also say for odd
numbers you want to start at 4 and increase by 16? 4 is not an odd number in
my system of math. Incrementing by 2's is not the same thing as increasing
by 16?

If you enter my formula in cell A1 and B1 and copy them down you will see
that they are retreaving the values from row 2, 4, 6, .... for the even
numbers and from rows 1, 3, 5 for the odd numbers.

You can modify these formulas to start at any value you want and increment
by any number of rows you want. So study the formulas and practice with them
until you see how you need to modify them to return the results you want.
For example if you drag the formula in cell A1 down far enough it will start
returning the values from farther down column X.

 

[Brad]

Shane,

You're misunderstanding what I'm saying.
Your formula puts a 12 (data) into the first cell and then increases (that
data) by 16 as I drag it down for the even numbers.
eg. E4 = 12, E6 = 28, E8 = 44 etc. changing the data in ScorecardX4, X6, X8
doesn't change the data in E4, E6, E8. Your formula isn't pulling the data
from the Scorecard sheet, at least not for me.
It's acting the same way for the odd number except it starts with a 4.
Your formula puts a 4 into the first cell and then increases by 16 as I drag
it down for the odd numbers.
same as above,

Your formula also doesn't retrieve or acquire the initial data from
"ScorecardX4" to start with. The data that's in ScorecardW4, X4, Y4, etc. is
the "data" that gets inputed into E4. The formula need to change which cells
it's looking at in the 'Scorecard' sheet.

The actual data that is in cell 'LeaguestandingsE4' is a variable based on
the data that's in cell 'ScorecardX4'.
The data in cell 'ScorecardX4' is based on a formula that calculates the
'handicap' from golf scores from each week golfed. Each row (eg.4) is a
specific golfer and each column, columns B-S, are the individual weeks
scores. Starting with column W (first week), X (second week), etc., a
formula calculates the handicap for the golfer. I need to input the
handicap(s) on the "LeagueStandings" sheet because the LeagueStandings sheet
is what gets physically printed to paper and given to each golfer each week.

I need the formula to retreive the data from ScorecardX4 and insert it into
cell LeagueStandingsE4 for the even numbers.
When I drag that formula down I need (or would like) it to increase by
increments of 2, eg.,
LeaguestandingsE4 = formual that looks (retrieves data) at ScorecardX4
LeaguestandingsE5 = formula that looks (retrieves data) at ScorecardX6
LeaguestandingsE6 = formula that looks (retrieves data) at ScorecardX8 etc,

LeaguestandingsG4 = formula that looks (retrieves data) at ScorecardX5
LeaguestandingsG5 = formula that looks (retrieves data) at ScorecardX7
LeaguestandingsG6 = formual that looks (retrieves data) at ScorecardX9

My initial 'Spreadsheet' starts out with this data/formulas;
LeaguestandingsE4 =TRUNC(Scorecard!W4) these are default handicaps for the
first week
LeaguestandingsG4 =TRUNC(Scorecard!W5) these are default handicaps for the
first week

Starting with Week #2 I'd like to change the "W" to an "X" in the formula in
the first cell (E4 or G4) and then drag it down and have the formula KEEP
the 'letter'designation that I've changed it to, in this case an "X", AND
automatically increment the "4" or "5" by 2.

If I use my formula "=TRUNC(Scorecard!X4)" and drag it down it increases by
1, eg. when I drag it down it changes to =TRUNC(Scorecard!X5)
I don't know how to get it to increase by increments of 2 as I drag it down.

I need (would like) a similar formula for the 'odd' numbered rows too.

**Each week we need to print out the "Leaguestandings" sheet and we need to
change the data in Columns E and G each week. For ease of use I'd like to
beable to just change the 'letter'/column designation, drag the formula down
and then have it collect the correct data.

Is there another way to do this?

Right now we go to each cell E4 - E20, G4 - G20 and manually change the
'letter' designation in the formula to pull the correct data. It's time
consuming and it leads to errors (fat fingers..).
AND I'm not the one who actually does all this, I'm trying to write /
develop this spreadsheet for our church golf league and making / keeping it
simple for someone else. also, this may (could) get passed on to the next
league secretary so I need to "keep it simple".

I hope that makes more sense.

 

[Brad]

Shane,

I've got your formula's working, THANK YOU.

I have to apologize for the mix-up.
Now that I've had time to completely analyze your formula and compare it to
my spreadsheet more closely I've found the problem and my misunderstanding
as to how your formula was working.

I needed the formula to "start" in row 4 (Leaguestandings sheet) and I
needed it to "start" looking at row 4 (Scorecard sheet).
When I setup your formula in row 4 it "started" looking / pulling data from
row 8 (for the even numbers) which has the data of '12' in it and the odd
number formula started pulling data from row 7 which had the data of '4' in
it.

Coincidently the formula we use for handicaps AND the test data I had
entered to test your formulas just happened to increase the data results by
8 for every cell, by 16 every other cell (increments of 2). That's what
threw me off. My first cells, per my test data, shouldn't have been 12 and
4.

When I changed my test data, my results from using your formula should have
changed; because I only tried changing my test data in the first two rows
(rows 4 and 5) and your formula didn't start pulling data until the 7th row
I assumed the formula wasn't working at all.

I finally had a chance to test your formulas in a NEW spreadsheet without
any other formulas or calculations (handicaps) and that's when I found the
problem or misunderstanding.

I am sorry for the confusion and THANK YOU very much for your time and
effort, it's working.
Brad