Updating Referenced Data Question

**floridarandy** · 04-22-2011, 12:54 PM

Greetings - I've got a workbook that contains various information, in separate worksheets, about a common list of people. The names of these people, their account number and account type are have been typed, alphabetically, into Columns A, B and C of Worksheet 1. Additional clients will be added in the future. There are additional columns of info that is unique to this worksheet.

For Worksheet 2 I want to begin by pulling the Columns A-C form Worksheet 1. I'm not sure how to copy these columns in a way that names will update when added, deleted or modified in Worksheet 1.

Thanks in advance.

Randy

**tlafferty** · 04-22-2011, 12:57 PM

Try copying the names when moving them to worksheet 2 and using paste special|paste link.

**floridarandy** · 04-22-2011, 01:00 PM

Originally Posted by tlafferty

Try copying the names when moving them to worksheet 2 and using paste special|paste link.

not familiar with this function. can you give me a quick overview of its application?

**tlafferty** · 04-22-2011, 01:40 PM

In Excel 2007 and 2010, the clipboard has a dropdown. Look for the button that has a chain link on it.

**floridarandy** · 04-22-2011, 02:33 PM

OK...part way there. The Paste Special/Paste Link works to copy names and info in Coumns 1-3 of Worksheet 1 to Worksheet 2, but I need the ability add rows in Worksheet 1 that will also update in Worksheet 2 and other worksheets using these Columns from Worksheet 1. Ideas?

**darkyam** · 04-22-2011, 04:17 PM

In Sheet2, A1, have =IF(Sheet1!A1="","",Sheet1!A1). Copy that over and down as far as you need (i.e., as far down as you think the list in Sheet1 is likely to go in the foreseeable future).

**floridarandy** · 04-22-2011, 06:36 PM

Originally Posted by darkyam

In Sheet2, A1, have =IF(Sheet1!A1="","",Sheet1!A1). Copy that over and down as far as you need (i.e., as far down as you think the list in Sheet1 is likely to go in the foreseeable future).

Darkyam - thank you. Simple, easy to understand, and it works!

OOPS...i thought it did, but now i find that it doesn't "automatically" update when I add rows to Sheet 1. It seems I must recopy the formula down the column in Sheet 2 in order for Sheet 2 "see" the change?

The solution must be "automatic" as these columns in Sheet 1 will be present in at least 5 worksheets and redoing the forumula is impractical.

Sorry

Randy