Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to edit another formula in excel?
I have multiple different formulas that I want copied many times. The
formula contains references to particular sheets (tabs at bottom of page). Everytime I copy the formulas and paste them over a range of cells, I want the sheet reference to change to a different sheet. How can I do this without manually changing the sheet reference one by one in these formulas? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to edit another formula in excel?
=IF(sheet1!$A1<"",sheet1!$A1,"")
say your formula is like the one above, select the entire area with formula then go to Replace type sheet1! with sheet2! replace all should take a couple of seconds, "engineer" wrote: I have multiple different formulas that I want copied many times. The formula contains references to particular sheets (tabs at bottom of page). Everytime I copy the formulas and paste them over a range of cells, I want the sheet reference to change to a different sheet. How can I do this without manually changing the sheet reference one by one in these formulas? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to edit another formula in excel?
Pilot,
Excellent solution, I was thinking in VBA code and you cut straight to the chase. Engineer: One thing that I might add...if you have any place on the sheet that has the same set of characters as the sheet name that you want to replace, and that is not part of a formula, you can use Edit | Go to | Special to select only the formulas on your worksheet, then the Find/Replace tool will work only on the selected cells. Note: You still could use VBA code to automate the whole process, including selecting only formulas and the find/replace tool. The advantage would be in the fact that the worksheet names in the workbook are also available in VBA, so that if you could define a condition so that you could detect the worksheet name from code, you would not have to type in the exact names, the VBA macro would do it for you. If Pilot's solution has helped or if this needs further clarification, please let us know. SongBear "ufo_pilot" wrote: =IF(sheet1!$A1<"",sheet1!$A1,"") say your formula is like the one above, select the entire area with formula then go to Replace type sheet1! with sheet2! replace all should take a couple of seconds, "engineer" wrote: I have multiple different formulas that I want copied many times. The formula contains references to particular sheets (tabs at bottom of page). Everytime I copy the formulas and paste them over a range of cells, I want the sheet reference to change to a different sheet. How can I do this without manually changing the sheet reference one by one in these formulas? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to edit another formula in excel?
I really appreciate your help.
Thanks!!! "SongBear" wrote: Pilot, Excellent solution, I was thinking in VBA code and you cut straight to the chase. Engineer: One thing that I might add...if you have any place on the sheet that has the same set of characters as the sheet name that you want to replace, and that is not part of a formula, you can use Edit | Go to | Special to select only the formulas on your worksheet, then the Find/Replace tool will work only on the selected cells. Note: You still could use VBA code to automate the whole process, including selecting only formulas and the find/replace tool. The advantage would be in the fact that the worksheet names in the workbook are also available in VBA, so that if you could define a condition so that you could detect the worksheet name from code, you would not have to type in the exact names, the VBA macro would do it for you. If Pilot's solution has helped or if this needs further clarification, please let us know. SongBear "ufo_pilot" wrote: =IF(sheet1!$A1<"",sheet1!$A1,"") say your formula is like the one above, select the entire area with formula then go to Replace type sheet1! with sheet2! replace all should take a couple of seconds, "engineer" wrote: I have multiple different formulas that I want copied many times. The formula contains references to particular sheets (tabs at bottom of page). Everytime I copy the formulas and paste them over a range of cells, I want the sheet reference to change to a different sheet. How can I do this without manually changing the sheet reference one by one in these formulas? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to edit another formula in excel?
I really appreciate your help.
Thanks!!! "ufo_pilot" wrote: =IF(sheet1!$A1<"",sheet1!$A1,"") say your formula is like the one above, select the entire area with formula then go to Replace type sheet1! with sheet2! replace all should take a couple of seconds, "engineer" wrote: I have multiple different formulas that I want copied many times. The formula contains references to particular sheets (tabs at bottom of page). Everytime I copy the formulas and paste them over a range of cells, I want the sheet reference to change to a different sheet. How can I do this without manually changing the sheet reference one by one in these formulas? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My excel program will not let me edit in the formula bar. | Excel Discussion (Misc queries) | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) | |||
How can i get an If formula in excel to edit another cell? | Excel Worksheet Functions | |||
edit formula bar in excel 2003? | New Users to Excel | |||
EDIT FORMULA BAR in excel 2003? why not? where is it? | Excel Worksheet Functions |