Add If to beginning of indirect lookup formula

[rhombus4]

From this threadhttp://www.mrexcel.com/forum/showthread.php?t=544974Currently have =VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)What I need is to add an If at the beginning so for example if cell C5 was blank or 0 then the result would be " or 0.i.e. something like=if(""&A1&"!c5="","",(VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

 

[rhombus4]

DO i need to add indirect to the formula at the beginning or something similarif(indirect(""&A1&"!c5="","",(VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

 

[jasonb75]

Is it just C5 you want to look at for blank / 0 or the result of the VLOOKUP?

 

[rhombus4]

Is it just C5 you want to look at for blank / 0 or the result of the VLOOKUP?

as Im copying the formula down just c5 and when copied down will be c6 c7 etcWhat i need is to use the names in column a as a link to sheet 2-50 but check if the relevant cell is blank before entering the indirect formula=VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0)

 

[jasonb75]

The problem with INDIRECT is that it doesn't recognise relative reference, so the row will not increase, it needs some extra tweaking to make that happen.

Should B2:D5 change to B3:D6, etc as you copy down as well?

 

[rhombus4]

In sheet 1 I have a list of the tab names in column A.I have sheets 2 -50 with tables in so the formula I had initially =VLOOKUP($B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0) works.What I need is if on sheet 2 for example cell C5 or C3 etc are blank then the answer on Sheet1 will be blank as well but not sure how to write the If before the Vlookup which incorporates the tab name

 

[rhombus4]

The problem with INDIRECT is that it doesn't recognise relative reference, so the row will not increase, it needs some extra tweaking to make that happen.

Should B2:D5 change to B3:D6, etc as you copy down as well?

Sorry the formulas I have now are absolute I just copied the formula from a different place which didnt show as absolute

 

[jasonb75]

I think this is right

=IF(INDIRECT(ADDRESS(ROW(A5),3,4,,A1))<>0,VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0),"")

 

[rhombus4]

I think this is right

=IF(INDIRECT(ADDRESS(ROW(A5),3,4,,A1))<>0,VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0),"")

Cant get it to workformulas fixed=VLOOKUP($B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)It looks for cell $B$2 in sheet1 then on sheet2 will look for the vlookup but I need an IF to say if cell C5 is "","",=VLOOKUP($B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)the answer will then be in Cell C8 in sheet1

 

[jasonb75]

=if(indirect("'"&A1&"'!C5")="","",VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

does it work in the first row?

if you copy it down, does it work in the second row?