change change cell reference to Absolute reference

A

art

How can I change a whole list of cell reffernce to an absolute reference. For
E.G. I have A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 C1 C2 C3 C4 C5. How can I Change
the whole list at once to: $A$1 $A$2... and so on?
 
R

RagDyeR

What exactly do you mean by "list"?

The cell references changing, or remaining constant are pertinent when they
are within a formula.
You can change *all* cell references in a formula with a *single* click of
<F4> if you select the entire formula in the formula bar.

If you're not talking about cell references in a formula, would you care to
elaborate a little in your explanation?


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


How can I change a whole list of cell reffernce to an absolute reference.
For
E.G. I have A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 C1 C2 C3 C4 C5. How can I Change
the whole list at once to: $A$1 $A$2... and so on?
 
A

art

I'm not talking about 1 cell. I'm talking about a whole list of formulas. For
E.G. in cell B1 is =A1. In Cell B2 is =A2. In cell B3 is =A3. In cell B4 is
=C1. In cell B5 is =C2... and so on with different references.
 
R

RagDyeR

Say you want to copy B1:B100 to another location.

The trick is to "UNformulate" those formulas, so that they become text
strings, and then copy the text to your other location, and then revert them
back to working formulas.

Change the equal sign to something unique, like ^^^, and after the copy and
paste, change it back to "=".

Select B1 to B100.
From the Menu Bar:
<Edit> <Replace>,

In the "Find What" box, enter
=

In the "Replace With" box, enter
^^^

Then, <Replace All>
You now have plain text.
Right click in the selection and choose "Copy".

Navigate to your new location and right click in the top cell and choose
"Paste".

Now, reverse the process:

From the Menu Bar:
<Edit> <Replace>,

In the "Find What" box, enter
^^^

In the "Replace With" box, enter
=

Then, <Replace All>
You now have working formulas with the original cell references.

Do the same reversal to your original B1:B100 if necessary.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I'm not talking about 1 cell. I'm talking about a whole list of formulas.
For
E.G. in cell B1 is =A1. In Cell B2 is =A2. In cell B3 is =A3. In cell B4 is
=C1. In cell B5 is =C2... and so on with different references.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top