Linking inside a document

[Guest]

This is probably a stupid question but...

I have a multi-page Excel worksheet. The first sheet is a list of all
positions in our agency, the following sheets list the various file rights,
groups, equipment etc. for each position. If I copy the list of positions
and paste special > link on subsequent pages, any changes I make on 1st page
replicate thruout the workbook. Good!

But if I ADD a position, it doesn't populate on other sheets. The "refresh
data" tool is not available.

Thanks!

Katie

 

[Guest]

Hi Katie

in your second sheets, it should show a formula of =Sheet1!A1 in A1 ,
=Sheet1!A2 in A2 accordingly so the Sheets 2 will reflect the value same as
Sheet 1.

However, if you add a row in Sheet 1, then nothing will happends in Sheet2,
i mean no new row will be inserted.

Is this your problem?

if yes, then i suggest you need to use absolute reference so Sheet2!A1 will
show what exact Sheet1!A1 show. this require you change the formula in Shee2
from =Sheet1!A1 to =Sheet1!$A$1 .

let say your data containing 30 rows, so make make it longer to 40 rows but
it will show 0 for those having no value in Sheet1.

For easier to set, I suggest you to first copy and paste the link in Sheet2
then find and replace the formula Find "!A" and replace with "!$A$" .
This is quite tricky but it work in my PC.

hope this help.

Leung