| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change the column references automatically
Hey people,
Just struggling with it for the past few days. I have quite a lot formula in Sheet3 linked to Sheet1 and Sheet2. For example, Sheet3 has RangeA1 of sheet3 has a formula =Sheet1!A1 Range E2 of sheet3 has a formula =min(Sheet2!A:A) etc... i.e. assorted cells in Sheet3 with formula relating to the column A in sheet1 and sheet2 Most important thing is that formula in Sheet3 would relate to values in column A of Sheet1 and Sheet2 When I make a copy of the sheet3 and get a sheet4, I would like to relate the values of those cells to the one in column B. i.e. Sheet4 has Range A1 of sheet4 becomes = Sheet1!B1 becomes Range E2 of sheet4 becomes = min(Sheet2!B:B) etc... Sheet5 will be related to column C so the formula in sheet5 becomes Range A1 of sheet5 becomes = Sheet1!C1 becomes Range E2 of sheet5 becomes = min(Sheet2!C:C) ...... Sheet26 will be related to column Z so the formula in sheet26 becomes Range A1 of sheet26 becomes = Sheet1!Z1 becomes Range E2 of sheet26 becomes = min(Sheet2!Z:Z) ......................... Sheet 122 will be related to column DR so the formula in sheet122 becomes Range A1 of sheet122 becomes = Sheet1!DR1 becomes Range E2 of sheet122 becomes = min(Sheet2!DR:DR) I have to do this for 122 sheets and things are getting crazy when I do manually for me, due to lack of time. Please let me know if this could be accomplished by a macro. Thank you very much |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to change the column references automatically
On Oct 30, 12:05 pm, wrote:
Hey people, Just struggling with it for the past few days. I have quite a lot formula in Sheet3 linked to Sheet1 and Sheet2. For example, Sheet3 has RangeA1 of sheet3 has a formula =Sheet1!A1 Range E2 of sheet3 has a formula =min(Sheet2!A:A) etc... i.e. assorted cells in Sheet3 with formula relating to the column A in sheet1 and sheet2 Most important thing is that formula in Sheet3 would relate to values in column A of Sheet1 and Sheet2 When I make a copy of the sheet3 and get a sheet4, I would like to relate the values of those cells to the one in column B. i.e. Sheet4 has Range A1 of sheet4 becomes = Sheet1!B1 becomes Range E2 of sheet4 becomes = min(Sheet2!B:B) etc... Sheet5 will be related to column C so the formula in sheet5 becomes Range A1 of sheet5 becomes = Sheet1!C1 becomes Range E2 of sheet5 becomes = min(Sheet2!C:C) ..... Sheet26 will be related to column Z so the formula in sheet26 becomes Range A1 of sheet26 becomes = Sheet1!Z1 becomes Range E2 of sheet26 becomes = min(Sheet2!Z:Z) ........................ Sheet 122 will be related to column DR so the formula in sheet122 becomes Range A1 of sheet122 becomes = Sheet1!DR1 becomes Range E2 of sheet122 becomes = min(Sheet2!DR:DR) I have to do this for 122 sheets and things are getting crazy when I do manually for me, due to lack of time. Please let me know if this could be accomplished by a macro. Thank you very much A loop to automate this column referencing would do. I am pretty sure that I can write a macro to copy the sheets accordingly. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to change the column references automatically
On Oct 30, 12:15 pm, wrote:
On Oct 30, 12:05 pm, wrote: Hey people, Just struggling with it for the past few days. I have quite a lot formula in Sheet3 linked to Sheet1 and Sheet2. For example, Sheet3 has RangeA1 of sheet3 has a formula =Sheet1!A1 Range E2 of sheet3 has a formula =min(Sheet2!A:A) etc... i.e. assorted cells in Sheet3 with formula relating to the column A in sheet1 and sheet2 Most important thing is that formula in Sheet3 would relate to values in column A of Sheet1 and Sheet2 When I make a copy of the sheet3 and get a sheet4, I would like to relate the values of those cells to the one in column B. i.e. Sheet4 has Range A1 of sheet4 becomes = Sheet1!B1 becomes Range E2 of sheet4 becomes = min(Sheet2!B:B) etc... Sheet5 will be related to column C so the formula in sheet5 becomes Range A1 of sheet5 becomes = Sheet1!C1 becomes Range E2 of sheet5 becomes = min(Sheet2!C:C) ..... Sheet26 will be related to column Z so the formula in sheet26 becomes Range A1 of sheet26 becomes = Sheet1!Z1 becomes Range E2 of sheet26 becomes = min(Sheet2!Z:Z) ........................ Sheet 122 will be related to column DR so the formula in sheet122 becomes Range A1 of sheet122 becomes = Sheet1!DR1 becomes Range E2 of sheet122 becomes = min(Sheet2!DR:DR) I have to do this for 122 sheets and things are getting crazy when I do manually for me, due to lack of time. Please let me know if this could be accomplished by a macro. Thank you very much A loop to automate this column referencing would do. I am pretty sure that I can write a macro to copy the sheets accordingly. Thanks- Hide quoted text - - Show quoted text - Hey guys, if not for 122 sheets, please help me with some 5-10 sheets. I will replicate it for 122 sheets. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| automatically change column references in formulas | Excel Programming | |||
| how do I change cell references automatically in formulas | Charts and Charting in Excel | |||
| how do I change cell references in a column | Excel Worksheet Functions | |||
| how to change column references, while filling down another column | Excel Discussion (Misc queries) | |||
| How do I change column references when filling down a formula | Excel Discussion (Misc queries) | |||
Linear Mode
