Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
drag formula down making chgs on the way (Q2)
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
drag formula down making chgs on the way (Q2)
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
drag formula down making chgs on the way (Q2)
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
drag formula down making chgs on the way (Q2)
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Brad" wrote: 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
drag formula down making chgs on the way (Q2)
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. "Shane Devenshire" wrote in message ... 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Brad" wrote: 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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
drag formula down making chgs on the way (Q2)
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 "Brad" wrote in message ... 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. "Shane Devenshire" wrote in message ... 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Brad" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drag formula across making chgs on the way | New Users to Excel | |||
can a tab name/# chg. when a cell name/# chgs. automatical Excel | Excel Worksheet Functions | |||
Can I prevent format chgs? | Excel Discussion (Misc queries) | |||
How do i create a Wrksht that auto. chgs to nxt invoice # when ope | Excel Discussion (Misc queries) | |||
Can I have a running total which begins again at 0 when dte chgs? | Excel Worksheet Functions |