Linking/refreshing data across worksheets

[Guest]

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

 

[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.

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.

 

[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.