Excel 2007 : How to change all reference cells in a worksheet

[CynthiaC]

Here is my situation:

I created one worksheet called: Assumptions.
In this worksheet I have 6 different columns representing 6 different cases.

I have completed the first case in a different worksheet called: WL. I would like to copy this case/worksheet WL and be able to change all the reference cells used in case#1: WL to case#2: FA. So all the cells used in my Assumptions worksheet were in the B colums for case 1: WL, for case 2: FA they will all be in the C column, same row, different column.

Does anyone knows a quick way to do the changes?

My case #1 worksheet as over 10 000 formulas, I really don't want to change each and every formula.

I was thinking there was a way to highlight ALL the reference cells used in my
Assumption worksheet, so I would have had to drag from B to C cells, only 50 cells. But I can't find it.

Thanks for your help.

[shg]

Find and Replace, Find WL!, Replace with FA!, Look in Formulas, Replace All

[CynthiaC]

If I used your trick I would have to find all b and replace with c, but then it will change all the B's for C's in my entire documents. I don't want to do that. I only want to change the b for c in the assumption sheet.

[shg]

Pardon ?

[CynthiaC]

I would create a FA worksheet from my WL page, a simple copy and paste. The only difference between WL and FA is that WL used reference cells from the column B while my FA worksheet use reference cells from the C column.

When I copy WL to create FA, all my formulas will stille refer toe the B cells. I want a quick trick to change the B column reference to the C column reference.

[shg]

Perhaps post a workbook and explain in context.

[forfriends]

Hate to dig up an old thread but I wanted a good solution to this problem as well, and doing a google search of "change all references in a sheet excel" turns this up as the first result, so I feel I should add my two cents.

The only way I can think of is this: drag column B over column C, then paste column C's correct values in (and put column B's values back if desired). Would like to hear alternative solutions.

BTW, @shg, I feel I should add it's exceedingly clear what OP was asking.

[forfriends]

Just thought of another one which I haven't tried but sounds like it could work:

Create a named range exactly the same size as your assumptions reference(s). Let's say you have assumptions A B C D E F, with sub-assumptions (individual cells/ranges) of 1 2 3 4 etc.

Name your ranges something easy and unique like AssumptionA_1, then do a find replace as required.

[FDibbins]

Hi forfriends and welcome to the forum

Seeing as this thread is almost 3 years old, and the OP did not respond to shg's suggestion of providing a sample workbook, I doubt they are still monitoring this thread now.

If you are asking the "same" question, please post it in your own thread (otherwise it's considerered hijacking). If you are offering another suggestion, the suggestion given by shg should work just find if done how he said (It will ONLY be applied to that sheet)

[forfriends]

Thanks for the welcome - I will post my own thread in the future, but I figured to help other travellers who stumble across this thread such as myself by google I thought it would be a good idea to post a solution here.

shg's solution doesn't work unless I misunderstood either the OP or shg (which I don't think I did..)

[FDibbins]

If you post a similar thread, we can discuss it there

[drums4tay]

I was following along fine until it was recommended that a new thread be started. Booh!

[FDibbins]

drums4tay, I *think* this is the contunuation of this...
http://www.excelforum.com/excel-form...g-formula.html