Referencing a cell

W

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
 
W

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?
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top