Using location and not cell contents

[David Schwartz]

I didn't really know how else to define the subject of this question.

I have a table of hours assigned to different tasks (columns) for
various prioritized projects (rows). I wanted to have a parallel table
on another worksheet addressing the cost of the various projects'
tasks. I want it so that the Cost worksheet would update when a task
on the Hours worksheet is moved up or down (high/lower priority).

However, when I "shift down and move" a row (project) on the Hours
worksheet, the corresponding cells on the Cost worksheet adjust to
**follow** the re-prioritized project. Using absolute references
doesn't address this issue.

So, how can I make the Cost worksheet reflect the current physical
locations on the Hours worksheet rather than following the Hours cells
around as they get moved?

TIA,
David

 

[Dennis Chou]

Have you tried using the indirect function?

i.e.
=indirect("a1")