Excel - anchor problem

[MarcLodge]

Hi all,
I have a world cup score prediction spreadsheet with a complex formula in one cell. This formula compares the actual score of a game in two 'master cells' with the score predicted by the user in two other cells and, depending on whether they've guessed the right score, awards points.

The formula works fine and at the moment it is only in one cell (against one fixture). What I need to do is copy it down the spreadsheet to all the other 47 fixtures which are on the side of the spreadsheet. This bit is not a problem as I don't have any anchors in the formula.

The problem comes when I want to set up a new player/user along the top of the spreadsheet. I need to anchor the 'master cells' but leave the user cells floating. As the formula is reasonably complicated, I don't want to have to edit 48 formulas to add the anchors, so is there any way to do this easily or am I stuck with a long edit session?

I'm not sure if I've explained this very well, so please come back to me if necessary.

I've included the formula below for reference. The 'master cells' are C7 and E7, and the user input cells are F7 and H7:

=(IF(AND(C7=" ",E7=" "),0,IF(AND(C7=F7,E7=H7),3,IF(OR(C7=F7,E7=H7),1,IF(AND(C7>E7,F7>H7),1,IF(AND(C7=E7,F7=H7),1,IF(AND(C7<E7,F7<H7),1,0)))))))

Marc

 

[SkipVought]

Hi,

Check out Excel help on absolute with particular attention to...

Switch between relative, absolute, and mixed references
About cell and range references

Seems to me you'll want to make the "master" referenced where the column is relative and the ROW is ABSOLUTE.

BTW, Excel does not referto references as anchor -- rather absolute.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MarcLodge]

Thanks Skip, that was very helpful and I never knew you could highlight the formula and press f4 to switch between the different types.

Unfortunately, I don't think that is going to help me as I want to change the formula so that two of the cells are absolute (C7 and E7 in the example) but the other two are relative (F7 and H7). I can't see any way to change specific cells in a formula so it looks like I'll just have to edit each separate one.

Marc

 

[SkipVought]

???
[tt]
=(IF(AND($C$7=" ",$E$7=" "),0,IF(AND($C$7=F7,$E$7=H7),3,IF(OR($C$7=F7,$E$7=H7),1,IF(AND($C$7>$E$7,F7>H7),1,IF(AND($C$7=$E$7,F7=H7),1,IF(AND($C$7<$E$7,F7<H7),1,0)))))))
[/tt]
just copy down!!!

...I never knew you could highlight the formula and press f4 ...

Do NOT "highlight the formula!"

DO highlight a reference!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}