Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 410
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   Report Post  
Posted to microsoft.public.excel.misc
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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
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   Report Post  
Posted to microsoft.public.excel.misc
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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 03:11 PM
How do I change an Excel range of cells from relative to absolute. Jrhenk Excel Worksheet Functions 2 November 15th 04 11:55 PM


All times are GMT +1. The time now is 09:05 PM.

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

About Us

"It's about Microsoft Excel"