Range of cells: Convert relative reference into absolute

Hello, everyone,

Is there a way to change simultaneously a whole range of cells that contain
the same relative reference into absolute references.

For example:

Convert the following relative references in the range of cells D56:AB56

=Sheet1!B1 (...) =Sheet1!Z1

Into:

= Sheet1!$B1 (...) Sheet1!$Z$1

But: All at the same time, not one by one.

I have to transpose a large amount of tables that have relative references
and I need to change these into absolute references. It would take me forever
to do this one by one.

Thanks for the help!

--

igor

On Sep 29, 2:21*pm, Igor > wrote:
> Hello, everyone,
>
> Is there a way to change simultaneously a whole range of cells that contain
> the same relative reference into absolute references.
>
> For example:
>
> Convert the following relative references in the range of cells D56:AB56
>
> =Sheet1!B1 (...) =Sheet1!Z1
>
> Into:
>
> = Sheet1!$B1 (...) Sheet1!$Z$1
>
> But: All at the same time, not one by one.
>
> I have to transpose a large amount of tables that have relative references
> and I need to change these into absolute references. It would take me forever
> to do this one by one.
>
> Thanks for the help!
>
> --
>
> igor

Find and replace
Ctrl + F Find Sheet1!B1 and replace with Sheet1!$B1
Jay

Thanks for the reply, Jay,

Find and Replace will not work here because only one of the cells has column
B as reference, all the others reference different columns. There are no 2
cells that reference the same column.

Thanks anyway.

--

igor


"jlclyde" wrote:

> On Sep 29, 2:21 pm, Igor > wrote:
> > Hello, everyone,
> >
> > Is there a way to change simultaneously a whole range of cells that contain
> > the same relative reference into absolute references.
> >
> > For example:
> >
> > Convert the following relative references in the range of cells D56:AB56
> >
> > =Sheet1!B1 (...) =Sheet1!Z1
> >
> > Into:
> >
> > = Sheet1!$B1 (...) Sheet1!$Z$1
> >
> > But: All at the same time, not one by one.
> >
> > I have to transpose a large amount of tables that have relative references
> > and I need to change these into absolute references. It would take me forever
> > to do this one by one.
> >
> > Thanks for the help!
> >
> > --
> >
> > igor
>
> Find and replace
> Ctrl + F Find Sheet1!B1 and replace with Sheet1!$B1
> Jay
>

Then why not Find "!" and replace with "!$"
That takes care of the general problem.
Then follow up and change
"!$Z" with "!$Z$"

Maybe that will work?

"Igor" wrote:

> Thanks for the reply, Jay,
>
> Find and Replace will not work here because only one of the cells has column
> B as reference, all the others reference different columns. There are no 2
> cells that reference the same column.
>
> Thanks anyway.
>
> --
>
> igor
>
>
> "jlclyde" wrote:
>
> > On Sep 29, 2:21 pm, Igor > wrote:
> > > Hello, everyone,
> > >
> > > Is there a way to change simultaneously a whole range of cells that contain
> > > the same relative reference into absolute references.
> > >
> > > For example:
> > >
> > > Convert the following relative references in the range of cells D56:AB56
> > >
> > > =Sheet1!B1 (...) =Sheet1!Z1
> > >
> > > Into:
> > >
> > > = Sheet1!$B1 (...) Sheet1!$Z$1
> > >
> > > But: All at the same time, not one by one.
> > >
> > > I have to transpose a large amount of tables that have relative references
> > > and I need to change these into absolute references. It would take me forever
> > > to do this one by one.
> > >
> > > Thanks for the help!
> > >
> > > --
> > >
> > > igor
> >
> > Find and replace
> > Ctrl + F Find Sheet1!B1 and replace with Sheet1!$B1
> > Jay
> >

Yes, that would indeed solve the problem. Such an easy answer and it totally
slipped by!

Thank you, J!

--

igor


"JLatham" wrote:

> Then why not Find "!" and replace with "!$"
> That takes care of the general problem.
> Then follow up and change
> "!$Z" with "!$Z$"
>
> Maybe that will work?
>
> "Igor" wrote:
>
> > Thanks for the reply, Jay,
> >
> > Find and Replace will not work here because only one of the cells has column
> > B as reference, all the others reference different columns. There are no 2
> > cells that reference the same column.
> >
> > Thanks anyway.
> >
> > --
> >
> > igor
> >
> >
> > "jlclyde" wrote:
> >
> > > On Sep 29, 2:21 pm, Igor > wrote:
> > > > Hello, everyone,
> > > >
> > > > Is there a way to change simultaneously a whole range of cells that contain
> > > > the same relative reference into absolute references.
> > > >
> > > > For example:
> > > >
> > > > Convert the following relative references in the range of cells D56:AB56
> > > >
> > > > =Sheet1!B1 (...) =Sheet1!Z1
> > > >
> > > > Into:
> > > >
> > > > = Sheet1!$B1 (...) Sheet1!$Z$1
> > > >
> > > > But: All at the same time, not one by one.
> > > >
> > > > I have to transpose a large amount of tables that have relative references
> > > > and I need to change these into absolute references. It would take me forever
> > > > to do this one by one.
> > > >
> > > > Thanks for the help!
> > > >
> > > > --
> > > >
> > > > igor
> > >
> > > Find and replace
> > > Ctrl + F Find Sheet1!B1 and replace with Sheet1!$B1
> > > Jay
> > >

Sometimes we can't see the trees for the forest, sometimes we don't even
realize we're in a forest because we focus to much on one tree.

Not sure which category this falls into -- and I'd hate to try to count the
times I've missed the "easy" answers in the past.

Thanks for the feedback, much appreciated, and glad I could help.

"Igor" wrote:

>
> Yes, that would indeed solve the problem. Such an easy answer and it totally
> slipped by!
>
> Thank you, J!
>
> --
>
> igor
>
>
> "JLatham" wrote:
>
> > Then why not Find "!" and replace with "!$"
> > That takes care of the general problem.
> > Then follow up and change
> > "!$Z" with "!$Z$"
> >
> > Maybe that will work?
> >
> > "Igor" wrote:
> >
> > > Thanks for the reply, Jay,
> > >
> > > Find and Replace will not work here because only one of the cells has column
> > > B as reference, all the others reference different columns. There are no 2
> > > cells that reference the same column.
> > >
> > > Thanks anyway.
> > >
> > > --
> > >
> > > igor
> > >
> > >
> > > "jlclyde" wrote:
> > >
> > > > On Sep 29, 2:21 pm, Igor > wrote:
> > > > > Hello, everyone,
> > > > >
> > > > > Is there a way to change simultaneously a whole range of cells that contain
> > > > > the same relative reference into absolute references.
> > > > >
> > > > > For example:
> > > > >
> > > > > Convert the following relative references in the range of cells D56:AB56
> > > > >
> > > > > =Sheet1!B1 (...) =Sheet1!Z1
> > > > >
> > > > > Into:
> > > > >
> > > > > = Sheet1!$B1 (...) Sheet1!$Z$1
> > > > >
> > > > > But: All at the same time, not one by one.
> > > > >
> > > > > I have to transpose a large amount of tables that have relative references
> > > > > and I need to change these into absolute references. It would take me forever
> > > > > to do this one by one.
> > > > >
> > > > > Thanks for the help!
> > > > >
> > > > > --
> > > > >
> > > > > igor
> > > >
> > > > Find and replace
> > > > Ctrl + F Find Sheet1!B1 and replace with Sheet1!$B1
> > > > Jay
> > > >