CODEWITHSUNDEEP
excelexcel-formula
Anirudh
Anirudh

Reputation: 591

rename excel worksheet without losing formula references

I thought it is going to be quick to what I plan to do but it is getting me no where.

I have a huge excel file with various tabs (i.e worksheets).

enter image description here

The main worksheet (the first tab) is named Inventory. I got a modified version of Inventory worksheet which has lot of changes. So I copied this as 'NewInventory' into the excel file.

I have lot of formulas with lookups pointing to Inventory but I need all of them to reference now to NewInventory.

I can't rename Inventory as InventoryOLD as the references gets changed. Is there a way I can have all lookup formuals (there are plenty in sheets such as Sh2, Sh3, Sh4 etc) to look at NewInventory instead of Inventory in some simple steps.

Here is an example formula

=VLOOKUP(A2, 'Inventory'!$R$2:$R164104, 1, FALSE)

I don't want to change for one cell and then drag it all the way down and repeat for other cells across various worksheets.

Please suggest.

Upvotes: 2

Views: 15936

Answers (1)

Ditto
Ditto

Reputation: 3344

Head into Replace (+H), expand

Enter "Inventory" in the "Find What" field. Enter "NewInventory" in the "Replace with" field. Change "Within" option to "Workbook".

Click "Find All". Nothing's changes yet, however you can browse down there and see if that nailed everything. If it did .. Replace All.

Viola . done. I've used this to exactly what you've done in the past, works like a charm. Good luck!

Upvotes: 4

Related Questions