Data Validation to Fill in Spreadsheet

Ebull

New Member
Joined
Feb 20, 2019
Messages
1
I am creating a battle simulator for a crypto-game that I play (Etheremon if anyone is familar). I am able to pull a list of my possible opponents and all of their "mons" and respective stats using VBA. It looks like this:

UsernameAvg BPAvg LevelOpp. AddressPlayer IDCatch NumberMonster IDType 1Type 2Gason Y/NAncestor 1Ancestor 2Ancestor 3Class IDTotal LevelExpHPPAPDSASDSPDTotal BP
forest gate609770xc674d5f701bbd8a79315cd2434af18c75816856e3701043672117FALSE80977913765669207514702470682275491522
forest gate609770xc674d5f701bbd8a79315cd2434af18c75816856e370938204146FALSE799725108854487302637647583591910578657
forest gate609770xc674d5f701bbd8a79315cd2434af18c75816856e370837995917FALSE977980157823414717612603859604603614649
forest gate609770xc674d5f701bbd8a79315cd2434af18c75816856e370258450541417FALSE241332791187769256786275
forest gate609770xc674d5f701bbd8a79315cd2434af18c75816856e3701284267719FALSE291816801156828275836974
forest gate609770xc674d5f701bbd8a79315cd2434af18c75816856e370166419496FALSE32813797116710387521027280
TONKA⇒638840xa9838c0f833d4e378fc937cc00d8b4080193815b240729654412FALSE252654929512048271662977972405684661726
TONKA⇒638840xa9838c0f833d4e378fc937cc00d8b4080193815b2402280961416FALSE64782421726555312560826812557603
TONKA⇒638840xa9838c0f833d4e378fc937cc00d8b4080193815b24027302141612FALSE312878100813266507579308583842596616587
TONKA⇒638840xa9838c0f833d4e378fc937cc00d8b4080193815b240242268694TRUE361174538930978771
TONKA⇒638840xa9838c0f833d4e378fc937cc00d8b4080193815b2405749299121415FALSE238242518661636774617466
TONKA⇒638840xa9838c0f833d4e378fc937cc00d8b4080193815b24093056412TRUE1101181459047788571
¯\_(ツ)_/¯557720x52e1319ecc564852b63ab200802c49b1d7140f93551242487146FALSE79972510853216923429446402404601378443
¯\_(ツ)_/¯557720x52e1319ecc564852b63ab200802c49b1d7140f935562909869FALSE32813798823281295585839609322594579588
¯\_(ツ)_/¯557720x52e1319ecc564852b63ab200802c49b1d7140f935563012956FALSE1427488833602117602580583861602612640
¯\_(ツ)_/¯557720x52e1319ecc564852b63ab200802c49b1d7140f935576488836TRUE961157256951788260
¯\_(ツ)_/¯557720x52e1319ecc564852b63ab200802c49b1d7140f9355853017856FALSE14427811167757692807176
¯\_(ツ)_/¯557720x52e1319ecc564852b63ab200802c49b1d7140f935546772320948FALSE2194271154775981667468
Mon complex569740x9a81d4da7d0147f0163e4ecf95f67678c40a26a5672842610138FALSE1411168262511037478279453651476464466
Mon complex569740x9a81d4da7d0147f0163e4ecf95f67678c40a26a5671236194188FALSE1542971802880461584561560817569578611
Mon complex569740x9a81d4da7d0147f0163e4ecf95f67678c40a26a56773393256FALSE1427488823311704590591579864583587632
Mon complex569740x9a81d4da7d0147f0163e4ecf95f67678c40a26a567801319516FALSE42118374563130709272
Mon complex569740x9a81d4da7d0147f0163e4ecf95f67678c40a26a5676518926FALSE144490919014167877491
Mon complex569740x9a81d4da7d0147f0163e4ecf95f67678c40a26a56766197118FALSE15113496087102568773
hagoromo590750x057dc43af6187a931e78a56b3293912a5b42e5ec324338900316FALSE33825141741670009549552537763540539580
hagoromo590750x057dc43af6187a931e78a56b3293912a5b42e5ec324841700617FALSE628782153711217180521526522747745537599
hagoromo590750x057dc43af6187a931e78a56b3293912a5b42e5ec32422301461612FALSE312878100823436479562333602846603604591
hagoromo590750x057dc43af6187a931e78a56b3293912a5b42e5ec3243647399138FALSE1411168213711577102129101105104
hagoromo590750x057dc43af6187a931e78a56b3293912a5b42e5ec32452418546TRUE96215679548043916869
hagoromo590750x057dc43af6187a931e78a56b3293912a5b42e5ec32433054517TRUE1122164644310245789170
Lemongrass709940x3c9c530d684a99cc39388c644f21ebeeb61c283970512651056FALSE1427488100197712577167076871012702711755
Lemongrass709940x3c9c530d684a99cc39388c644f21ebeeb61c283970522960416FALSE67885985710603916872334630630664
Lemongrass709940x3c9c530d684a99cc39388c644f21ebeeb61c283970523854424FALSE9097301339410589235667683654383936935709
Lemongrass709940x3c9c530d684a99cc39388c644f21ebeeb61c283970588544155548FALSE219427185402115183108175110183145
Lemongrass709940x3c9c530d684a99cc39388c644f21ebeeb61c2839705974533516TRUE741121511097015581156141118
Lemongrass709940x3c9c530d684a99cc39388c644f21ebeeb61c283970548449082TRUE1131155498836695859
Mushroom Man632830x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b5094291261612FALSE312878100875569853610349632901635649629
Mushroom Man632830x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b5091385431412FALSE31993213063536866678676473466285476509
Mushroom Man632830x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b509931911412FALSE2526549210018921829714######397703704758
Mushroom Man632830x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b509694702312TRUE1101164237146858362
Mushroom Man632830x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b5091144702414TRUE761167549325746062
Mushroom Man632830x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b5092574638412FALSE141255311169988564807778
ももちび0215452530x798fc047b8628533d70544a24a9ea37f92c88b64953714926427FALSE33830148218047213196192128123199175
ももちび0215452530x798fc047b8628533d70544a24a9ea37f92c88b64953739763617FALSE62878215369977652514523508717720525584
ももちび0215452530x798fc047b8628533d70544a24a9ea37f92c88b6495334037783FALSE897170154701062980523529733734536544599
ももちび0215452530x798fc047b8628533d70544a24a9ea37f92c88b649533044172138FALSE141116824383343345221354493346361353
ももちび0215452530x798fc047b8628533d70544a24a9ea37f92c88b6495346404328TRUE1091158328636978365
ももちび0215452530x798fc047b8628533d70544a24a9ea37f92c88b6495327307766TRUE961164519333878268

<colgroup><col><col><col><col><col><col><col><col><col span="5"><col span="2"><col><col span="3"><col><col><col><col></colgroup><tbody>
</tbody>


In another sheet I want to be able to have a dropdown menu where I can choose an opponent's username. For example, if I choose "forest gate" I want a table to be filled in a table like the one below. Mon 1 would be the first "forest gate" mon, Mon 2 the second, and so on: Table shown below.


Username ___
Mon 1Mon 2Mon 3Mon 4Mon 5Mon 6
Total LvlTotal LvlTotal LvlTotal LvlTotal LvlTotal Lvl
HPHPHPHPHPHP
PAPAPAPAPAPA
PDPDPDPDPDPD
SASASASASASA
SDSDSDSDSDSD
SPDSPDSPDSPDSPDSPD
Class IDClass IDClass IDClass IDClass IDClass ID
Type 1Type 1Type 1Type 1Type 1Type 1
Type 2Type 2Type 2Type 2Type 2Type 2
Gason Y/NGason Y/NGason Y/NGason Y/NGason Y/NGason Y/N

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Ideally I'd like for the drop down to not have duplicates. If I don't use a custom formula I'd see each username 6 times. I'd also like the drop down to be able to show any usernames that appear (not have trouble if VBA imports more than X number of opponents. I'm stuck on how to start this. Any help is greatly appreciated. Thanks in advance.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
You could use this formula option, assuming that your stats tab starts from A1;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Player Name</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">forest gate</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Player</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mon 1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mon 2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mon 3</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mon 4</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mon 5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mon 6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total Lvl</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">85</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">82</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">HP</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">514</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">637</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">612</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">87</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">56</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">67</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">702</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">647</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">603</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">76</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">82</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">103</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PD</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">470</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">583</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">859</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">92</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">82</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">87</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">SA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">682</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">591</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">604</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">56</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">75</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">52</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">SD</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">275</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">910</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">603</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">78</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">83</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">102</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">SPD</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">491</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">578</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">614</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">62</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">69</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">72</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Class ID</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">137</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">108</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">157</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">79</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">80</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">97</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Type 1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Type 2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Gason Y/N</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$O$2:$O$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B7</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$Q$2:$Q$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$R$2:$R$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$S$2:$S$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$T$2:$T$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B11</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$U$2:$U$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$V$2:$V$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B13</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$N$2:$N$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B14</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$H$2:$H$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B15</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$I$2:$I$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B16</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">Sheet2!$B$6:B6</font>)>COUNTIF(<font color="Red">Sheet1!$A$2:$A$49,Sheet2!$A$3</font>),"",INDEX(<font color="Red">Sheet1!$J$2:$J$49,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(<font color="Teal">Sheet1!$A$2:$A$49</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),COLUMNS(<font color="Purple">Sheet2!$B$6:B6</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,114,255
Messages
5,546,794
Members
410,758
Latest member
Papers
Top