Referencing a cell

[Wombat]

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

 

[Jim Thomlinson]

=indirect("B1")

 

[Wombat]

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]

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.

 

[מיכ×ל (מיקי) ×בידן]

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

 

[David Biddulph]

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

 

[Wombat]

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