Absolute references within a multiple-worksheet project

Keela

New Member
Joined
Jan 27, 2005
Messages
3
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Keela

New Member
Joined
Jan 27, 2005
Messages
3
Is my question hard to understand? Let me know if I need to re-write it to make more sense, okay?
 

bjwillingham

Active Member
Joined
Jun 17, 2003
Messages
287
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

New Member
Joined
Jan 27, 2005
Messages
3
bjwillingham said:
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.
 

Forum statistics

Threads
1,147,845
Messages
5,743,513
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top