Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Relative to absolute address | Excel Discussion (Misc queries) | |||
Changing Cells from Relative to Absolute Reference | Excel Discussion (Misc queries) | |||
How do I get relative/absolute reference button (macros) | Excel Discussion (Misc queries) | |||
changing multiple cells from relative to absolute reference | Excel Discussion (Misc queries) | |||
How do I change an Excel range of cells from relative to absolute. | Excel Worksheet Functions |