Data Validation

  • Thread starter Thread starter RE: VLOOKUP fORMULA
  • Start date Start date
R

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
 
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)
 
Back
Top