Are all the tables in the sheets you want to lookup of the same size like
Sheet2!A1:C100
Sheet3!A1:C100
?
--
Regards,
Peo Sjoblom
(No private emails please)
I need to be able to find a given text from workbook1 from any worksheet
in
workbook2 and be able to pull up the data from that worksheet(s) in
workbook2
with the text into the worksheet in workbook2. Does that make since??
I
don't really know the computer 'lingo'...
:
There are some fairly complex workarounds without using the add-in but
you
need to give us
more information
--
Regards,
Peo Sjoblom
(No private emails please)
Can you only use this as a function if you have downloaded
something??
:
I don't see any problems with that, if you would have asked that is
what
I
would have suggested..
I always use dummy sheets when doing things like this (I even put
one
at
the
start as well)
=VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0)
The zero at the end tells it to look for an exact match while
omitting
it
or
using 1 (or TRUE for 1 and FALSE for 0)
will look for the next largest value that is less than the lookup
value,
also the array must be sorted in ascending order
so the first sheet must have the lowest value etc. By using 0 or
FALSE
it
can be sorted in any order
Good luck
--
Regards,
Peo Sjoblom
message
I tried this and discovered a few problems that were self
inflicted.
I
rectified them as follows:
My VLOOKUP formula did not contain the ,0) at the end. I am not
familiar
with that!
I believe the problem with the file structure arose where there
was
no
worksheet M305. This made it create a link to look in the currect
directory
for a file that did not exist. To fix this where an M305 sheet is
not
needed,
I intend to create a hidden sheet named "stop" as the last sheet
and
refer
to
it at the end within my formula.
=VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0)
If you see any danger in theis approach, please let me know!
Your help is so very much appreciated, Peo. Thank you very much!
:
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" <
[email protected]>
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,THREED('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
:
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
:
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.