Linking named ranges

[Guest]

Hi.
I have an excel workbook with numerous sheets. On sheet 1 i wish to link
the data from the named ranges of the different worksheets.

E.g. Worksheet 2 has data in a named range for one topic, worksheet 3 has
data in a named for another etc, and worksheet 1 is the overview of the other
worksheets.

I want to link them so that if i insert a cell, row, the overview sheet
updates automatically.

Can anybody assist at all?

 

[Guest]

Simply refer to them by their names. Example, you have a named range on
Sheet2, and you want the total of the values in that range, you can use this
formula anywhere in the book:
=SUM(input1range)
assuming the name of that range is input1range, of course.

When you do insert rows/cells WITHIN that named range, the value in the
formula referring to it will update automatically. Adding data outside of
the range won't affect it.

If you've made copies of a worksheet with named ranges, then those copies
will also have named ranges of same name and you'll need to specify sheetname
along with the range name for various sheets. Lets say you make a copy of
Sheet2 that has a named range Input1Range on it, then 'Sheet2 (2)' also has a
range named Input1Range, but it is independent of the original, so you could
have 2 formulas as
=SUM(Sheet2!Input1Range) + SUM('Sheet2 (2)'!Input1Range)
if the sum of the first is 55 and the sum on Sheet2 (2) is 144, that formula
would return 199.

 

[Guest]

Yes.

You can a hyperlink to the named ranged in the Insert Hyperlink dialog box.
Select the user defined name under the Defined Names section in the Select a
Place in the document list.

Challa Prabhu