آلن می‌تواند از اعتبارسنجی داده‌ها برای ایجاد یک لیست کشویی از انتخاب‌های معتبر برای یک سلول استفاده کند. با این حال، آنچه او در واقع به آن نیاز دارد پیچیده تر است. او تعداد زیادی نام اقلام با کدهای مورد مرتبط دارد. در سلول B2 او می تواند یک لیست اعتبار سنجی داده ایجاد کند که نام همه موارد (همزن، موتور، پمپ، مخزن و غیره) را نشان می دهد. سپس کاربر می تواند یکی از این موارد را انتخاب کند. با این حال، وقتی او به سلول B2 در جای دیگری ارجاع می دهد، می خواهد کد مورد - نه نام مورد - توسط مرجع برگردانده شود. بنابراین، مرجع به جای همزن، موتور، پمپ، مخزن و غیره، A، M، P، TK و غیره را برمی گرداند.
هیچ راه مستقیمی برای انجام این کار در اکسل وجود ندارد. دلیل آن این است که لیست های اعتبارسنجی داده ها به گونه ای تنظیم شده اند که فقط یک لیست تک بعدی از موارد را شامل شود. این باعث می‌شود فهرست نام آیتم‌های شما را آسان کند. با این حال، می‌توانید نحوه استفاده از فهرست اعتبارسنجی داده‌ها را کمی گسترش دهید تا به آنچه می‌خواهید برسید. این مراحل را دنبال کنید:
شکل 1. کادر محاوره ای New Name.
شکل 2. کادر محاوره ای Data Validation.
- جایی در سمت راست داده های کاربرگ خود، یک جدول داده تنظیم کنید. این جدول شامل نام اقلام شما و در سمت راست نام هر مورد، کد مورد مرتبط با آن نام خواهد بود.
- سلول های حاوی نام آیتم های شما را انتخاب کنید. (کد اقلام را انتخاب نکنید، فقط نام ها را انتخاب کنید.)
- تب Formulas روبان را نمایش دهید.
- روی ابزار Define Name در گروه Defined Names کلیک کنید. اکسل کادر محاوره ای New Name را نمایش می دهد. (شکل 1 را ببینید.)
- در کادر Name یک نام توصیفی مانند ItemNames وارد کنید.
- روی OK کلیک کنید تا نام را اضافه کنید و کادر محاوره ای را ببندید.
- سلول B2 (سلولی که در آن لیست اعتبارسنجی خود را می خواهید) انتخاب کنید.
- تب Data روبان را نمایش دهید.
- بر روی ابزار Data Validation در گروه Data Tools کلیک کنید. اکسل کادر محاوره ای Data Validation را نمایش می دهد. (شکل 2 را ببینید.)
- با استفاده از لیست کشویی Allow، لیست را انتخاب کنید.
- در کادر منبع، یک علامت مساوی به دنبال نامی که در مرحله 5 تعریف کردید (مانند =ItemNames ) وارد کنید.
- روی OK کلیک کنید.
با انجام این مراحل، افراد همچنان می توانند از لیست کشویی اعتبارسنجی داده برای انتخاب نام آیتم های معتبر استفاده کنند. کاری که اکنون باید انجام دهید ارجاع به کد مورد از جدول داده ای است که در مرحله 1 تنظیم کرده اید. می توانید این کار را با فرمولی مانند زیر انجام دهید:
=VLOOKUP(B2,OFFSET(Itemlist,0,0,,2),2,FALSE)
این فرمول می تواند به تنهایی مورد استفاده قرار گیرد (برای قرار دادن کد مورد مورد نظر در یک سلول) یا می تواند در یک فرمول بزرگتر استفاده شود، هر جایی که در ابتدا به B2 اشاره می کردید.
اگر به دلایلی نمی توانید یک جدول داده برای نام و کد اقلام خود ایجاد کنید، می توانید با ایجاد یک فرمول آرایه به مشکل نزدیک شوید:
=INDEX({"A","M","P","TK"},MATCH(B2,{"agitator","motor","pump","tank"},0))
مانند تمام فرمول‌های آرایه، این فرمول را با فشار دادن Ctrl+Shift+Enter وارد می‌کنید . بزرگ‌ترین ایراد آن این است که به‌روز نگه‌داشتن فرمول به‌سرعت دشوار می‌شود و «محدودیت دوام» در مورد تعداد جفت کدها و مواردی که می‌توانید در فرمول قرار دهید وجود دارد. (محدودیت با طول فرمول تعیین می‌شود، بنابراین به طول نام آیتم‌های شما بستگی دارد.) همچنین، این رویکرد خوب است که فقط کد مورد را در سلول دیگری برگرداند، نه اینکه آن را به عنوان بخشی از یک فرمول بزرگتر شامل شود.
