Urgent help required for AutoFilling a formula containing range names!

[bnoc7312]

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

 

[skywriter]

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]

Exactly! Each named range is a single cell too.

 

[skywriter]

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.