MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Linking data from other worksheets.

Posted by Garry on April 24, 2001 7:45 PM

I seem to be unable to find any information on this.

I've created a INPUT worksheet which holds employee's names, pay rate, hours worked etc.

I have a Calculate worksheet that takes the data from the INPUT worksheet and peforms some cals.

The third sheet PAY lists all the employee's, pay amounts, hours worked, etc.

The problem I'm having is when i delete a employee (off the INPUT list by deleting the row eg he's left the company) its give me a #REF error on all the other worksheets. What type of link do i need (in my CALS and PAYS worksheet) so it looks at a cell number and that it doesnt care if i add or delete rows in between. (Input list is sorted A-Z so new ppl get added everwhere)

Sorry bout being long winded about this . .

Thanx in advance


Posted by Dave Hawley on April 24, 2001 8:52 PM

Hi Garry

This is normal for Excel to do this as the cell it is referencing is no longer available. Instead of deleting the row, select it and delete it's content only, then sort. this will push the empty row to the bottom.

DaveOzGrid Business Applications

Posted by S N on April 25, 2001 9:13 AM

I consider myself to be a "newbie", but perhaps you'd consider using the VLOOKUP function, as this will allow you to add/remove/sort employees any way you'd like in the future.