Macro (or other route) for changing several formulas at once

[n_lince]

I have a sheet with several formulas that need to be adjusted, and I'm sure there's a faster way to do it than by hand! All the formulas are INDEX formulas (not the array option), and each one is unique. For example, the formulas in F11, F12, and F13 are:

=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A1))
=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A2))
=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A3))

And the formulas in I11-13 are:

=INDEX('App 1T Staff-MA'!$E$24:$N$24,1,ROW(A1))
=INDEX('App 1T Staff-MA'!$E$24:$N$24,1,ROW(A2))
=INDEX('App 1T Staff-MA'!$E$24:$N$24,1,ROW(A3))


For each of formula on the sheet, I'd like to make the following modification (noted in bold):

=original index formula + (original index formula * SA_staff_time)

where the "SA_staff_time" is a constant named elsewhere in the Workbook. So for example, the formulas noted above for cells F11-13 should look like this after the change:

=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A1)) + ((INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A1))) * SA_staff_time)
=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A2)) + ((INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A2))) * SA_staff_time)
=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A3)) + ((INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A3))) * SA_staff_time)

Is there an easy way to do this with a macro?

Thanks for any advice.

-Naomi

 

[cpatel13]

I have a sheet with several formulas that need to be adjusted, and I'm sure there's a faster way to do it than by hand! All the formulas are INDEX formulas (not the array option), and each one is unique. For example, the formulas in F11, F12, and F13 are:

=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A1))
=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A2))
=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A3))

And the formulas in I11-13 are:

=INDEX('App 1T Staff-MA'!$E$24:$N$24,1,ROW(A1))
=INDEX('App 1T Staff-MA'!$E$24:$N$24,1,ROW(A2))
=INDEX('App 1T Staff-MA'!$E$24:$N$24,1,ROW(A3))


For each of formula on the sheet, I'd like to make the following modification (noted in bold):

=original index formula + (original index formula * SA_staff_time)

where the "SA_staff_time" is a constant named elsewhere in the Workbook. So for example, the formulas noted above for cells F11-13 should look like this after the change:

=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A1)) + ((INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A1))) * SA_staff_time)
=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A2)) + ((INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A2))) * SA_staff_time)
=INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A3)) + ((INDEX('App 1T Staff-MVA'!$E$23:$N$23,1,ROW(A3))) * SA_staff_time)

Is there an easy way to do this with a macro?

Thanks for any advice.

-Naomi

There is a way out, could you let me know where are the changes needs to be made, are those in the same field or are the changes which you highlighted in the bold should be the same in various other worksheets in your workbook, a quick screen-shot or a dummy file would be appreciated.

 

[shg]

Just modify the first formula and copy down:


=INDEX('App 1T Staff-MA'!$E$24:$N$24,1,ROW(A1)) * (1 + SA_staff_time)

 

[shg]

Or, you could enter a formula in a convenient cell:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#FFFF00"]

1.1​

[/td][td][/td][td]F9: =1 + SA_Staff_Time[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][td]F11: =INDEX(Sheet2!$E$23:$N$23, 1, ROW(A1))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][td][/td][/tr]
[/table]

... then copy that cell, select the formula cells, and do Paste > Paste Special, and tick Multiply:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#FFFF00"]

1.1​

[/td][td][/td][td]F9: =1 + SA_Staff_Time[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][td]F11: =(INDEX(Sheet2!$E$23:$N$23, 1, ROW(A1)))*(1 + SA_Staff_Time)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][td][/td][/tr]
[/table]

Then you can clear the temporary formula cell.

 

[mikerickson]

Just modify the first formula and copy down:


=INDEX('App 1T Staff-MA'!$E$24:$N$24,1,ROW(A1)) * (1 + SA_staff_time)

Similarly you could find = and replace with =(1+SA_staff_time)* for a bulk change.