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?