|
Data Validation
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 formula =IF(ROW(A1)-ROW(A$1)+1COUNT(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 |
Data Validation
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) -- Biff Microsoft Excel MVP " VLOOKUP fORMULA" wrote in message ... 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 formula =IF(ROW(A1)-ROW(A$1)+1COUNT(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 |
| All times are GMT +1. The time now is 04:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com