Local references
 

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?

Local references
 

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!
--
Richard

Search the web and raise money for charity at http://www.everyclick.com/


"Strd M 37" wrote:

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?

Local references
 

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" wrote:

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!
--
Richard

Search the web and raise money for charity at http://www.everyclick.com/


"Strd M 37" wrote:

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?

Local references
 

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

Search the web and raise money for charity at http://www.everyclick.com/


"Strd M 37" wrote:

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" wrote:

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!
--
Richard

Search the web and raise money for charity at http://www.everyclick.com/


"Strd M 37" wrote:

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?

Local references
 

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.

"Rich57" wrote:

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

Search the web and raise money for charity at http://www.everyclick.com/


"Strd M 37" wrote:

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" wrote:

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!
--
Richard

Search the web and raise money for charity at http://www.everyclick.com/


"Strd M 37" wrote:

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?