Range of cells: Convert relative reference into absolute

I

Igor

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

jlclyde

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!

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

Igor

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

JLatham

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

Igor

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

Thank you, J!
 
J

JLatham

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.
 

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