Linking/refreshing data across worksheets

J

jennifer

This is a very basic question, but has me stumped nonetheless.

I have one worksheet which contains a list of my "master data", while several other worksheets in the workbook reference this list of data using an absolute reference i.e. ='Food Master'!B20

Is there an easy way to keep the data synced across worksheets if I insert a row into my master worksheet? FYI, I do not have this data saved as a list.

Is there a way to accomplish this with absolute references or do I need to use a pivot table, lookup table, etc.? I'm trying to keep this simple if at all possible.

thanks in advance,
jh
 
S

Stan Scott

"='Food Master'!B20" is not an absolute reference. "='Food
Master'!B$20" has an absolute row reference. When you insert rows, all of
the formulas referring to the original row remain the same. That is, if you
insert a new row above row 20 on the Food Master sheet, the formulas will
then refer to row B21.

What exactly do you mean by "synced"? Maybe a bit more detail is needed.


jennifer said:
This is a very basic question, but has me stumped nonetheless.

I have one worksheet which contains a list of my "master data", while
Click to expand...
several other worksheets in the workbook reference this list of data using
an absolute reference i.e. ='Food Master'!B20
Is there an easy way to keep the data synced across worksheets if I insert
Click to expand...
a row into my master worksheet? FYI, I do not have this data saved as a
list.
Is there a way to accomplish this with absolute references or do I need to
Click to expand...
use a pivot table, lookup table, etc.? I'm trying to keep this simple if
at all possible.
 
D

Debra Dalgleish

On the sheets that refer to the master list, you can use the Indirect
function. For example:

=INDIRECT("FoodMaster!"&ADDRESS(ROW(),COLUMN()))

will refer to the matching cell on the FoodMaster sheet.
 
Top