Is there an easy way to change all instances of a cell reference with one
action? For example, when you enter a particular cell, Excel hightlights on
the spreadsheet all of the references within that formula, and if you click
on the border of one of the references, you can drag it to a new location.
However, if you have more than one reference to that cell within the formula,
it only changes one of those references, and you end up having to drag the
cell repeatedly for each reference. I was thinking maybe there would be a
shortcut like holding control or alt or some other combination to drag all of
them at once, but I can't find it. Does anyone know of a shortcut like this?

AlwaysFroosh! wrote:

Is there an easy way to change all instances of a cell reference with
one action? For example, when you enter a particular cell, Excel
hightlights on the spreadsheet all of the references within that
formula, and if you click on the border of one of the references, you
can drag it to a new location. However, if you have more than one
reference to that cell within the formula, it only changes one of
those references, and you end up having to drag the cell repeatedly
for each reference. I was thinking maybe there would be a shortcut
like holding control or alt or some other combination to drag all of
them at once, but I can't find it. Does anyone know of a shortcut
like this?



Search and Replace (Ctrl-H)????



--

AlwaysFroosh: Try find/replace. In 2007, it's on the home ribbon. You can
also key in ctrl+h. Be careful, because if you try to replace all instances
of A1, it will do this on the entire sheet. If you only want it to look at
one cell, select at least two before you do it.

In the "find what" field, type the reference you want to replace. In the
"replace with" field, type the new reference you want to use.

"AlwaysFroosh!" wrote:

Is there an easy way to change all instances of a cell reference with one
action? For example, when you enter a particular cell, Excel hightlights on
the spreadsheet all of the references within that formula, and if you click
on the border of one of the references, you can drag it to a new location.
However, if you have more than one reference to that cell within the formula,
it only changes one of those references, and you end up having to drag the
cell repeatedly for each reference. I was thinking maybe there would be a
shortcut like holding control or alt or some other combination to drag all of
them at once, but I can't find it. Does anyone know of a shortcut like this?

Search and replace will work, but it's a little awkward when already
modifying your formula graphically. Sometimes it forces you out of the cell,
and sometimes it modifies the formula in the background without updating the
current display. Being able to drag all the references at once would be
cleaner, but maybe there is no way to do this. Thanks for the suggestion.


"Chris Premo" wrote:

AlwaysFroosh! wrote:

Is there an easy way to change all instances of a cell reference with
one action? For example, when you enter a particular cell, Excel
hightlights on the spreadsheet all of the references within that
formula, and if you click on the border of one of the references, you
can drag it to a new location. However, if you have more than one
reference to that cell within the formula, it only changes one of
those references, and you end up having to drag the cell repeatedly
for each reference. I was thinking maybe there would be a shortcut
like holding control or alt or some other combination to drag all of
them at once, but I can't find it. Does anyone know of a shortcut
like this?



Search and Replace (Ctrl-H)????



--

Post a few examples of your formulas and what you want to change.

Might be as easy as the difference between relative and absolute references.


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 13:50:06 -0800, AlwaysFroosh!
wrote:

Is there an easy way to change all instances of a cell reference with one
action? For example, when you enter a particular cell, Excel hightlights on
the spreadsheet all of the references within that formula, and if you click
on the border of one of the references, you can drag it to a new location.
However, if you have more than one reference to that cell within the formula,
it only changes one of those references, and you end up having to drag the
cell repeatedly for each reference. I was thinking maybe there would be a
shortcut like holding control or alt or some other combination to drag all of
them at once, but I can't find it. Does anyone know of a shortcut like this?

Thanks for the ideas guys. I'm aware of the replace function, and yes this
will work for my case. TKS_Mark is correct though in that you have to be
cautious when attempting to replace all and be sure that it doesn't replace
all references within the worksheet.

I am strictly talking about editing the formula graphically. When you enter
into the formula Excel puts colored boxes around the references. You can
change these references by dragging them, however if that reference appears
more than once in the formula it only changes one reference, the first one I
think, and then changes the color of the reference left behind. I just
thought that maybe there was a quick little shortcut to drag all the
references at once, instead of having to do them one by one, or exiting the
formula and performing a replace action on the cell. I'm getting the idea
that this functionality does not exist, although I think it would be handy.

I've run into this scenario numerous times before, but the one that sparked
me to ask the question to see if there was something I didn't know is as
follows. This formula performs linear interpolation on data in a steam chart
to find the value that corresponds to the pressure in cell B9. The first part
checks to make sure the value being looked up is within the limits of the
chart, else it displays a message.

=IF(OR(B9INDEX(SteamLatentHeatPressures,ROWS(Stea mLatentHeatPressures)),B9<INDEX(SteamLatentHeatPre ssures,1)),B9&"
kPa is outside of Interpolation
Range",(B9-INDEX(SteamLatentHeatPressures,MATCH(B9,SteamLaten tHeatPressures,1),1))/(INDEX(SteamLatentHeatPressures,MATCH(B9,SteamLate ntHeatPressures)+1,1)-INDEX(SteamLatentHeatPressures,MATCH(B9,SteamLaten tHeatPressures,1),1))*(INDEX(SteamLatentHeatTemper atures,MATCH(B9,SteamLatentHeatPressures)+1,1)-INDEX(SteamLatentHeatTemperatures,MATCH(B9,SteamLa tentHeatPressures,1),1))+INDEX(SteamLatentHeatTemp eratures,MATCH(B9,SteamLatentHeatPressures,1),1))

As you can see there are many references to B9, so in this case I would use
a replace action. It just sparked my curiosity and I thought I might learn
something new if a shortcut existed.

Thanks for your help,
Graham

"Gord Dibben" wrote:

Post a few examples of your formulas and what you want to change.

Might be as easy as the difference between relative and absolute references.


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 13:50:06 -0800, AlwaysFroosh!
wrote:

Is there an easy way to change all instances of a cell reference with one
action? For example, when you enter a particular cell, Excel hightlights on
the spreadsheet all of the references within that formula, and if you click
on the border of one of the references, you can drag it to a new location.
However, if you have more than one reference to that cell within the formula,
it only changes one of those references, and you end up having to drag the
cell repeatedly for each reference. I was thinking maybe there would be a
shortcut like holding control or alt or some other combination to drag all of
them at once, but I can't find it. Does anyone know of a shortcut like this?