Naming ranges

[Mike B]

W2K, Excel2K

I would like to name a range called Data_Cells on a
spreadsheet which has multiple pages. Is it possible to
define the range *with one definition* so that it selects
the range of cells on the worksheet I am currently on?

Eg I would name the range A2:B6 plus C810 Data_Cells
just once on (say) Sheet1, and if I was on Sheet2 it would
pick up the range A2:B6 plus C810 on Sheet2?

I thought I'd seen it done, but Help and various books I
have don't deal with it.
TIA

 

[Leo Heuser]

You can do it this way:

1. Choose Insert > Name > Define
2. In "Names in Workbook": "Data_Cells" without quotes
3. In "Reference to" (or similar) =!$A$2:$B$6,!$C$8:$D$10

The name will not show in the namebox of any sheet,
but if you enter "Data_Cells" in the box, the correct
range will be highlighted (in the active sheet)

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

 

[Mike B]

Thanks very much to Leo and Anon - I knew it could be
done, but the "how" was eluding me! Works a treat.