named range across sheets

[marleyps]

I wish to perform a index match lookup on a spreadsheet , but the proposedlookup range is spread over 3 sheets in another workbook . Is it possible to create a named range that is spread over several sheets, and does the range have to encompass the same set of cells on all the lookup sheets.

 

[CharlesChuckieCharles]

You can specify the same range across several sheets thus

=SUM(Sheet1:Sheet3!D5)
This would sum D5 on sheet1, sheet2 and sheet3


and creating a named range :- MyRange

and typing
=Sheet1:Sheet3!$A$1:$D$20
in Refers to: on the name manager

allows you to do

=SUM(MyRange) which would sum everything in MyRange (A1:d20) on 3 sheets

Note MyRange is not listed in the corner list where you'd expect to see named ranges listed

So yes you can have a named range over more than 1 sheet

 

[marleyps]

hi
Thanks for the reply
I created a named range across two sheets then tried a vlookup on sheet 3
the data was simple

sheet 1

a 1
b 2
c 3



sheet 2


d 4
e 5
f 6


on sheet 3 I entered e in cell f11 and in cell g11 I placed the formula =vlookup(f11,myrange,2,false) I expected to return 5 but all I get is #value what am I doing wrong.

 

[Aladin Akyurek]

hi
Thanks for the reply
I created a named range across two sheets then tried a vlookup on sheet 3
the data was simple

sheet 1

a 1
b 2
c 3



sheet 2


d 4
e 5
f 6


on sheet 3 I entered e in cell f11 and in cell g11 I placed the formula =vlookup(f11,myrange,2,false) I expected to return 5 but all I get is #value what am I doing wrong.

A different take...

Sheet3, G11

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,
    VLOOKUP(F11,Sheet1!$A$2:$B$4,2,0),
    VLOOKUP(F11,Sheet2!$A$2:$B$4,2,0)))