אתמול הראינו שיטה כדי למנוע הכנסת של ערכים כפולים בתא בו רשימה נפתחת של אימות נתונים. בפוסט הזה נדגים פתרון אחר, מבוסס נוסחאות.

הפתרון מבוסס מקרו מחקה את אימות הנתונים. הפתרון עם נוסחאות מבוסס על גישה אחרת. במקום לשאול "איך אני מפעיל שתי שיטות שונות של אימות נתונים על אותו תא", אנו שואלים "איך אני מונע הכנסה בתא של ערכים שכבר קיימים בטבלה".

הפתרון מבוסס על בנייה של רשימה מתגלגלת עם אימות נתונים מבוססת על רשימה דינמי בה מופיעים רק שמות שטרם הוכנסו בטבלה.

בסרטון הבא אפשר לראות שאחרי שמכניסים את "אסתר" בטבלה, השם נעלם מהרשימה המתגלגלת

המודל של הפתרון מבוסס על רשימה של כל השמות המותרים ורשימה נוספת בה מופיעים רק שמות שטרם שובצו בטבלה.

הטווח A5:A16 מכיל את כל השמות.

התאים הטווח B5:B16 ("aux1") מכיל את התוצאות של הנוסחה

=IF(COUNTIF($H$5:$H$7,$A$5:$A$16)=1,"",A5)

המשמעות: אם שם ברשימת השמות כבר מופיע בטבלה, התוצאה תהיה string באורך 0 (התא אינו ריק שהרי מכיל את הנוסחה); אם השם טרם מופיע בטבלה הוא יופיע ב-"aux1".

התאים בטווח C5:C16 מכילים את הנוסחה:

=COUNTIF($B$5:$B$16,"<"&B6)

הנוסחה מדרגת את הערכים של הטווח aux1; תאים ללא שם מקבלים את הערך 0, תאים אחרים מקבלים ערך לפי סדר האלף בית.

הטווח aux3 בנוי עם הנוסחה

=LARGE($C$5:$C$16,$B$1-ROW()+5)

הנוסחה מסדרת את הערכים שך aux2 בסדר עולה; תאים עם ערך 0 מחזירים ערך !NUM# טעות.

הצעד לפני האחרון הוא לבנות את הרשימה שך המספרים הפנוים (הטווח Available) בנוי עם הנוסחה

=INDEX($A$5:$A$16,MATCH(D5,$C$5:$C$16,0))

שמתרגם את רשימת המספרים של aux3 לשמות.

הצעד האחרון הוא ליצור שם (Defines Name) שיכלול את השמות של Available באופן דינמי

השם List_of_Names2 מפנה לנוסחה

=OFFSET(formula!$E$5,0,0,formula!$B$1,1)

הנוסחה משתמשת בתא  B1 שמכילה את הנוסחה

=COUNTIF($C$5:$C$16,">0")

כך השם המוגדר שלנו יכלול רק את השמות ולא את ערכי הטעות.

אפשר להסתיר את העמודות A:E

בטבלה שם מגדירים רשימה נפתחת עם אימות נתונים שמשתמש בשם List_of_Names2 כמקור הנתונים

ניתן להוריד את הקובץ, כולל גם את המקרו של הפוסט הקודם, כאן.