Urgent help required for AutoFilling a formula containing range names!

bnoc7312

New Member
Joined
Sep 15, 2014
Messages
4
Hello guys,

I have a number of equations containing range names in a massive excel spread sheet that would take days to alter manually as the regular autofill function does not seem to work for this.

Here an example:

=(cost_1*component_demand_1+failure_1*cost_1+failure_1*installation_cost_1)/number_of_operators

I would like the cell below this one to read:

=(cost_2*component_demand_2+failure_2*cost_2+failure_2*installation_cost_1)/number_of_operators

This should continue down the entire column and finish with:

=(cost_100*component_demand_100+failure_100*cost_100+failure_100*installation_cost_100)/number_of_operators

I have about 20 such equations that need to be altered so it would take me countless hours to do manually. Is there a way of automating this process?

Thanks in advance!

Ian
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
So cost_1 is a named range and you have 100 named ranges that start with cost_ and a number and the same for the others you show in your equations, component demand etc.?
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Exactly! Each named range is a single cell too.

Because you have named ranges the solution I would give you would be highly volatile, meaning that depending on the amount of data you have it could slow down your spreadsheet significantly.
Every time something on your spread sheet changes all of these formulas would recalculate regardless of whether the data that was changed is used in any one formula or not.

If there is a relative relationship between these cells you would be better off just referencing the actual cells and copying the formula down, or across, whatever the case may be. With the volatile named range solution you would still have to copy the formulas down or across whatever the case may be.

If you are unfamiliar with relative relationships her is an example.
If I have numbers in column A and column B and I want to add those numbers together and I always want to add the number in column A with the adjacent number in column B I could type the following in cell C1.
=A1+B1 and then copy it down and in C2 the formula would become A2+B2, very easy and quick.

I'm not sure why you would have a named range for each individual cell. If your data has no relative relationship and is scattered all over the place I would highly recommend structuring it so that you can simply use cell references and then just copy your formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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