Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically change the referenced worksheet name in all formulas?
Hi,
I have a worksheet (call it "formulas") that has a series of formulas that work on data from another worksheet ("data 1"). I would like to write a VBA function that automatically updates all the formulas that reference the "data 1" worksheet on the "formulas" worksheet, to reference a new data sheet ("data 2"). "Data 1" and "Data 2" are identical in structure, they just have slightly different data. Therefore the cell references can remain the same, it is just the worksheet reference that needs to change. Please could someone tell me what the best way to do this is? Thanks in advance, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically change the referenced worksheet name in all formulas?
You can do it manually with a search and replace just highlight all the
cells and press Ctrl + H, treat with care as if you are not specific enough in what you search for you could get unexpected results. I.e. to take your example replace "Data 1" with "Data 2" if you replace "1" with "2" then the references elsewhere in the formulae will become corrupted. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically change the referenced worksheet name in all f
You may like to look at the Indirect function. Using this function you can
construct a reference to another worksheet using a formula. For instance if you use the following formula =Indirect(A1 & "!B1") then the formula will refer to Data 1's Cell B1 if the Cell A1 contains Data 1 but will refer to Data 2's cell B1 if A1 contains the value Data 2. Alok Joshi " wrote: Hi, I have a worksheet (call it "formulas") that has a series of formulas that work on data from another worksheet ("data 1"). I would like to write a VBA function that automatically updates all the formulas that reference the "data 1" worksheet on the "formulas" worksheet, to reference a new data sheet ("data 2"). "Data 1" and "Data 2" are identical in structure, they just have slightly different data. Therefore the cell references can remain the same, it is just the worksheet reference that needs to change. Please could someone tell me what the best way to do this is? Thanks in advance, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas Aren't Automatically Updating When Cells Change | Excel Discussion (Misc queries) | |||
Automatically change sequential worksheet names in formulas | Excel Worksheet Functions | |||
how do I change cell references automatically in formulas | Charts and Charting in Excel | |||
Has anyone had formulas change automatically with no explanation? | Excel Worksheet Functions | |||
Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions |