# Urgent help required for AutoFilling a formula containing range names!

#### bnoc7312

##### New Member
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?

Ian

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### skywriter

##### Well-known Member
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.?

#### bnoc7312

##### New Member
Exactly! Each named range is a single cell too.

#### skywriter

##### Well-known Member
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.

Replies
6
Views
973
Replies
0
Views
334
Replies
7
Views
361
Replies
2
Views
269
Replies
17
Views
515

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,903
Messages
5,834,309
Members
430,276
Latest member
legalcriminal015

### 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.

### Which adblocker are you using?

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

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