SOLVED
Home

INDEX MATCH DATA VALIDATION and Growing Table

%3CLINGO-SUB%20id%3D%22lingo-sub-313761%22%20slang%3D%22en-US%22%3EINDEX%20MATCH%20DATA%20VALIDATION%20and%20Growing%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313761%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20that%20contains%20a%20list%20of%20information%20that%20I%20want%20to%20use%20to%20fill%20two%20columns%26nbsp%3Bin%20another%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable1%20has%20headers%20that%20I'll%20use%20in%20the%20first%20data%20validation%20list%3B%3CBR%20%2F%3EEvery%20header%20contains%20information%20that%20will%20be%20used%20on%20the%20second%20data%20validation%20list%3B%3C%2FP%3E%3CP%3EThis%20second%20data%20validation%20list%20depends%20on%20the%20first%20one%20value%20to%20bring%20possible%20entries%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20created%20specific%20names%20on%20Name%20Manager%20to%20make%20it%20easier%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEverything%20is%20working%20fine%2C%20except%20for%20the%20fact%20that%20the%20new%20rows%20I%20add%20to%20the%20table%2C%20will%20have%20a%20reference%20for%20the%20first%20row%20instead%20so%3B%3C%2FP%3E%3CP%3ENo%20matter%20the%20value%20of%20the%20row%2C%20it%20will%20always%20use%20the%201st%20row%20as%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%3F%20%3AD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-313761%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314473%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20DATA%20VALIDATION%20and%20Growing%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314473%22%20slang%3D%22en-US%22%3EI'll%20be%20checking%20if%20it%20works!%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you!%20%3A)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313817%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20DATA%20VALIDATION%20and%20Growing%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313817%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Carlos%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20that's%20since%20your%20names%20were%20not%20updated%20when%20you%20modified%20first%20table.%20Instead%20of%20names%20I'd%20suggest%20to%20use%20references%20on%20tables%3C%2FP%3E%0A%3CPRE%3E%3DINDIRECT(%22Subcategory%5B%23Headers%5D%22)%0A%0Aand%0A%0A%3DINDIRECT(%22Subcategory%5B%22%20%26amp%3B%20INDIRECT(%22Transactions%5B%40Category%5D%22)%20%26amp%3B%22%5D%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-614111%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20DATA%20VALIDATION%20and%20Growing%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-614111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F264021%22%20target%3D%22_blank%22%3E%40Carlos_Ney_Filho%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20did%20it%20work%20for%20you%3F%20I%20am%20also%20facing%20similar%20problem%20and%20unfortunately%20i%20am%20not%20that%20proficient%20and%20expert%20in%20Excel%20as%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20share%2C%20how%20you%20handled%20the%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-650299%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20DATA%20VALIDATION%20and%20Growing%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650299%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344672%22%20target%3D%22_blank%22%3E%40ThirsT_2_Learn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20more%20or%20less.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20to%20make%20a%20workaround%20and%20use%20it%20in%20a%20simple%20way.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EBut%20the%20solution%20above%20was%20my%20guide.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Carlos_Ney_Filho
Occasional Contributor

I have a table that contains a list of information that I want to use to fill two columns in another table.

 

Table1 has headers that I'll use in the first data validation list;
Every header contains information that will be used on the second data validation list;

This second data validation list depends on the first one value to bring possible entries;

 

I've created specific names on Name Manager to make it easier;

 

Everything is working fine, except for the fact that the new rows I add to the table, will have a reference for the first row instead so;

No matter the value of the row, it will always use the 1st row as reference.

 

Can someone help me? :D

 

 

 

4 Replies
Solution

Hi Carlos,

 

I guess that's since your names were not updated when you modified first table. Instead of names I'd suggest to use references on tables

=INDIRECT("Subcategory[#Headers]")

and

=INDIRECT("Subcategory[" & INDIRECT("Transactions[@Category]") &"]")
I'll be checking if it works!

Thank you! :)

@Carlos_Ney_Filho 

 

Hi, did it work for you? I am also facing similar problem and unfortunately i am not that proficient and expert in Excel as you.

 

Could you please share, how you handled the problem?

@ThirsT_2_Learn 

 

Hi, more or less.

 

I had to make a workaround and use it in a simple way.


But the solution above was my guide.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies