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?
index(match
?