Copy formula without changing references [Excel 2007]

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Piotr - 07 May 2008 09:07 GMT

Hello!

In my Workbook I have many cells with data validation settings as follows:

Allowed data: List

Source: =INDIRECT(L9)

Now, I`d like to change the source formula to the following:

=IF(M9="P";INDIRECT(L9);R11:R21)

When I check the option "Make changes in every cell with the same
settings", Excel does it, but it also adjusts the cell references"

Is there a way to automatically change the formula in every cell with
the same settings, but leaving the formula untouched?

The formulas to be changed are not next to each other, they are located
throughout the Workbook, in several worksheets, in hundreds of cells, so
the copy/paste option wouldn`t make me happy.

I tried "Find and Replace", but Excel doesn`t find the data to be
changed. I use polish version of the software, so it might be, that some
translations I made, are not accurate.

Thanks in advance
Peter

Reply to this Message

Jim Rech - 07 May 2008 12:03 GMT

Making all formula references absolute should fix this:

=IF($M$9="P";INDIRECT($L$9);$R$11:$R$21)

Signature

Jim

| Hello!
|
[quoted text clipped - 24 lines]
| Thanks in advance
| Peter

Reply to this Message

Piotr - 07 May 2008 21:42 GMT

Jim Rech pisze:

> Making all formula references absolute should fix this:
>
> =IF($M$9="P";INDIRECT($L$9);$R$11:$R$21)

Works great! Thanks! Good to know, what do all these "dollars" do. That
makes the life much easier ;).

Greetings
Peter

Reply to this Message

Gord Dibben - 08 May 2008 00:15 GMT

The $ signs "fix" the row and column references so they don't change as you
copy.

See help on absolute and relative cell referencing.

Gord Dibben MS Excel MVP

>Jim Rech pisze:
>> Making all formula references absolute should fix this:
[quoted text clipped - 6 lines]
>Greetings
>Peter

Reply to this Message