ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¡i¤w¸Ñ¨M¡j¼Æ¾ÚÅçÃÒ°ÝÃD Data Validation Hide Previously Used Dropdown list

¡i¤w¸Ñ¨M¡j¼Æ¾ÚÅçÃÒ°ÝÃD Data Validation Hide Previously Used Dropdown list

¥»©«³Ì«á¥Ñ ´å·à ©ó 2010-10-2 13:12 ½s¿è

¦U¦ì¤j¤j¡A¤p§Ì°Ñ¦Ò¤F¬YÀɮתº¼Æ¾ÚÅçÃÒ©Ò°µ¥X¨ÓªºÅçÃҮĪGAA7:AG7 return value ¬O 0¡A¦ý­W´o¤£ª¾¹D°ÝÃD¥X²{¦b­þ­Ó¤½¦¡©Î­þ­ÓÂI ¡C¡C¡C ¥i§_½Ð¤j¤jÀ°¦£«üÂI°g¬z
ªþ¤W¤p§Ì©Ò°Ñ¦ÒªºÀÉ®× DataValDailyListExample.zip (15.97 KB) ©M ¦Û°µªºÀÉ®× DataValidationTestError.rar (54.39 KB) ¡C

§A­nÅçÃҬƻò?
³o¬O¤½¦¡¶Ç¦^­pºâµ²ªG¸òÅçÃÒ¦³¬Æ»òÃö³s?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

©êºp¡A¥Ñ©ó¤p§Ì¥Îªº¬O­^¤åª©¡A©Ò¥H¤£¤ÓªÖ©w¡§ÅçÃÒ¡¨ªº¦WºÙ¬O§_¥¿½T¡A±æ¨£½Ì¡C
¨ä¹ê¡A¤p§Ì·Q§Ë¥Xªº®ÄªG¬O AA7:AG7 ¼Æ­ÈÀ³¸Ó¬O©M AA6:AG6 ¤@¼Ë¡Aº¸«á¡AH7 ªº©ì¦²ªí¤~¯àÅý¤p§Ì¿ï¾Ü AA7:AG7 ªº¼Æ­È¡C
º¸«á¡A¦bJ7ªº©ì¦²ªí´N·|Òç°£ H7 ªº¿ï¶µ¡AL7 ªº©ì¦²ªí´N·|ÀHµÛÒç°£ H7 ¸ò L7 ªº¿ï¶µ¡A¥H¦¹Ãþ±À¡C
¥i¬O¡A¥Ø«eªºª¬ªp¬O¤p§Ìªº¤u§@ªíDailyPlan!AA7:AG7 ªº return value ¬O 0¡Aµ²ªG H7 ªº©ì¦²ªí¤]¥u¦³ "0" ¿ï¶µ¡A©Ò¥H¥u¯à°±¯d¦b¦¹¶¥¬q¡C

ª`¡G¤p§Ì¬O°Ñ¦Ò¤F¨ä¥Lºô¯¸ªº¤u§@ªí¶i¦Ó½Æ»s¨ä¤½¦¡¦b¤p§Ìªº¤u§@ªí¡C

ÁÂÁ¡C

TOP

AA7°}¦C¤½¦¡
{=IF(COLUMN(A$1)>cnt,"",INDIRECT("R6C"&SMALL(IF(ISERROR(MATCH($AA$6:$AG$6,$H7:$N7,0)),COLUMN($AA$6:$AG$6)),COLUMN(A$1)),0))}
¦V¤U¦V¥k½Æ»s
©óH7§@¥Î®É©w¸q¦WºÙ
cnt=7-COUNTA(DailyPlan!$H7,DailyPlan!$J7,DailyPlan!$L7,DailyPlan!$N7)
list=OFFSET(DailyPlan!$AA7,,,,cnt)
¸ê®Æ/ÅçÃÒ/¦Û­q¤½¦¡
=list
DataValidationTestError.rar (60.29 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁ Áª©¥D¤j¤j ¦£¸Ì©â¶¢ ¬°¤p§Ì¸ÑµªÃøÃD
ÁöµM¤p§ÌÁÙ·d¤£²M·¡¤§«e¨Ì¼Ëµe¿cªº¤½¦¡­þ¸Ì¥X²{°ÝÃD
¦ý¤j¤j©Ò´£¨Ñªº¤½¦¡¤w¸gÀ°¤p§Ìµ¹°ÝÃD¸Ñ¨M¤F¡C

TOP

¥»©«³Ì«á¥Ñ ´å·à ©ó 2010-10-2 16:47 ½s¿è

¯d­Ó°O¿ý¡C

¦pªG¤T­ÓªÅªí¥u¦³¨â­Ó¿ï¶µ¡G
Define Name: ¡§cnt¡¨ ¡]¥ô¦ó¦r²´¥i¨ú¥N¦WºÙ¡^
  1. =2-COUNTA(DailyPlan!$L7:DailyPlan!$N7)
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD