Getting cell in one worksheet to refer to corresponding cell in another worksheet

  • Thread starter Thread starter Stephen Poley
  • Start date Start date
S

Stephen Poley

I have a worksheet Hours and a worksheet Costs. Each cell in Costs needs
to be a function of the corresponding cell in Hours so, for example,
Costs!K12 is a function of Hours!K12.

The problem is that cells frequently get inserted or deleted in Hours.
In this case Excel changes the reference in Costs so that, for example
Costs!K12 now refers to Hours!L12, which is no use.

How do I get Costs to always use the corresponding cells?

(I came across one or two old posts in the archive which seemed to imply
that this problem could be solved by means of the OFFSET function, but a
bit of experimenting seems to show this isn't the case.)
 
To always point to the cells in sheet: Hours

In sheet: Costs
-------------------
Instead of say:

In A1: =Hours!A1*2

you could try in A1

: =INDIRECT("Hours!A1")*2

or

: =OFFSET(Hours!$A$1,ROW(A1)-1,COLUMN(A1)-1)*2

Both are functionally equivalent to having
in A1 : =Hours!A1*2
 
To always point to the cells in sheet: Hours

In sheet: Costs
-------------------
Instead of say:

In A1: =Hours!A1*2

you could try in A1

: =INDIRECT("Hours!A1")*2
Click to expand...

This one isn't really on, because the formula doesn't propagate on copy.
With around 700 cells to start with, and maybe a few thousand in due
course, typing them in manually isn't an option.

or

: =OFFSET(Hours!$A$1,ROW(A1)-1,COLUMN(A1)-1)*2
Click to expand...

This appears to be the way to go - thank you very much.

It makes my resulting formula a bit of a brute though; I fear I'm going
to have to do some good documentation for the benefit of the poor ***
who'll have to maintain it!
 
To make the formula more flexible, use:

=INDIRECT("'Hours'!"&ADDRESS(ROW(),COLUMN()))
 
Debra Dalgleish said:
To make the formula more flexible, use:
=INDIRECT("'Hours'!"&ADDRESS(ROW(),COLUMN()))
Click to expand...

Thanks for the refined formula, Debra !

Stephen, you're welcome !
Thanks for the feedback.
 
Back
Top