Data Validation

  • Thread starter RE: VLOOKUP fORMULA
  • 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
 
T

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top