This works for me
=VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0)
or are you using this from another workbook
=VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0)
replace test.xls with the name of the workbook
The other workbook needs to be open or you'll get a REF error when you try
to calculate it
I am assuming here that the sheets are named M010:M305
--
Regards,
Peo Sjoblom
"Ray Stubblefield" wrote in
message ...
Good morning, Peo.
I am having difficulty.
Following your lead, I tried:
=VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2)
It immediately changes to
=VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2)
Upon copying down my list of search criteria, I get the following:
=VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000
=VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000
=VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124
=VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124
There are no worksheets is this particular workbook that the reference can
search, so it is returning the previous result.
I tried:
=INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0),
2)
It immediately changes to:
=INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30
5'!$J$19)0),2)
but, happily, it does give me the correct results. However, upon saving
and
closing, the cells contain:
=INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE
D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2)
This is not a valid reference to the worksheet, but only to the directory
containing that worksheet.
I do appreciate your help with this, Peo!
Ray Stubbleefield
"Peo Sjoblom" wrote:
There is no built in lookuop that will work over multiple sheets, I
would
recommend using Laurent Longre's excellent Morefunc that can be
downloaded
here
http://longre.free.fr/english/
descriptions here
http://www.rhdatasolutions.com/morefunc/
In your case it would be
=VLOOKUP(Lookup_value,THREED( etc
Regards,
Peo Sjoblom
"Ray Stubblefield" wrote:
I want to create a summary sheet that will lookup a particular cells
value on
multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based
upon a
cell next to it ($I$19) that will match the criteria on the summary
sheet
(e.g. w1, w2, w3).
I have tried VLOOKAllSheets but when there are other similar workbooks
open,
it doesn't work right.
Anyone? I appreciate your help.