I am running a workbook with various versions of the below formula:
=CHOOSE(MATCH($D$5,{"Select reporting Month
","JAN07","FEB07","MAR07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07","OCT07","NOV07","DEC07"},0),JAN07!F14,JAN07!E$11,FEB07!E$11,MAR07!E$11,APR07!E$11,MAY07!E$11,JUN07!E$11,JUL07!E$11,AUG07!E$11,SEP07!E$11,OCT07!E$11,NOV07!E$11,DEC07!E$11)

I need to make the next formula in the same manner. I need to include all 12
months
=IF(ISERROR(IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB600,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB600,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB600,4,FALSE),"No
Data")))),"No
Responses",IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB600,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB600,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB600,4,FALSE),"No Data"))))

Thanks for any help

You could try the INDIRECT() function

=VLOOKUP(D6,INDIRECT(D5&"!D16:AB600"),4,FALSE)



"Scott@CW" wrote:

> I am running a workbook with various versions of the below formula:
> =CHOOSE(MATCH($D$5,{"Select reporting Month
> ","JAN07","FEB07","MAR07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07","OCT07","NOV07","DEC07"},0),JAN07!F14,JAN07!E$11,FEB07!E$11,MAR07!E$11,APR07!E$11,MAY07!E$11,JUN07!E$11,JUL07!E$11,AUG07!E$11,SEP07!E$11,OCT07!E$11,NOV07!E$11,DEC07!E$11)
>
> I need to make the next formula in the same manner. I need to include all 12
> months
> =IF(ISERROR(IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB600,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB600,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB600,4,FALSE),"No
> Data")))),"No
> Responses",IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB600,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB600,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB600,4,FALSE),"No Data"))))
>
> Thanks for any help

it works!!!!
you rock!!!!

"Duke Carey" wrote:

> You could try the INDIRECT() function
>
> =VLOOKUP(D6,INDIRECT(D5&"!D16:AB600"),4,FALSE)
>
>
>
> "Scott@CW" wrote:
>
> > I am running a workbook with various versions of the below formula:
> > =CHOOSE(MATCH($D$5,{"Select reporting Month
> > ","JAN07","FEB07","MAR07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07","OCT07","NOV07","DEC07"},0),JAN07!F14,JAN07!E$11,FEB07!E$11,MAR07!E$11,APR07!E$11,MAY07!E$11,JUN07!E$11,JUL07!E$11,AUG07!E$11,SEP07!E$11,OCT07!E$11,NOV07!E$11,DEC07!E$11)
> >
> > I need to make the next formula in the same manner. I need to include all 12
> > months
> > =IF(ISERROR(IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB600,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB600,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB600,4,FALSE),"No
> > Data")))),"No
> > Responses",IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB600,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB600,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB600,4,FALSE),"No Data"))))
> >
> > Thanks for any help