range problem too

Posted by Larry on July 10, 2001 7:55 PM

I am also having problems naming the range. When I click on the cells and try to define the range name, a message pops up that says "Your formula contains an invalid external reference to a worksheet." My worksheet's name is "ISP (2)". For some reason, it is putting the worksheet address in between the cell addresses. (='ISP (2)'!$D$5,'ISP (2)'!$D$6,'ISP (2)'!$D$7,'ISP (2)'!$D$11,'ISP (2)'!$D$12,etc.)

I'm still learning this and any help would be great!

Thanks in advance!

Posted by Aladin Akyurek on July 10, 2001 9:09 PM

It looks like ISP (2) is a copy of ISP. Making a copy of an existing sheet that already contains named ranges can be tricky.

In order to name a range selected that range, go to the Name Box, and type a name. The same result can be achieved via Insert|Name|Define.

There is no need to name non-contiguous ranges in order to do a conditional count in your case. Just use ordinary range references.

Aladin

Posted by Larry on July 11, 2001 5:07 AM

How do I use ordinary range references in non-congruent cells?

Larry

Posted by Aladin Akyurek on July 11, 2001 5:34 AM

Larry,

Here are some examples:

(1) =SUM(A1,B1:E1, G1:G3)

(2) =COUNT(C1:C10, E1:E10)

(3) =MAX(Sheet1!A1:A5;Sheet2!A1:A5)

etc. Note that you can name all these ranges via the Name Box and use those names in the above formulas in the stead of the ordinary ranges that figure as arguments of the functions. Mastering both ways is an important step in learning spreadsheets.

Hope this helps.

Aladin