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))

 

[jasonb75]

comes up with Ref# errorthis is what I have in sheet 1Tom is B1101 starts A4so the formula in the stats which equals 12 is =IF(INDIRECT("'"&$A4&"'!C2")="","",VLOOKUP($B$1,INDIRECT("'"&$A4&"'!B2:D5"),2,0)) Tom Stats Errors101 12 101801 15 1001501 15 1112201 16 1012901 19 85

That looks like a cat jumped on the keyboard, more than anyone trying to explain a problem or ask a question!

Please make an effort to use the return key in your replies occasionally and make the text easier to read.

If the address formula returns a #REF! error then the name in column A is not the same as the sheet name to refer to, either that or you have incorrectly described what you want.

What is in A4?

Please also give the formula for what you want to do without using any INDIRECT references, i.e.

=IF(Sheet2!C5="","",VLOOKUP($B$1,Sheet2!B2:D5,2,0))

This will make it easier to evaluate the error source.

 

[rhombus4]

That looks like a cat jumped on the keyboard, more than anyone trying to explain a problem or ask a question!

Please make an effort to use the return key in your replies occasionally and make the text easier to read.

If the address formula returns a #REF! error then the name in column A is not the same as the sheet name to refer to, either that or you have incorrectly described what you want.

What is in A4?

Please also give the formula for what you want to do without using any INDIRECT references, i.e.

=IF(Sheet2!C5="","",VLOOKUP($B$1,Sheet2!B2:D5,2,0))

This will make it easier to evaluate the error source.

Apologies for the text I am using return key which shows as new line when typing but not when submitted.in A4 on sheet1 is 101 a5 801 a6 1501 they are just dates.Where it looks for the data in tabs 101 801 1501 etc a4 is blankI have got it to work using on sheet1 (cellC4) =IF(INDIRECT("'"&$A4&"'!C2")="","",VLOOKUP($B$1,INDIRECT("'"&$A4&"'!B2:D5"),2,0))however when i copy all on sheet1 to sheet2 it wont work they will only go blank when toms is blankthink it is the C2 part that probably needs changing to C3 like somkind of lookupSheet1 is tomsSheet2 is harrys

 

[jasonb75]

Are the dates in column A formula generated?

Do they appear in the formula bar the same as they appear in the cell?

Based on what you've said the formula back in post #15 should be working, so it's possible it could be a formatting issue.

What format is being used on the date cells in column A?

 

[rhombus4]

Are the dates in column A formula generated?

Do they appear in the formula bar the same as they appear in the cell?

Based on what you've said the formula back in post #15 should be working, so it's possible it could be a formatting issue.

What format is being used on the date cells in column A?

Thanks for all your help. It does work on the sheet1 but had to do Find and replace for other sheets