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
 

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.
 

Forum statistics

Threads
1,081,706
Messages
5,360,769
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top