Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Cell Formula Reference
I have two issues both similar:
1) I have a number of columns, one of which has a formula with a Sheet!Cell reference in the formula. I want to change the Sheet reference to another sheet and previous the rest of the formula in that column. For example: Old Sheet Name: DoThis New Sheet Name: GoHere --- Can some loop be constructed using For Each in maybe a VB module which would do this? If so what is the object name and how would I do it? ---- Is there an easier way? ================ 2) Basically the same issue, except the link is in another workbook. I want to remove the linked workbook and move the single sheet on the Workbook to another Workbook with multiple sheets? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Cell Formula Reference
I just use the CTRL-H search/replace function for this kind of thing.
-- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "David" wrote: I have two issues both similar: 1) I have a number of columns, one of which has a formula with a Sheet!Cell reference in the formula. I want to change the Sheet reference to another sheet and previous the rest of the formula in that column. For example: Old Sheet Name: DoThis New Sheet Name: GoHere --- Can some loop be constructed using For Each in maybe a VB module which would do this? If so what is the object name and how would I do it? ---- Is there an easier way? ================ 2) Basically the same issue, except the link is in another workbook. I want to remove the linked workbook and move the single sheet on the Workbook to another Workbook with multiple sheets? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Cell Formula Reference
Hi David,
Ensure that you back up your workbooks before you do the following in case it does not work as expected. In your question 1 does your other sheet already exist? If so, then Find/Replace should work. Question 2. If you open all 3 workbooks concerned then you should be able to right click the worksheet name tab of the worksheet to be moved and select Move or Copy and simply move it to the other workbook and Excel should look after the links for you. -- Regards, OssieMac "David" wrote: I have two issues both similar: 1) I have a number of columns, one of which has a formula with a Sheet!Cell reference in the formula. I want to change the Sheet reference to another sheet and previous the rest of the formula in that column. For example: Old Sheet Name: DoThis New Sheet Name: GoHere --- Can some loop be constructed using For Each in maybe a VB module which would do this? If so what is the object name and how would I do it? ---- Is there an easier way? ================ 2) Basically the same issue, except the link is in another workbook. I want to remove the linked workbook and move the single sheet on the Workbook to another Workbook with multiple sheets? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Cell Formula Reference
OssieMac:
Thanks got it done. Copy worked great. Only issue was it keep the links to the other workbook on the copied sheet. ALso found a MS "Q" with an XLA which will find links. Appreciate the Assist. David "OssieMac" wrote in message ... Hi David, Ensure that you back up your workbooks before you do the following in case it does not work as expected. In your question 1 does your other sheet already exist? If so, then Find/Replace should work. Question 2. If you open all 3 workbooks concerned then you should be able to right click the worksheet name tab of the worksheet to be moved and select Move or Copy and simply move it to the other workbook and Excel should look after the links for you. -- Regards, OssieMac "David" wrote: I have two issues both similar: 1) I have a number of columns, one of which has a formula with a Sheet!Cell reference in the formula. I want to change the Sheet reference to another sheet and previous the rest of the formula in that column. For example: Old Sheet Name: DoThis New Sheet Name: GoHere --- Can some loop be constructed using For Each in maybe a VB module which would do this? If so what is the object name and how would I do it? ---- Is there an easier way? ================ 2) Basically the same issue, except the link is in another workbook. I want to remove the linked workbook and move the single sheet on the Workbook to another Workbook with multiple sheets? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy an exact formula without changing cell reference | Excel Worksheet Functions | |||
how to reference a changing 'last row' cell in a formula | Excel Programming | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
A cell reference in a formula changing | Excel Worksheet Functions | |||
HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE | Excel Discussion (Misc queries) |