Data Validation

[RE: VLOOKUP fORMULA]

Dear All….Please help

Help on Excel -- Data Validation -- Hide Previously Used Items in Dropdown

I have an Excel file with two sheets (Room and Main)

On Sheet “ Roomâ€
(A1:A66) I have room numbers from 1 to 66

(F1:F66) I have formula
=IF(COUNTIF(Main!$A$6:$A$66,A1)>=1,"",ROW())

(G1:G66) I have formul
=IF(ROW(A1)-ROW(A$1)+1>COUNT(F$1:F$120),"",INDEX(A:A,SMALL(F$1:F$120,1+ROW(A1)-ROW(A$1))))

On sheet “Main†on A6
I have a Name manager “=RoomChk†with a formula
=OFFSET(Room!$G$1,0,0,COUNTA(Room!$G$1:$G$120)-COUNTBLANK(Room!$G$1:$G$120),1)

The following message appears:-
The Source currently evaluates to an error. Do you want to continue?

Pls help

 

[T. Valko]

Why are you referencing down to row 120 in some of your formulas?

It worked OK for me when I changed this:

=OFFSET(Room!$G$1,0,0,COUNTA(Room!$G$1:$G$120)-COUNTBLANK(Room!$G$1:$G$120),1)

To this:

=OFFSET(Room!$G$1,0,0,COUNTA(Room!$G$1:$G$66)-COUNTBLANK(Room!$G$1:$G$66),1)