drag formula across making chgs on the way


B

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 #1 for Sh4 and problem I have is this;
Week1(b4) =('Weekly Input'!C3)
Week2 (c4)=('Weekly Input'!H3)
Week3 (d4)=('Weekly Input'!M3)
As you can see my formula pulls from cells that advance or change by 5 each
week.
How can I drag the cell formulas across the row and have the formula change
(advance) "just" the letter(column) designation (by 5)correctly? If at all
possible.

Question #2 for Sh2 and problem I have is;
team1 play1's hcp(e4) =TRUNC(Scorecard!W4)
team2 play1's hcp(e5) =TRUNC(Scorecard!W6)
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
e4 =Trunc(Scorecard!X4)
e5 =Trunc(Scorecard!X6)
e6 =Trunc(Scorecard!X8)
e7 =Trunc(Scorecard!X10)
etc.
I have two columns to do this to, team1 player1(e) and team1 player2(g).

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
 
Ad

Advertisements

M

Max

For your Q1, here's one way ..

In Sh4,
In B4: =OFFSET('Weekly Input'!$C$3,,COLUMNS($A:A)*5-5)
Copy B4 across to D4. And that will do it.

Success? Celebrate it, click the YES button below

P/s: You should stick to only 1Q per thread. Make it focused and attractive
for responders, increase your chances of getting responses ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
 
B

Brad

Max,

Thank You very much for that response and answer;

It works great, it will save setup / changes time drastically when I have at
least 20 weeks to change.

Brad
 
Ad

Advertisements

B

Brad

Max,

could you look at my second question I re-submitted seperately(sp).

Shane, who has been trying to help me, is misunderstanding what I want.

I hope I have restated it more accurately.

Brad
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top