Link Sheets

**tanya_c3** · 02-06-2009, 12:12 PM

Hi,

I am trying to link two sheets in the same file. My first sheet is a master file with lots of information. I'd like to take specific columns of that sheet and link it to a new sheet. I am currently doing a copy/paste special. This works when I update existing information. However, when I try to add a new row in my master file, it does not appear in my second sheet. HELP!

Thanks

**Cheeky Charlie** · 02-06-2009, 12:28 PM

You can cause a sheet to replicate another with a simple (sheet1)A1=Sheet2!A1 type formula.

More generally, why would you need the same data in a different sheet?

CC

**tanya_c3** · 02-06-2009, 01:25 PM

I don't however need all the data (or columns) from the first sheet.
The first sheet is my master list where I have all the info on employees/projects, etc...

What I am trying to do now is create another sheet to track attendance. I would only need to extract 3 columns of info from my first sheet and put it in the second sheet. I've tried the copy/paste special but I noticed that when I hire a new employee and add a row in the middle of my master sheet, it doesn't do the same on the second sheet.... Does this make more sense?

**Cheeky Charlie** · 02-06-2009, 01:32 PM

There are a lot of options within paste special - I don't know which you have employed.

Attached is how to make one sheet refer to another

**tanya_c3** · 02-06-2009, 03:08 PM

The problem with the file you sent is that if you add a row between row 4 and 5 (on Sheet 1), it will not add a row on Sheet 2. This is the problem I am encountering as well... How can we fix it?

I used copy/Paste special - paste link.

**Cheeky Charlie** · 02-09-2009, 05:15 AM

How about this:
=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)

You'd need to think about how big your table might grow to

CC

**tanya_c3** · 02-09-2009, 11:32 AM

Thanks so much Charlie.
I however have another little problem.

Col 1: Employee #
Col 2: Last Name, First Name
Col 3: Last Name
Col 4: First Name
Col 5: Main Project
Col 6: Job Title

Out of this master list, I only need the information of Columns 2 and 5 to appear on my new sheet. How do I do this?

**Cheeky Charlie** · 02-09-2009, 12:35 PM

I guess your first column is right, for the second column, use the same formula, but just change the offset address to $E$1

HTH

**tanya_c3** · 02-10-2009, 09:55 AM

Thanks so much.. it works.
I never heard of this formula in the past

**tanya_c3** · 02-11-2009, 03:03 PM

I just noticed a problem...

When I add a person on the first sheet, it will automatically add that person on the second sheet. The problem is that I am tracking attendance on the second sheet and when a new person appears on the second sheet, the attendance info doesn't get pushed down (it doesn't follow the name of the person). Basically, all the info I enter on my 2nd spreadsheet doesn't follow the name it's supposed to follow.

Eg. 2nd spreadsheet
Feb 1 Feb 2 Feb 3
Tanya X
David X

If I add a person between Tanya and David, it will appear but it the rest of the info will not move

Eg 2
Feb 1 Feb 2 Feb 3
Tanya X
Janice X
David

I need that X to follow David if he moves!