How can I get the relative position of a cell in a range?

Question

I have a range of cells in a workbook, and I want to dynamically replicate them in another workbook. I don't want to use explicit cell references in case the range is moved, I always try to use named ranges for cross-workbook references. So I want to enter a formula in the replicated workbook that uses the named range to get the value that is in the same position. I need to calculate what values to use in the parameters to the OFFSET() function.

What I want is something like this:

=OFFSET('Book1'!NamedRange,x,y)

What can I put into x and y to calculate the current cell's relative position within a same-sized area in Workbook 2?

Or, is there another way to replicate a named range from one workbook into another?

Answer 1

I have figured out an answer! I set the first formula (let's say it's A1) to this:

=OFFSET('Book1'!NamedRange,ROW(A1)-ROW($A$1),COLUMN(A1)-COLUMN($A$1),1,1)

I can then drag this formula across the range, and the formulae all work to calculate their own row and column positions relative to A1. The ,1,1 at the end is so that I get a single cell, rather than a shifted range of the same size as the NamedRange.

Answer 2

If you have the named range in both workbooks (exactly the same name) excel will ask you if you would like the formula to reference the named range in the new or old workbook. At which point you can click "new" (or similar) et voila.