Worksheet Names Pull-Down in Formula?

[dougbert]

None of my searches on the forum seemed to directly address my problem. Seems simple enough. I'm hoping it's just a syntax error, but can't seem to wrap my head around what I'm missing.

In cell $A$1, I have a data validation, pull down menu with a listing of worksheets names contained within the same workbook. $A$1 is formatted as Text. Each worksheet name in the drop down list is surrounded by single quote marks.

I want to reference the value in cell $A$1 in a formula where that value will be the worksheet name. Here is the formula I tried that obviously doesn't work:
=IF(ISERROR(AVERAGEIFS($A$1!$AB$3:$AB$700,$A$1!$AB$3:$AB$700,"<>0")),"N/A",AVERAGEIFS($A$1!$AB$3:$AB$700,$A$1!$AB$3:$AB$700,"<>0"))

Pressing Enter results in an error with the first occurrence of $A$1 highlighted in the formula bar.

I will be adding additional criteria to this formula, which is why I'm using AVERAGEIFS. However, I need this simpler formula to work first.

Can anyone spot my syntax (hopefully) error?

If what I want to do is impossible, I'd appreciate any suggestions anyone might offer.

 

[AlphaFrog]

You can't simply insert a cell value within part of a cell reference. You have to use the INDIRECT function to do something like that. The INDIRECT function allows you to "build" a cell reference from text strings.

Replace this...
$A$1!$AB$3:$AB$700

With this...
INDIRECT($A$1 & "!$AB$3:$AB$700")

Be aware that the data validation in cell A1 may not interpret your quotes properly. You may want to omit them from the Data Validation list but include them in the INDIRECT formula e.g.
INDIRECT("'" & $A$1 & "'!$AB$3:$AB$700")

 

[dougbert]

Thanks for the education AlphaFrog! While I knew the INDIRECT function existed, I'd never used it before. So, naturally, it never occurred to me. Trust me...it's in my toolbox forevermore!

Your last example was perfect and solved my issue!

Thank you so much! ray: