Copying formulas "problem" or not possible?

[jbmesser]

I run a golf league and have created an extensive workbook that calculates the players handicaps on the scorecards. The problem I'm having is trying to add additional scorecards (adding rows) with out having to re-write the formulas for each cell.


example:
In row 18 the players handicap is located in column Y and the formula below will use the value in Y and compare it to the data in row 4 for each cell to determine if the player gets a stroke on that hole (it will add a dot if the player does)


This is the formula for the first hole:


=IF(Y18-B4>=18,"••",IF(Y18>=B4,"•",""))


This is the formula for the second hole:


=IF(Y18-C4>=18,"••",IF(Y18>=C4,"•",""))


As you can see, the first reference is for the players Handicap and the second is the handicap value of the hole to determine the dot placement.


The problem that I am having is this....


If I want to add additional players, I copy the formula down to the next line and I get this:

This is the formula for the first hole:

=IF(Y19-B5>=18,"••",IF(Y19>=B5,"•",""))

This is the formula for the second hole:

=IF(Y19-C5>=18,"••",IF(Y19>=C5,"•",""))


You can see that the formula change both of the values for the "target cells" however, the Y18 cell changed to Y19 which is what I wanted, but the secondary changed from B4 and C4 to B5 and C5 and I would like this to remain in the 4th row if possible.

Am I chasing a lost cause? Do I need to suck it up and just change them all? or is there a way to make this happen?

Thanks in advance for the good or bad news...

jbmesser

 

[Marcelo Branco]

Welcome to Mr Excel

See if this does what you need
=IF($Y18-B$4>=18,"••",IF($Y18>=B$4,"•",""))
copy across and down

Hope this helps

M.

 

[Roxxien]

This is not a "problem".

It exist 4 kind of reference in Excel : absolute, referential and two mixte.

The first one, the "referential", is wrote like "B2". If your formula is in B1, then Excel won't memorize "B2" but row + 1 and column + 0. In that case, if you increment your formula, the referential won't change from row+1 and column+0 but the referenced cell is going to change.

The second one, the "absolute", is wrote like "$B$2". Again, if your formula is in B1, then Excel will only memorize "B2", whatever the way you increment your formula, the referenced cell will never change.

The two mixte version are like their name say a mixte of the two other. "$B2" will lock the column but not the row. "B$2" will lock the row but not the column.

For your problem, =IF(Y18-B4>=18,"••",IF(Y18>=B4,"•","")), you need to change it to =IF(Y18-$B$4>=18,"••",IF(Y18>=$B$4,"•",""))

In the same formula you can alternate between all kind of reference. Their exist a shortcut too change a reference from a kind to another and it's F4.

 

[jbmesser]

Marcelo,

Worked like a charm!

THANK YOU!!!!

 

[jbmesser]

Roxxien,

Thanks for the breakdown, I used Marcello's fix and then you explained it so I know why it worked!

Thanks so much!

 

[Marcelo Branco]

Marcelo,

Worked like a charm!

THANK YOU!!!!

You are welcome. Glad to help

M.

 

[Roxxien]

Roxxien,

Thanks for the breakdown, I used Marcello's fix and then you explained it so I know why it worked!

Thanks so much!

It has been my pleasure to explain that notion to you.