Copy Formulas with Name Ranges

[OilEconomist]

Thanks in advance for any suggestions which I will give feedback.

My actual file/case is much larger and entails a much larger amount of cells, but I have simplified it here.

I have my cells NameRanged and I would like to copy a formula which includes the name ranges, but it remains static when I do it. Is there a VBA code or something to where it will change the formula based on the name ranges.

For example say cells A1 and A2 are named Oil.Volume.1 and Oil.Volume.2 and I make cell A3 = Oil.Volume.1 + Oil.Volume.2

Now say cells:

• B1 and B2 are named Oil.Volume.3 and Oil.Volume.4
• C1 and C2 are named Oil.Volume.5 and Oil.Volume.6
• D1 and D2 are named Oil.Volume.7 and Oil.Volume.8
• etc.

If I copy cell A3 to B3, C3, and D3, it will make:

• B3 = Oil.Volume.1 + Oil.Volume.2 vs. Oil.Volume.3 and Oil.Volume.4
• C3 = Oil.Volume.1 + Oil.Volume.2 vs. Oil.Volume.5 and Oil.Volume.6
• D3 = Oil.Volume.1 + Oil.Volume.2 vs. Oil.Volume.7 and Oil.Volume.8

Understandably I can make a A3 = A1 + A2 so that:

• B3 = B1 + B2
• C3 = C1 + C2
• D3 = D1 + D2

But as I indicated, the spreadsheet is much larger, and the formulas involve more cells than indicated. Additionally, I would like to see the name ranges to help identify what cells are a part of the calculation as it’s not as simple as the given example.

 

[ClaireS]

Excel range names are absolute references, so when you copy them, they do not change, unlike cell references which use the relative locations unless you fix them with $ signs.

Just a thought, but have you considered making your data into a table (v2007 and above, though better from v2010)? That would at least allow you to have column names, but in a manner that does update when you copy. So if you labelled your columns, say, Oil Volume 1 and Oil Volume 2 etc, then the first total would sum Oil Volume 1

by name ​

and the copies would refer to Oil Volume 2, 3, 4 etc.

 

[OilEconomist]

Oh thanks for the suggestion as I could use that in the future. The way this table is set up and since somebody else is using it, I want to avoid using a data table.

Once again thanks so much as I can use this in the future.

 

[jim may]

Not Sure, But could you select (highlight) the area involved and then go to the Menu
Home tab, Find, Select - there do a:
Replace...
Find What? Replace With

Oil.Volume.3

Oil.Volume.1​

_{^{<strike>
</strike>}}Oil.Volume.5

Oil.Volume.1​

_{^{<strike>
</strike>}}Oil.Volume.7

Oil.Volume.1​

​

<strike></strike>

then do the same for for 4, 6, and 8 - replace with 2

Then delete the RangeNames 3,4,5,6,7,8+

 

[OilEconomist]

Not Sure, But could you select (highlight) the area involved and then go to the Menu
Home tab, Find, Select - there do a:
Replace...
Find What? Replace With

Oil.Volume.3
Oil.Volume.1

_{^{<strike>
</strike>}}Oil.Volume.5
Oil.Volume.1

_{^{<strike>
</strike>}}Oil.Volume.7
Oil.Volume.1

​

<strike></strike>

then do the same for for 4, 6, and 8 - replace with 2

Then delete the RangeNames 3,4,5,6,7,8+

I know this post is old, but I wanted to respond. I could have done the Find Replace, but it was such a large data set. I don't think I ever finished this project as the Manager scrapped it.