Absolute references within a multiple-worksheet project

[Keela]

Hello. First of all, the previous posts on this board have already helped me through 3 days of Excel problems. Thank you for your knowledge!

I couldn't find a previous answer to this one, but I have no doubt someone will know how to do this....

The three relevant worksheets within my project are as follows:
Current Worklist- Data source for all other worksheets
Completed Work- Rows from Current Worklist get copied here as an 'archive'
Capacity- Contains formulas which make use of the data in Current Worklist . Data is 'pulled' from Current Worklist via absolute references.

What I would like is for the data in Capacity to always match (row for row) the data in Current Worklist. In other words, if the unique row identifier in column A of Current Worklist reads: 1,2,3,4,5 (in rows 1-5) I want the same rows displayed in Capacity, with the first 5 rows in Column A reading 1,2,3,4,5 as well.

My problem occurs when I 'cut' completed items from Current Worklist and move them to Completed Work. I assumed that if I made the references within Capacity (to Current Worklist) absolute, that the first 5 rows within Capacity would always display whatever was in the first 5 rows of Current Worklist. Instead, the references within Capacity change when rows are cut from Current Worklist and pasted to Completed Work. After the cut/paste, Capacity contains absolute references to the cells within Completed Work where the data was copied to.

I suspect I may be able to do something with the Indirect function, but I'm not sure and would appreciate any suggestions one of you may have.

Thank you!
Keela

 

[Keela]

Is my question hard to understand? Let me know if I need to re-write it to make more sense, okay?

 

[bjwillingham]

When you 'cut', any references using that cell are updated to the new location. That is the way Excel works.

You could 'copy' the data to completed, then clear the row (don't delete) in current.

You could also use the offset function in capcity to reference current work. The only reference that will adjust with a cut would be the anchor reference in the offset function. So if you would never 'cut' cell A1, then you could build all the offset functions to reference from A1.

 

[Keela]

You could also use the offset function in capcity to reference current work. The only reference that will adjust with a cut would be the anchor reference in the offset function. So if you would never 'cut' cell A1, then you could build all the offset functions to reference from A1.

Thanks Brad- I'm trying this now. Can I build all the references (multiple columns) from the one cell? I've never used the offset function before.