I have a cell in a worksheet = B1 of a different worksheet. What should I do
if I want this cell to ALWAYS show what is in B1 regardless of any new rows
added or anything else.
so its not referencing the value of the cell but rather the 'geographical'
location...

Thanks for any help

=indirect("B1")
--
HTH...

Jim Thomlinson


"Wombat" wrote:

I have a cell in a worksheet = B1 of a different worksheet. What should I do
if I want this cell to ALWAYS show what is in B1 regardless of any new rows
added or anything else.
so its not referencing the value of the cell but rather the 'geographical'
location...

Thanks for any help

Ok, what if I want to have lots of cells doing that and don't want to write
in the cell number everytime. Is there a way to copy this from one cell to
the other?

"Jim Thomlinson" wrote:

=indirect("B1")
--
HTH...

Jim Thomlinson


"Wombat" wrote:

I have a cell in a worksheet = B1 of a different worksheet. What should I do
if I want this cell to ALWAYS show what is in B1 regardless of any new rows
added or anything else.
so its not referencing the value of the cell but rather the 'geographical'
location...

Thanks for any help

Indirect evaluates a text address and returns the cell value from that
address. Text however does not increment as you drag. There are not a lot of
options here.

=indirect("B" & row())

where row returns the row number of the row number of the cell it is in.

Note that these functions are volatile and I do not recommend having
thousands of these types of formulas as the performance of the spreadsheet
will start to drop off.
--
HTH...

Jim Thomlinson


"Wombat" wrote:

Ok, what if I want to have lots of cells doing that and don't want to write
in the cell number everytime. Is there a way to copy this from one cell to
the other?

"Jim Thomlinson" wrote:

=indirect("B1")
--
HTH...

Jim Thomlinson


"Wombat" wrote:

I have a cell in a worksheet = B1 of a different worksheet. What should I do
if I want this cell to ALWAYS show what is in B1 regardless of any new rows
added or anything else.
so its not referencing the value of the cell but rather the 'geographical'
location...

Thanks for any help

If I understood your request and assuming your formula goes into cell H1 -
type:
=INDIRECT(ADDRESS(ROW(),COLUMN()-6))
which will refer to call B1
If you'll copy it 2 cells down & 3 cells right the formula will refer to
cell E3
Micky


"Wombat" wrote:

Ok, what if I want to have lots of cells doing that and don't want to write
in the cell number everytime. Is there a way to copy this from one cell to
the other?

"Jim Thomlinson" wrote:

=indirect("B1")
--
HTH...

Jim Thomlinson


"Wombat" wrote:

I have a cell in a worksheet = B1 of a different worksheet. What should I do
if I want this cell to ALWAYS show what is in B1 regardless of any new rows
added or anything else.
so its not referencing the value of the cell but rather the 'geographical'
location...

Thanks for any help

=INDIRECT("B"&ROW(A1)) and copy down.
--
David Biddulph

"Wombat" wrote in message
...
Ok, what if I want to have lots of cells doing that and don't want to
write
in the cell number everytime. Is there a way to copy this from one cell to
the other?

"Jim Thomlinson" wrote:

=indirect("B1")
--
HTH...

Jim Thomlinson


"Wombat" wrote:

I have a cell in a worksheet = B1 of a different worksheet. What should
I do
if I want this cell to ALWAYS show what is in B1 regardless of any new
rows
added or anything else.
so its not referencing the value of the cell but rather the
'geographical'
location...

Thanks for any help

Okey dokes... I'll see what I can put together. Thanks for the help!

"מיכאל (מיקי) אבידן" wrote:

If I understood your request and assuming your formula goes into cell H1 -
type:
=INDIRECT(ADDRESS(ROW(),COLUMN()-6))
which will refer to call B1
If you'll copy it 2 cells down & 3 cells right the formula will refer to
cell E3
Micky


"Wombat" wrote:

Ok, what if I want to have lots of cells doing that and don't want to write
in the cell number everytime. Is there a way to copy this from one cell to
the other?

"Jim Thomlinson" wrote:

=indirect("B1")
--
HTH...

Jim Thomlinson


"Wombat" wrote:

I have a cell in a worksheet = B1 of a different worksheet. What should I do
if I want this cell to ALWAYS show what is in B1 regardless of any new rows
added or anything else.
so its not referencing the value of the cell but rather the 'geographical'
location...

Thanks for any help