Multiple worksheet vlookup

[Anto111]

Hi guys,

I have a workbook containing 46 sheets and I am trying to use VLookup to
return a valiue on a summary sheet when one of the specific worksheet names
is entered in cell K2

The formula I am currently trying is highlighted below however it is
returning #REF!

=VLOOKUP(K2,THREED('[Heart Rate.xlsx]Week 1':'[Heart Rate.xlsx]Week
46'!$D$7:$NF$74),9,0)

The data to be looked up is located in a seperate workbook to the summary
sheet and the workbook is called "Heart Rate".

The worksheets within this workbook are labled "week 1" thorugh to "week 46".

Any help would be fantastic.

Many thanks in advance,

Ant

 

[igorek]

you can only do it via macro as vlookup and other lookups do nut support
ranges on multiple ranges...