One named range for the same range on 2 or more noncontiguous worksheets

[agarwaldvk]

Hi Everybody

Can anyone suggest how to create a range name (say for the range A1:D100) on four different worksheets say (W1, W3, W5 and W7). Please note these worksheets are not arranged sequentially one after the other and hence obviously I cannot the colon (:) range operator. There is no guarantee that these WILL always be arranged in a sequential order - it cannot be assumed to be so.

I am not inclined to use different range names one for each worksheet since I will be having a few of them and hence the formula will become very unwieldy.

Something like so is obviously wrong but in the same vein perhaps may work if someone can lead me to it :-

TestRange = W1!A1:D100,W3!A1:D100,W5!A1:D100,W7!A1:D100


Best regards and thanks


Deepak Agarwal

[mikerickson]

Define name: myRange refersTo: !$A$1:$D$100 (The key is the leading !)

myRange will refer to A1 : D100 on whatever sheet it is put. For example
put =MAX(myRange) in Sheet1!A200 and it will return the maximum value of Sheet1!A1 : D100

=MAX(myRange) in Sheet2!A200 will return the max of Sheet2!A1 : D100.

If you need to use these names across sheets, you might look at INDIRECT.

(Darn smilies messing up ranges A1:D100)

[agarwaldvk]

Dear Mike

Thanks for your response.

I probably confused the question.

The formula would be in a totally different worksheet - other than W1, W3, W5 and W7 - say in worksheet called 'Summary'.

I want to sum or conditionally sum (using sumproduct) the range say A1:A100 on workhseets W1, W3, W5 and W7.

I hope this clarifies the question, does it?


Best regards


Deepak

[mikerickson]

Either a named range for each sheet, or INDIRECT would be the direction I went.