How to refer local cell like referred from other sheet

[nagovind]

This is regarding general cell reference made in a formula


1) For e.g in a sheet i will add 2 nos. like this


=A1+A2


2) For the above if i do the calculation in sheet2 with the nos. in sheet1 then the formula is

=Sheet1!A1+Sheet1!A4


My query is how to insert the (local)sheet reference when the calculation is done in the same sheet


This has many importance like while copying the data from one sheet to other the link will remains the same to maintain the result. Is there is any way out for this

 

[Hui]

Nagovind


There isn't any equivalent of the Range $ sign for copying relative or fixed values for use with Sheet names


Two options come to mind


1. Use Named Ranges, which can be specific to a sheet/cell or range.


2. Use the Indirect Function which allows variables to be used to define sheet and ranges.


Neither way will allow copy and paste relatively to the previous sheet.

 

[cyrilz]

Nagowing,


You can also use the "Paste Special" menu when copying, and click on "Paste link..." button.


You will end up with cells containing directly the link instead of absolute formulaes.