Local references

[Strd M 37]

I want to make a spreadsheet where i have a formula that references a cell,
and then be able to copy the formula to other cells, and have the same cell
be referenced, such as using $A$11, but i want to be able to copy this whole
array again without having to move the absolute references, making them like
local "absolute" references. Is this possible?

 

[Rich57]

I'm not clear on what you mean. When you say "copy this whole array again",
do you want the formulae in the array to refer to a new absolute reference?

Suppose you anchor the row references only (like A$11), and then copy your
array 4 columns to the right on the same row, then the formulae will change
to E$11.

Tell me if I'm barking up the wrong tree!

 

[Strd M 37]

that's kind of what i want, but i need to copy the whole array to the right,
and also down. let's say i have a 3x3 array, a1 to c3. i want cells a2,a3,
b1:c3 all to reference a1. then, i want to copy this array to d1:f3, and
also a4:c6. now, in each 3x3 array, i want to reference the top left cell
(a1,d1,a4) in each of the other cells in that 3x3 array, without having to
change the formula on each one

 

[Rich57]

Hmm. That means you don't want any $ in your formulae, doesn't it?

 

[Strd M 37]

Yes, this is just a simple example, but i need to repeatedly copy this
formula within the sheet, and then also create a much bigger sheet. i would
normally just change the formula, but the amount of data that this sheet has
is too big to change individually.