Can I avoid multiple conditions with dynamic sheet reference?

**eskpe** · 05-09-2009, 11:01 PM

Im trying to get a worksheet to lookup values in tables accross multiple sheets, deciding which sheet to look at by me changing the value in a box say A3, so far I have multiple IF(Value in A3=x look in Xsheet, if not, if it is y look in y sheet, if it is none, "Error") this is obviously tiresome to do, and gets very long excel equations causing me trouble

Id like to have something llke

=Lookup($C$3, $A$3!A:A,$A$3!C:C) but this isnt working for some reason.

There must be some function or some trick to allow me to do this, I just have no idea what it is.

Many Thanks

**Paul** · 05-10-2009, 12:01 AM

Hi Eskpe, and welcome to the forum.

You might want to research the INDIRECT function. In your example, this should work for you:

=LOOKUP(C3,INDIRECT(A3&"!A:A"),INDIRECT(A3&"!C:C"))

Hope that helps!

**martindwilson** · 05-10-2009, 04:21 AM

heres a good quick tutorial
http://www.brainbell.com/tutorials/m...ple_Tables.htm
there is also this udf from ozgrid which does a vlookup across the same range on all sheets

http://www.ozgrid.com/VBA/VlookupAllSheets.htm

Please Login or Register  to view this content.

once inserted in a new module(see where do i put code link in my signature)exactly the same as vlookup but use VLOOKAllSheets
=VLOOKAllSheets("your item",A1:A10,2,FALSE)

Can I avoid multiple conditions with dynamic sheet reference?

LinkBack

Thread Tools

Rate This Thread

Display

Can I avoid multiple conditions with dynamic sheet reference?

Re: Can I avoid multiple conditions with dynamic sheet reference?

Re: Can I avoid multiple conditions with dynamic sheet reference?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions