Block of formulas

G

gary

How can I assign a "label" to a range of cells?

(I'd like to refer to the "label" rather than to the
individual locations of the cells in the range).
 
M

Myrna Larson

Select the cells. Then type the range name in the box at the left end of the
formula bar (the one that normally displays the cell reference, like A1), OR
go to the Insert Menu, select Name, then Define, and type the name in the top.
The appropriate reference should already be displayed in the bottom box.
 
M

mzehr

Hi Gary,
Simply highlight the range, then select
insert/name/define/ and name the range.

Or an even easier way is to highlight the range, then go
to the name box and type in the name you want to give it.
HTH
 
G

gary

OK. I've defined a "Name" to a range of cells. Now, using
EDIT/REPLACE, I've entered a particular value in the "Find
What" box and I want to enter the above "Name" in the
"Replace With" box. What is the proper syntax for the
"Name" in "Replace With" box?
 
G

Gord Dibben

gary

Labels are not the same as named ranges. Which is it you want to use?

If named ranges, you have a couple of answers.

If Labels...........

Select row 1 assuming it has titles. Insert>Name>Label

Your row 1 range will be in the "add label range" dialog box. Select "Column
Labels" option button and click "Add" and OK.

Select Column A and do same except option button "Row Labels"

Now see Help on how to use "label ranges" and address intersections.

Note you must have Tools>Options>Calculation>Workbook Options "accept labels
in formulas" enabled.

Gord Dibben Excel MVP
 
P

Peter

-----Original Message-----
OK. I've defined a "Name" to a range of cells. Now, using
EDIT/REPLACE, I've entered a particular value in the "Find
What" box and I want to enter the above "Name" in the
"Replace With" box. What is the proper syntax for the
"Name" in "Replace With" box?
Click to expand...
Gary

You can use the name to select the named range. Say the
range is called Description. In Description are several
items called Widget1 that are to bere named Piston.

Click on the reference box (at the left of the formula
bar) and select the range Description.

In the replace What textbox type widget1 andin the Replace
With textbox type Piston and click on Replace or Replace
all.

Hope this is what you want.

Peter Atherton
 
G

gary

I want to assign a "name" to range F1..G6. The contents of
these cells are:

Columns
Row F G

1 r Cat
2 Dog
3 Rat
4 Pig
5 Ferret
6 Mouse

I want to search column C. For every cell whose contents =
'x', I want to replace 'x' with the above "range name". As
a result, if C1 = 'x', then:
C1 would be changed to 'r'
D1 = Dog
D2 = Cat
D3 = Rat
D4 = Pig
D5 = Ferret
D6 = Mouse

Gary

P.S: Excel2000 does have a "Tools" menu-item but its
"Options" selection doesn't provide a "Workbook Options"
tab so I can't enable "Accept labels in formulas".
 
G

gary

I wish someone would step me through this process.

My workbook has about 30,000 rows with over 1,600 values in
colunn C. (In my workbook, the range of formulas varies
between 15 columns by 2 rows to 15 columns by 98 rows).
The values in the cells in column C determines the size of
the range of formulas.

While I'm waiting for the way to use named ranges (or
whatever the correct term is), I've started MANUALLY doing
1,600 EDIT/FIND/REPLACE actions. This means that I have go
through the entire worksheet over-and-over again for each
value in the cells in column C. But this will take forever.

Gary
 
Top