conditional sum - copyng formula
 

I have conditionla sum formula in one cell which I wish to :
a) copy across to another sheet in same workboo
b) copy into other cells on same sheet but incremant the values in the
formula, I wsih to change complete to new, butwhen I copy to another cell I
and change the word I get error, probably because I cannot change the
destination cell.

Cheers

=SUM(IF($C$2:$C$701="Capability",IF($H$2:$H$701="C omplete",IF($I$2:$I$701="Administrator",$B$2:$B$70 1,0),0),0))

conditional sum - copyng formula
 

If you paste the formula into the cell then you have the permission to
changge the cell. Editing formulas can cause problems. first, never use the
backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete character
one at a time. Then enter new characters. Then click on worksheet to end the
editing of the formula.

"offcott" wrote:

I have conditionla sum formula in one cell which I wish to :
a) copy across to another sheet in same workboo
b) copy into other cells on same sheet but incremant the values in the
formula, I wsih to change complete to new, butwhen I copy to another cell I
and change the word I get error, probably because I cannot change the
destination cell.

Cheers

=SUM(IF($C$2:$C$701="Capability",IF($H$2:$H$701="C omplete",IF($I$2:$I$701="Administrator",$B$2:$B$70 1,0),0),0))

conditional sum - copyng formula
 

What version of XL are you using Joel?

changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula)

The Backspace works just fine for me in XL 97. You are not thinking about
the arrow keys in Conditional Formatting where they do enter new cell
references are you? Whereas:

backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.

In the fx dialog box clicking on the worksheet *will* enter the cell
reference for that cell and does not end the editing of the formula.

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
If you paste the formula into the cell then you have the permission to
changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.

"offcott" wrote:

I have conditionla sum formula in one cell which I wish to :
a) copy across to another sheet in same workboo
b) copy into other cells on same sheet but incremant the values in the
formula, I wsih to change complete to new, butwhen I copy to another cell
I
and change the word I get error, probably because I cannot change the
destination cell.

Cheers

=SUM(IF($C$2:$C$701="Capability",IF($H$2:$H$701="C omplete",IF($I$2:$I$701="Administrator",$B$2:$B$70 1,0),0),0))

conditional sum - copyng formula
 

Excel 2003 . I habve the View - toolbars - standard to get the F(x) box.

"Sandy Mann" wrote:

What version of XL are you using Joel?

changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula)

The Backspace works just fine for me in XL 97. You are not thinking about
the arrow keys in Conditional Formatting where they do enter new cell
references are you? Whereas:

backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.

In the fx dialog box clicking on the worksheet *will* enter the cell
reference for that cell and does not end the editing of the formula.

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
If you paste the formula into the cell then you have the permission to
changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.

"offcott" wrote:

I have conditionla sum formula in one cell which I wish to :
a) copy across to another sheet in same workboo
b) copy into other cells on same sheet but incremant the values in the
formula, I wsih to change complete to new, butwhen I copy to another cell
I
and change the word I get error, probably because I cannot change the
destination cell.

Cheers

=SUM(IF($C$2:$C$701="Capability",IF($H$2:$H$701="C omplete",IF($I$2:$I$701="Administrator",$B$2:$B$70 1,0),0),0))

conditional sum - copyng formula
 

Sorry I am using Excel2002, Managed to copy and edit, but what is the { used
for that I have to put in at the beginnig of the formula, after I use thisit
works, but why?

Eric

"Sandy Mann" wrote:

What version of XL are you using Joel?

changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula)

The Backspace works just fine for me in XL 97. You are not thinking about
the arrow keys in Conditional Formatting where they do enter new cell
references are you? Whereas:

backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.

In the fx dialog box clicking on the worksheet *will* enter the cell
reference for that cell and does not end the editing of the formula.

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
If you paste the formula into the cell then you have the permission to
changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.

"offcott" wrote:

I have conditionla sum formula in one cell which I wish to :
a) copy across to another sheet in same workboo
b) copy into other cells on same sheet but incremant the values in the
formula, I wsih to change complete to new, butwhen I copy to another cell
I
and change the word I get error, probably because I cannot change the
destination cell.

Cheers

=SUM(IF($C$2:$C$701="Capability",IF($H$2:$H$701="C omplete",IF($I$2:$I$701="Administrator",$B$2:$B$70 1,0),0),0))

conditional sum - copyng formula
 

The bracket specifies an array. Usually I use Sumproduct which doesn't
require the bracket in 2003.

"offcott" wrote:

Sorry I am using Excel2002, Managed to copy and edit, but what is the { used
for that I have to put in at the beginnig of the formula, after I use thisit
works, but why?

Eric

"Sandy Mann" wrote:

What version of XL are you using Joel?

changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula)

The Backspace works just fine for me in XL 97. You are not thinking about
the arrow keys in Conditional Formatting where they do enter new cell
references are you? Whereas:

backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.

In the fx dialog box clicking on the worksheet *will* enter the cell
reference for that cell and does not end the editing of the formula.

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
If you paste the formula into the cell then you have the permission to
changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.

"offcott" wrote:

I have conditionla sum formula in one cell which I wish to :
a) copy across to another sheet in same workboo
b) copy into other cells on same sheet but incremant the values in the
formula, I wsih to change complete to new, butwhen I copy to another cell
I
and change the word I get error, probably because I cannot change the
destination cell.

Cheers

=SUM(IF($C$2:$C$701="Capability",IF($H$2:$H$701="C omplete",IF($I$2:$I$701="Administrator",$B$2:$B$70 1,0),0),0))

conditional sum - copyng formula
 

Sorry if I have caused confusion Eric, I was asking Joel what version of XL
he was using because his instructions do not work for me in XL97.

The { } around your formula,which should be put there by XL when you enter
the formula by pressing and holding the Ctrl and Shift keys whil you press
Enter tell Xl that it is an array formula and to work on each cell in the
ranges in the formula otherwise it will just use the first cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"offcott" wrote in message
...
Sorry I am using Excel2002, Managed to copy and edit, but what is the {
used
for that I have to put in at the beginnig of the formula, after I use
thisit
works, but why?

Eric

"Sandy Mann" wrote:

What version of XL are you using Joel?

changge the cell. Editing formulas can cause problems. first, never
use
the
backspace key (it changes the address in the formula)

The Backspace works just fine for me in XL 97. You are not thinking
about
the arrow keys in Conditional Formatting where they do enter new cell
references are you? Whereas:

backspace key (it changes the address in the formula). Click on the
cell
then do the editing in the F(x) box at the top of the worksheet. Click
on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to
end
the
editing of the formula.

In the fx dialog box clicking on the worksheet *will* enter the cell
reference for that cell and does not end the editing of the formula.

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
If you paste the formula into the cell then you have the permission to
changge the cell. Editing formulas can cause problems. first, never
use
the
backspace key (it changes the address in the formula). Click on the
cell
then do the editing in the F(x) box at the top of the worksheet. Click
on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to
end
the
editing of the formula.

"offcott" wrote:

I have conditionla sum formula in one cell which I wish to :
a) copy across to another sheet in same workboo
b) copy into other cells on same sheet but incremant the values in the
formula, I wsih to change complete to new, butwhen I copy to another
cell
I
and change the word I get error, probably because I cannot change the
destination cell.

Cheers

=SUM(IF($C$2:$C$701="Capability",IF($H$2:$H$701="C omplete",IF($I$2:$I$701="Administrator",$B$2:$B$70 1,0),0),0))

conditional sum - copyng formula
 

Sandy

When working in the formula is: dialog in CF, hit F2 key then you can use arrow
keys.

Same for the refers to: dialog in InsertNameDefine.


Gord

On Fri, 20 Jun 2008 14:48:49 +0100, "Sandy Mann"
wrote:

You are not thinking about
the arrow keys in Conditional Formatting where they do enter new cell
references are you?

conditional sum - copyng formula
 

Thank you for that Gord - that has been a bugbear to me for ages.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sandy

When working in the formula is: dialog in CF, hit F2 key then you can use
arrow
keys.

Same for the refers to: dialog in InsertNameDefine.


Gord

On Fri, 20 Jun 2008 14:48:49 +0100, "Sandy Mann"

wrote:

You are not thinking about
the arrow keys in Conditional Formatting where they do enter new cell
references are you?