Use RC Notation to copy formula from different sheet while maintaining relative references

ianm101

New Member
Joined
Apr 1, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,
I am trying to copy a formula from another sheet ("SCE_Tables") to a central sheet ("Main"). "SCE_Tables" holds reference tables that I am copying over to main, based on selections from a form that the user fills out.

The formula in "SCE_Tables", uses RC-notation to subtract values from columns 2 to the left ('Prebill'), and 1 to the left ('Post BESS Bill') of the cells in the 'BESS Savings' column:
In the highlighted cell below:
VBA Code:
=RC[-2]-RC[-1]
. This cell is at Row 15, Column 6
mr_excel_demo.png


The issue arises when I try to fill the "Main" sheet range with these tables. The "Main" sheet range where these tables go have the same format as the reference tables:
The highlighted cell below (on the "Main" sheet), is at Row 21, Column 18. The formula in this cell is:
VBA Code:
=R[-6]C[-14]-R[-6]C[-13]
mr_excel_main_demo.png


The RC reference that is copied over to the "Main" sheet converts the RC reference back to that in the "SCE_Tables" sheet, resulting in the formula trying to calculate
VBA Code:
(R15, C4) - (R15, C5)
on the main sheet, when my desired formula is:
VBA Code:
(R21, C16) - (R21, C15)

Is there a way I dynamically create a formula that calculates the difference between the cells 2 to the left and 1 to the left of the cell in the "Main" sheet?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top