Can I avoid multiple conditions with dynamic sheet reference?

[eskpe]

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]

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]

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

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _

                          Col_num as Integer, Optional Range_look as Boolean)



''''''''''''''''''''''''''''''''''''''''''''''''

'Written by OzGrid.com



'Use VLOOKUP to Look across ALL Worksheets and stops _

at the first match found.

'''''''''''''''''''''''''''''''''''''''''''''''''

Dim wSheet As Worksheet

Dim vFound



On Error Resume Next



	For Each wSheet In ActiveWorkbook.Worksheets

		With wSheet

		Set Tble_Array = .Range(Tble_Array.Address)

			vFound = WorksheetFunction.VLookup _

			(Look_Value, Tble_Array, _

			Col_num, Range_look)

		End With

		If Not IsEmpty(vFound) Then Exit For

	Next wSheet



	Set Tble_Array = Nothing

	VLOOKAllSheets = vFound

End Function

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)