A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Range of cells: Convert relative reference into absolute



 
 
Thread Tools Display Modes
  #1  
Old September 29th 08, 08:21 PM posted to microsoft.public.excel.misc
Igor
external usenet poster
 
Posts: 20
Default 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
Ads
  #2  
Old September 29th 08, 08:29 PM posted to microsoft.public.excel.misc
jlclyde
external usenet poster
 
Posts: 406
Default Range of cells: Convert relative reference into absolute

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
  #3  
Old September 29th 08, 09:56 PM posted to microsoft.public.excel.misc
Igor
external usenet poster
 
Posts: 20
Default Range of cells: Convert relative reference into absolute

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
>

  #4  
Old September 29th 08, 10:03 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 3,366
Default Range of cells: Convert relative reference into absolute

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
> >

  #5  
Old September 29th 08, 11:13 PM posted to microsoft.public.excel.misc
Igor
external usenet poster
 
Posts: 20
Default Range of cells: Convert relative reference into absolute


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
> > >

  #6  
Old September 30th 08, 01:16 AM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 3,366
Default Range of cells: Convert relative reference into absolute

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
> > > >

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Relative to absolute address Yorke Excel Discussion (Misc queries) 6 October 25th 07 07:47 PM
Changing Cells from Relative to Absolute Reference PZ Excel Discussion (Misc queries) 16 April 11th 07 08:22 PM
How do I get relative/absolute reference button (macros) SPBaku Excel Discussion (Misc queries) 1 May 27th 05 02:18 PM
changing multiple cells from relative to absolute reference Mike Excel Discussion (Misc queries) 4 March 10th 05 02:11 PM
How do I change an Excel range of cells from relative to absolute. Jrhenk Excel Worksheet Functions 2 November 15th 04 10:55 PM


All times are GMT +1. The time now is 10:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.