Defining a name for more than one range

[joylee]

Dear,
No problem for defining a name for a single data range. However, I have situation to make a frequency table using data in two separate ranges, for example B2:C5 and D2:E6. Is there a way to define these two ranges as one name so I can use '=frequency(the name, bin range)?' Thank you, joylee

 

[Rick Rothstein]

Select the two ranges (select the first then hold down the CTRL key while selecting the second) and then type the Defined Name for this discontinuous range in the Name Box (located to the left of the Formula Bar). I haven't tested it (I'll let you do that), so I am not sure how well the FREQUENCY function will handle discontinuous ranges though.

 

[joylee]

Thank you very much. It works perfectly when I have two ranges in the same sheet. However, if they are in different sheets, I cannot define a name with the 'control' key. I have tried to define by typing in the range addresses in the name manager box. For example, the name 'AA' by '=sheet1!$A$1, sheet2!$A$1'
EXCEL does not give me any error for defining this way but when I calculate with AA, for example, =sum(AA), gives me #VALUE ! error.
Your further help will be greatly appreciated. JYLee

 

[My Aswer Is This]

Why got:
Give the Range in Sheet(1) the Name "Apple"

Give the Range in Sheet(2) the Name "Pie"

Then use the formula:

=Sum(Apple)+Sum(Pie)​

 

[Rick Rothstein]

Then use the formula:

=Sum(Apple)+Sum(Pie)

With those defined names, you could also write that formula this way...

=SUM(Apple,Pie)

 

[Rick Rothstein]

I have tried to define by typing in the range addresses in the name manager box. For example, the name 'AA' by '=sheet1!$A$1, sheet2!$A$1'

I am hardly an expert on Defined Names so others who are will hopefully chime in, but I could not find a way to create a disjointed Defined Name that is usable. The closest I could come to what you tried above was this..

=Sheet1:Sheet2!$A$1

However, note that this encompasses those two sheets and all other sheets physically located between them. So, if you inserted a sheet between Sheet1 and Sheet2 and put a value in A1 of that sheet, using SUM on that Defined Name would sum cell A1 on Sheet1, Sheet2 and that inserted sheet. As I said, I could not find any way to define two (and I presume or more) disjointed ranges located on different sheets that did not involve the same cell or cells within a range of sheets.

 

[joylee]

Well thank you. But my original task is more than simple addition using =sum(). For example, I want to have a frequency table using two separate ranges of data in different sheets.
JYLee

 

[joylee]

Thank you. I have data on different sheets. The only thing I can do now may be, as you kindly found out, that I have to rearrange all my data to be the same address in different sheets.
JYLee