Pass Cell Content to a Formula not working

[learningexcel123]

Hello!

cell AC8 has my table name called tblOrders. I want to be able pass this value to a formula. If I type the table name in the formula everything works fine. If however I replace the table name with $AC$8, nothing shows up.


It's currently working if I use this:

IF(ISERROR(IF(B10=0,0,SUMIF(OFFSET((INDEX(tblOrders,,1)),0,0,COUNTA((INDEX(tblOrders,,1)),1)),B10,'11Orders'!J:J)))=TRUE,0,IF(B10=0,0,SUMIF(OFFSET((INDEX(tblOrders,,1)),0,0,COUNTA((INDEX(tblOrders,,1)),1)),B10,'11Orders'!J:J)))

Would like it to be this:

IF(ISERROR(IF(B11=0,0,SUMIF(OFFSET((INDEX($AC$8,,1)),0,0,COUNTA((INDEX($AC$8,,1)),1)),B11,'11Orders'!J:J)))=TRUE,0,IF(B11=0,0,SUMIF(OFFSET((INDEX($AC$8,,1)),0,0,COUNTA((INDEX($AC$8,,1)),1)),B11,'11Orders'!J:J)))

 

[AlphaFrog]

Use the INDIRECT function to convert the text in AC8 into a cell reference

IF(ISERROR(IF(B11=0,0,SUMIF(OFFSET((INDEX(INDIRECT($AC$8),,1)),0,0,COUNTA((INDEX(INDIRECT($AC$8),,1)),1)),B11,'11Orders'!J:J)))=TRUE,0,IF(B1 1=0,0,SUMIF(OFFSET((INDEX(INDIRECT($AC$8),,1)),0,0,COUNTA((INDEX(INDIRECT($AC$8),,1)),1)),B11,'11Orders'!J:J)))

 

[learningexcel123]

Thank you alpha dog - worked like a charm!