Changing Formulas from Another Worksheet.

adillard

New Member
Joined
Jul 11, 2006
Messages
26
Well, I hope I can explain this clearly. I have formulas in a worksheet such as (=Average(E7, F7, G7, H7, AQ7)*0.6, which the cell reference is (I7) the formula gives a 60% value of the total. Now in another worksheet I want to change this formuls value from 60% to maybe 50% so all I want to do is type 0.5 an it will change the value of the formula on that worksheet. Ther are 30 formulas that will be effected such as: (I7, then I8, etc. The same applies the the formuls (E7 Etc. become E8 Etc. and so on forthe total of 30 forumlas.
Can anyone or does any know how to do this type of function. Thanks
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,902
You could use a named value
Name: PercentOfTotal
Value: .5

and add "*PercentOfTotal" to all the formulas on the sheet.

Change the named value at will (or set it to a cell address or...)

or are you asking for the technique to change all the formulas on a worksheet? If so Find = , ReplaceWith =PercentOfTotal*
 
Last edited:

adillard

New Member
Joined
Jul 11, 2006
Messages
26
OK PercentOfTotal - Please show an example:
Worksheet name is: Grade Book English
Formula is: =average(E7,F7,G7,H7,Aq7)*0.6
Cell: I7

The new worksheet to change the percent of the value of 0.6:
SetUp Values
Cell: Any

Now I presume you mean this: =PercentOfToal(='Grade Book English'!(I7) then in this cell where the formula is just enter 0.5 or 0.4 and it will change the reference cell value in that worksheet. Correct or wrong. Thanks
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,902
Create the name PercentOfTotal as described above.
Activate the sheet containing the formulas you want to change.
From the Edit > Replace
FindWhat: )*0.6
ReplaceWith : )* PercentOfTotal
 

adillard

New Member
Joined
Jul 11, 2006
Messages
26
Ok, I understand but this is not just what I need.
I really want to make a function as a seup and change for certin values in other worksheets from a main worksheet, within to hold workbook system.
For example:

Change grade value for the test: .3
Change garde value for assessments: .7

This will then change all the formulas on the worksheet that does the calculation.

So if the worksheet (Grade Book English) is calculating .6 and .4, which equals 100% total value. Then if the setup requirements change say to .7 and .3 then on the worksheet that is the setup worksheet area for values, the owner can change the value by just typing in the value.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top