Hello everyone.

Is anyone knows how to automatically replace part of a formula, which is a link on that formula?

Right now, I'm trying to create a macro that would enable me to replace a link on a formula.

The formula is shown below:

=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Jan!C9

I would like to know whether it is possible to create a macro that will prompts user to input the first three letters of a particular month (eg., Jan, Feb, Mac...) and then automatically replace the formula in a range of cells acccording to that particular month.

For example, the original formula is:

=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Jan!C9

When the user inputs Feb inside the prompt box, all the cells that contain this formula will be replaced with this formula:

=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Feb!C9

The worksheet that contains this formula is consisting of Column A until BZ and the numbers of rows are indeterminate. However, the first four rows are reserved for headers. Thus, the first cell is A5.

The first workbook is the source of the link and contains multiple worksheets according to months.

Currently, I tried to use the indirect method but my boss, the headmaster along with several colleagues found it quite difficult for them to use.

Any help or assistance is greatly treasured.

Thanks.

Is anyone knows how to automatically replace part of a formula, which is a link on that formula?

Right now, I'm trying to create a macro that would enable me to replace a link on a formula.

The formula is shown below:

=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Jan!C9

I would like to know whether it is possible to create a macro that will prompts user to input the first three letters of a particular month (eg., Jan, Feb, Mac...) and then automatically replace the formula in a range of cells acccording to that particular month.

For example, the original formula is:

=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Jan!C9

When the user inputs Feb inside the prompt box, all the cells that contain this formula will be replaced with this formula:

=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Feb!C9

The worksheet that contains this formula is consisting of Column A until BZ and the numbers of rows are indeterminate. However, the first four rows are reserved for headers. Thus, the first cell is A5.

The first workbook is the source of the link and contains multiple worksheets according to months.

Currently, I tried to use the indirect method but my boss, the headmaster along with several colleagues found it quite difficult for them to use.

Any help or assistance is greatly treasured.

Thanks.

Last edited: