I have the following named ranges on a worksheet (KPI_1): KPI_1 (this has the Pass/Fail result), KPI_1_MTH_YR (this has the month and year), KPI_1_QUARTER (Determines the quarter - period being reported) using fixed ranges for each of the above KPI_1 = KPI_1!$N$5:$N$20000, KPI_1_MTH_YR = KPI_1!$B$5:$B$20000, KPI_1_QUARTER = KPI_1!$A$5:$A$20000

The data is entered from row 5 onwards, as the top 4 rows have been used for the column titles. Using the following formula with the defined named ranges (i.e. $A$5:$A$20000) work fine:

=COUNTIFS(KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2,KPI_1,"Pass")/COUNTIFS(KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2)),"NDA",COUNTIFS(KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2,KPI_1,"Pass")/COUNTIFS

(KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2))

returning a decimal value or percentage.

However, when using the following to create a dynamic named range KPI_1 = OFFSET(KPI_1!$N$5, 0, 0, COUNTA(KPI_1!$N:$N), 1) using similar for the KPI_1_MTH_YR and KPI_1_QUARTER formula returns error #VALUE.

Any ideas why the dynamic named ranges are not working?

Please note this question has been posted on Stackoverflow: https://stackoverflow.com/ques…with-dynamic-named-ranges

[h=1][/h]