Liisaska hoos-u-dhaca ee Excel waa aalado awood leh. Waxay kuu ogolaanayaan inaad siiso isticmaalayaasha falaar hoos-hoos ah oo, marka la doorto, siisa liiska xulashooyinka.

Tani waxay yarayn kartaa khaladaadka galitaanka xogta sababtoo ah waxay ka ilaalinaysaa isticmaalayaasha inay si toos ah u qoraan jawaabaha. Excel xitaa wuxuu kuu ogolaanayaa inaad ka soo saarto shayada liisaska hoos u dhaca ee unugyo kala duwan.

Si kastaba ha ahaatee, halkaas kuma eka. Adigoo isticmaalaya qaar ka mid ah habab hal abuur leh si aad u habaynayso ansaxinta xogta unugyada hoos u dhaca, waxaad xitaa abuuri kartaa liisaska hoos u dhaca ee badan, oo isku xidhan, halkaasoo walxaha laga heli karo liiska labaad ay^{(available in a second list)} ku xidhan yihiin qaybta isticmaaluhu ku sameeyay liiska kowaad.

Maxay U Wanaagsan^(Good) Yihiin Liisaska^(Linked) Hoos-u-dhaca ee Dhowrka ah ee ku Xiran ?

Tixgeli in foomamka intarneedka intooda badani ay buuxiyaan liisaska hoos-u-dhaca labaad ee ku salaysan waxaad kaga jawaabto liiska hoos-u-dhaca ee ka horreeya. Tani waxay la macno tahay inaad ka dhigi karto xaashida galitaanka xogta Excel si la mid ah sida foomamka khadka tooska ah. Way is beddeli doontaa iyada oo ku saleysan jawaabaha isticmaalaha. 

Tusaale ahaan, aan sheegno inaad isticmaalayso xaashida Excel si aad macluumaadka kombiyuutarka uga ururiso isticmaalayaashaada u baahan dayactirka kombiyuutarka^{(computer repairs)} . 

Xulashooyinka gelitaanka ayaa laga yaabaa inay u ekaan karaan sidan:

• Qaybta Kombiyuutarka^{(Computer Part)} : Kormeere, Mouse , Kiiboodhka^(Keyboard) , Nidaamka Saldhigga^{(Base System)}
• Nooca Qaybta:
  • Korjoogteynta^(Monitor) : Dhalada, Guriyeynta^(Housing) , Xadhkaha Korontada^{(Power Cord)} , Elektarooniga Gudaha^{(Internal Electronics)}
  • Mouse : Wheel, Iftiinka LED^{(LED Light)} , Xadhkaha^(Cord) , Badhamada^(Buttons) , Dacas
  • Kiiboodhka^(Keyboard) : Furayaasha, Guriyeynta^(Housing) , Xubinta^(Membrane) , Xadhkaha^(Cord) , Elektarooniga Guddaha^{(Internal Electronics)}
  • Nidaamka Saldhigga^{(Base System)} : Daboolka , Badhamada^(Buttons) , Dekadaha^(Ports) , Korontada^(Power) , Electronics Gudaha^{(Internal Electronics)} , Nidaamka hawlgalka^{(Operating System)}

Sida aad ka arki karto geedkan, macluumaadka la heli karo si loo xusho "Part Type" waxay ku xiran tahay qaybta Computer^{(Computer Part)} -ka ee isticmaaluhu uu ka doorto liiska hoos u dhaca ee ugu horreeya.

Tusaalahan, xaashida faafintaada ayaa laga yaabaa inuu bilaabo inuu u ekaado sidan:

Haddii aad abuurto liisaska hoos u dhaca oo badan, isku xidhan, waxaad isticmaali kartaa shayga laga soo xushay liiska hoos u dhaca ee B1 si aad u wado waxa ku jira liiska hoos u dhaca ee B2.

Aan eegno sida aad tan u habayn karto. Sidoo kale, xor u noqo inaad soo dejiso xaashida tusaalaha Excel ee tusaalaha hoose.

Abuur xaashida Liistadaada^{(List Source Sheet)} Hoos-u-dhaca

Habka ugu nadiifsan ee lagu dejin karo wax sidan oo kale ah waa in la abuuro tab cusub oo Excel ah halkaas oo aad ku habeyn karto dhammaan shayada liiska hoos-u-dhaca.

Si aad u dejiso liisaska hoos-u-dhicida ee isku xidhan, samee miis ay madaxa sare ku yaalaan dhamaan qaybaha kombayutarka aad rabto in aad ku darto liiska hoos u dhaca ee hore. Dabadeed qor dhammaan shayada (noocyada qaybaha) ee ay tahay inay hoos maraan madaxaas.

Marka xigta, waxaad u baahan doontaa inaad doorato oo aad magacowdo tiro kasta si marka aad dejinayso Xaqiijinta Xogta^{(Data Validation)} hadhow, aad awood u yeelan doonto inaad doorato midka saxda ah.

Si taas loo sameeyo, dooro dhammaan shayada hoos yimaada tiir kasta iyo magaca la doortay waxay la mid yihiin madaxa. Si aad u magacowdo miis, waxaad si fudud ugu qortaa magaca goobta dhexdeeda tiirka “A”.

Tusaale ahaan, dooro unugyada A2 ilaa A5 , oo magaca inta u dhaxaysa "Monitor".

Ku soo celi habkan ilaa aad ka hesho dhammaan noocyada kala duwan ee si habboon loogu magacaabay. 

Si kale oo tan loo sameeyo waa iyadoo la isticmaalayo qaabka xulashada ee Excel's Create from Selection . Tani waxay kuu ogolaanaysaa inaad magacawdo dhammaan noocyada kala duwan sida habka gacanta ee sare, laakiin hal gujis ah.

Si tan loo sameeyo, kaliya dooro dhammaan safafka xaashida labaad ee aad abuurtay. Kadibna ka dooro Formulas ka menu-ka, oo dooro Create from Selection in the ribbon.

Daaqada pop-up ayaa soo bixi doonta. Hubi in kaliya safka sare^{(Top row)} la doortay ka dibna dooro OK .

Tani waxay isticmaali doontaa qiyamka madaxa ee safka sare si loo magacaabo mid kasta oo ka mid ah safafka hoostiisa. 

Deji Liiskaaga Hoose ee ugu horreeya

Hadda waa waqtigii aad dejin lahayd liisaska hoos u dhaca ee badan ee ku xidhan. Si tan loo sameeyo:

1. Ku laabo xaashida kowaad, dooro unugga banaan ee dhanka midig ee summada kowaad. Kadibna ka dooro Data menu-ka, oo dooro Data Validation in ribbon.

2. Daaqada Xaqiijinta Xogta^{(Data Validation)} ee furan, ka dooro List hoosta Allow , hoosta Source , dooro astaanta fallaadha kor. Tani waxay kuu ogolaan doontaa inaad doorato tirada unugyada aad rabto inaad u isticmaasho ilaha liiska hoos-u-dhaca.

3. Dooro xaashida labaad meesha aad ku dejisay liiska hoos-u-yaalka xogta isha, ka dibna dooro oo keliya goobaha madaxa. Kuwaas waxaa loo isticmaali doonaa in lagu buuxiyo liiska hoos u dhaca ee hore ee unugga aad dooratay.

4. Dooro falaarta hoose ee daaqada xulashada si aad u balaariso daaqada Xaqiijinta Xogta^{(Data Validation)} . Waxaad arki doontaa tirada aad dooratay oo hadda lagu soo bandhigay goobta Isha^(Source) . Dooro OK si aad u dhameysato.

5. Hadda, dib ugu noqo xaashida weyn, waxaad ogaan doontaa in liiska hoos u dhaca ee hore uu ka kooban yahay mid kasta oo ka mid ah meelaha madaxa ee xaashida labaad.

Hadda oo liiskaaga hoos u dhigista ee ugu horreeya uu dhammaaday, waa waqtigii aad abuuri lahayd liiskaaga hoos u dhaca ee ku xiran.

Deji Liiskaaga Hoose ee ugu horreeya

Dooro unugga labaad ee aad rabto inaad ku shubto liiska shayada iyadoo ku xiran waxa lagu doortay unugga koowaad.

Ku soo celi nidaamka sare si aad u furto daaqada Xaqiijinta Xogta . ^{(Data Validation)}Dooro Liiska ^(List)Allow hoos u-hoosaadka . Meesha Isha^(Source) ayaa ah waxa soo jiidi doona liiska iyada oo ku xidhan waxa lagu doortay liiska hoos u dhaca ee hore.

Si tan loo sameeyo, geli qaacidada soo socota:

=INDIRECT($B$1)

Sidee u shaqeysaa INDIRECT ?

Shaqadani waxay ka soo celinaysaa tixraaca Excel ansax ah (kiiskan ilaa xad), laga bilaabo xadhig qoraal ah. Xaaladdan oo kale, xadhigga qoraalku waa magaca cabbirka uu soo maray unugga koowaad ($ B$1). Markaa INNDIRECT waxay qaadataa magaca xadka ka dibna waxay bixisaa ansaxinta xogta hoos-u-u-dhigga ee cabbirka saxda ah ee la xidhiidha magacaas.

Fiiro gaar^(Note) ah: Haddii aad u habayso ansaxinta xogta hoos-u-dhacan labaad adiga oo aan qiimo ka dooran hoos-u-dhigga koowaad, waxaad arki doontaa farriin khalad ah. Waxaad dooran kartaa Haa^(Yes) si aad u illowdo khaladka oo aad u sii wado.

Hadda, tijaabi liisaska hoos u dhaca ee ku xidhan ee aadka u cusub. Isticmaal hoos-u-dhaca koowaad si aad u dooratid mid ka mid ah qaybaha kombiyuutarka. Markaad doorato hoos-u-dhaca labaad, waa inaad aragto liiska ku habboon qaybta kombayutarka. Kuwani waxay ahaayeen noocyada qaybta tiirka xaashida labaad ee^{(on the second sheet)} aad u buuxisay qaybtaas.

Isticmaalka Liisaska Hoos-u-dhaca ee Isku Xiran ee Badan ee ^{(Multiple Linked)}Excel

Sida aad arki karto, tani waa hab aad u fiican oo aad xaashiyahaaga faafinta uga dhigi karto mid firfircoon. Adiga oo buuxinaya liisaska hoos u dhaca ee soo socda si looga jawaabo waxay isticmaalayaashu ku doortaan unugyada kale^{(select in other cells)} , waxaad ka dhigi kartaa xaashida faafintaada inay aad uga jawaabaan isticmaalayaasha, xogtana aad uga faa'iido badan.

Ku ciyaar talooyinka sare oo arag nooca liisaska hoos-hoosaadka ee ku xidhan ee xiisaha leh ee aad ka dhex abuuri karto xaashiyahaaga. La wadaag^(Share) qaar ka mid ah talooyinkaaga xiisaha leh ee qaybta faallooyinka ee hoose.

How to Create Multiple Linked Drop Down Lists in Excel

Drоp-down lists in Excеl are powerful tools. They let you provide users with a drop-down arrow that, when selected, provides them with a list of choices.

This can reduce data-entry errors because it avoids users from having to type answers directly. Excel even lets you pull the items for those drop-down lists from a range of cells.

However, it doesn’t stop there. Using some creative ways to configure data validation for drop-down cells, you can even create multiple, linked drop-down lists, where the items that are available in a second list depend on the section the user made in the first list.

What Are Multiple Linked Drop-Down Lists Good For?

Consider that most forms online fill out secondary drop-down lists based on what you answer in the drop-down list before it. This means you can make your Excel data-entry sheets just as advanced as online forms. It will modify itself based on the user’s answers. 

For example, let’s say you’re using an Excel spreadsheet to collect computer information from your users who need computer repairs. 

The entry options might look like this:

• Computer Part: Monitor, Mouse, Keyboard, Base System
• Part Type:
  • Monitor: Glass, Housing, Power Cord, Internal Electronics
  • Mouse: Wheel, LED Light, Cord, Buttons, Casing
  • Keyboard: Keys, Housing, Membrane, Cord, Internal Electronics
  • Base System: Casing, Buttons, Ports, Power, Internal Electronics, Operating System

As you can see from this tree, the information that should be available to select for “Part Type” depends on which Computer Part the user selects in the first dropdown list.

In this example, your spreadsheet may start out looking something like this:

If you create multiple, linked drop-down lists, you can use the item selected from the drop-down list in B1 to drive the contents of the drop-down list in B2.

Let’s take a look at how you can set this up. Also, feel free to download our example Excel sheet with the example below.

Create Your Drop-Down List Source Sheet

The cleanest way to set something like this up is to create a new tab in Excel where you can configure all of your drop-down list items.

To set up these linked drop-down lists, create a table where the header at the top are all of the computer parts you want to include in the first dropdown list. Then list all of the items (part types) that should go under that header.

Next, you’ll want to select and name each range so that when you’re setting up Data Validation later, you’ll be able to select the correct one.

To do this, select all of the items under each column and name that selected range the same as the header. To name a table, you simply type the name in the field over the “A” column.

For example, select cells A2 through A5, and name that range “Monitor”.

Repeat this process until you have all of the ranges named appropriately. 

An alternative way to do this is using Excel’s Create from Selection feature. This lets you name all of the ranges like the manual process above, but with a single click.

To do this, just select all of the ranges in the second sheet that you created. Then select Formulas from the menu, and select Create from Selection in the ribbon.

A pop-up window will appear. Make sure only Top row is selected and then select OK.

This will use the header values in the top row to name each of the ranges underneath it. 

Set Up Your First Drop Down List

Now it’s time to set up your multiple, linked drop-down lists. To do this:

1. Back on the first sheet, select the blank cell to the right of the first label. Then select Data from the menu, and select Data Validation in the ribbon.

2. In the Data Validation window that opens, select List under Allow, and under Source, select the up arrow icon. This will let you select the range of cells you want to use as the source for this drop-down list.

3. Select the second sheet where you set up the drop-down list source data, and then select only the header fields. These will be used to fill out the initial drop-down list in the cell you’ve selected.

4. Select the down arrow in the selection window to expand the Data Validation window. You’ll see the range you’ve selected now displayed in the Source field. Select OK to finish.

5. Now, back on the main sheet, you’ll notice that the first drop-down list contains each of the header fields from the second sheet.

Now that your first drop-down list is finished, it’s time to create your next, linked drop-down list.

Set Up Your First Drop Down List

Select the second cell that you want to load list items for depending on what’s selected in the first cell.

Repeat the process above to open the Data Validation window. Select List in the Allow drop-down. The Source field is what will pull in list items depending on what’s selected in the first drop-down list.

To do this, enter the following formula:

=INDIRECT($B$1)

How does the INDIRECT function work?

This function returns a valid Excel reference (in this case to a range), from a text string. In this case, the text string is the name of the range passed by the first cell ($B$1). So INDIRECT takes the name of the range and then provides the drop-down data validation with the correct range associated with that name.

Note: If you configure the data validation for this second drop-down without selecting a value from the first drop-down, you’ll see an error message. You can select Yes to ignore the error and continue.

Now, test your new multiple, linked drop-down lists. Use the first drop-down to select one of the computer parts. When you select the second drop-down, you should see the appropriate list items for that computer part. These were the part types in the column on the second sheet that you filled in for that part.

Using Multiple Linked Drop-Down Lists in Excel

As you can see, this is a very cool way to make your spreadsheets much more dynamic. By filling in subsequent drop-down lists in response to what users select in other cells, you can make your spreadsheets far more responsive to users, and the data far more useful.

Play around with the tips above and see what kind of interesting linked drop-down lists you can create in your spreadsheets. Share some of your own interesting tips in the comments section below.

Related posts

• Sida Dhakhso Loo Geliyo Safaf Badan gudaha Excel

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida Taariikh ahaan loogu kala saaro Excel

• Isticmaal kiiboodhka si aad u bedesho dhererka safka iyo ballaca tiirka gudaha Excel

• Sida Loo Sameeyo Shaxda Pivot Fudud ee Excel

• Farqiga u dhexeeya Microsoft Excel Online iyo Excel ee Desktop

• Sida loo Isticmaalo Tixraacyada Absolute ee Excel

• Sida Loo Raadiyo Excel

• Sida Loo Dhaqaajiyo Columns gudaha Excel

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• Sida Loo Mideeyo Dukumentiyada Kalmada Badan

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• Sida loo xisaabiyo Z-Score gudaha Excel

• Sida loo sameeyo shaxda Pie ee Excel

• Sida Loo Wadaago Faylka Excel Si Fudud Layskula Shaqeeyo