i'm trying make column e in workbook display number of reports manager has submitted in given month in workbook b. want use counta formula count number of non-blank cells in column of workbook b, equivalent number of submitted reports. however, there multiple managers , each manager has own sheet in workbook b. example, manager john smith logs submitted reports in workbook b on sheet called "smith". name of sheet in workbook b located in cell q3 of workbook a. tried make work following code:
=counta((('[workbook b.xlsx]' & 'indirect(q3,false)' & '!$a$2')),('[workbook b.xlsx]' & 'indirect(q3,false)' & '!$a$500)) ideally, code count nonblank cells a2:a500 in workbook b , display number in workbook a.
i managed work changing code. indirect(q3) giving me #ref error, made q3 indirect after counta , worked.
=counta(indirect("'"&"[workbook b.xlsx]"&q3&"'!$a$1:$a$500")) 
Comments
Post a Comment