Referencing Excel name range from another worksheet

[mockions]

Suppose Sheet1, I defined the name of range A1-A5 with the name, "Range1." I would like to reference this on Sheet2, but starting from B3-B7, with the formula "=Range1."

The problem I've been encountering is that Excel bases it entirely on rows. So, B3 would reference A3 from Sheet1 and so on.

How do I fix this issue?

[Tony Valko]

Try it like this...

Entered in B3 and copied down to B7:

=INDEX(Range1,ROWS(B$3:B3))

[mockions]

What if, instead of referencing a range, I wanted to reference a table?

So, for example, I have a table from B3 to E:88 called "Table1" and I would like to reference it in B:9 to E:94?

[ben_hensel]

Referencing tables is easy; the whole table can be referenced as a matrix just by calling out the name (eg, "Table1") or a column in the table can be called out specifically as an array (eg, "Table1[Column1]").

Well, I don't understand what you're getting at with those overlapping references.

That is, what do you mean by having B9:E94 reference Table1 in B3:E88?

[mockions]

For example, I want to reference Table1 in Sheet1, which has several rows and columns. How would I reference it in Sheet2?

[ben_hensel]

=INDEX(Table1, row_num, col_num)

Because the table is a named range, you don't need to say which page it's on, excel baked that information into the name definition. You only have to name the table appropriately.

[mockions]

What if I want to do a range with multiple rows and columns?

For example, suppose I defined range, Range1 in Sheet1. Range 1 encompasses all the content in A6 to D13. I want to reference Range1 in Sheet2 in A7 to D14. How would I do this?

[Tony Valko]

Like this...

Entered in Sheet2 A7:

=INDEX(Range1,ROWS(A$7:A7),COLUMNS($A7:A7))

Copy across to D7 then down to A14:D14.

[mockions]

It worked. Thanks!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.