SOLVED

Dynamic Lists

%3CLINGO-SUB%20id%3D%22lingo-sub-2318853%22%20slang%3D%22en-US%22%3EDynamic%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2318853%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20has%20all%20off%20the%20manufacturers%20we%20represent%20as%20row%20headings.%20So%20all%20manufacturers%20are%20listed%20in%20Column%20A.%26nbsp%3B%20All%20of%20the%20Customers%20we%20sell%20to%20are%20Column%20Heading.%26nbsp%3B%20Customers%20are%20listed%20in%20row%201.%26nbsp%3B%20Not%20all%20customers%20are%20available%20to%20sell%20to%20a%20manufacturer%2C%20so%20there%20is%20an%20%22X%22%20in%20the%20the%20cells%20at%20the%20intersection%20of%20available%20customers%20and%20manufacturers.%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Cust%201%26nbsp%3B%20%26nbsp%3B%20Cust%202%26nbsp%3B%20%26nbsp%3B%20Cust%203%26nbsp%3B%20%26nbsp%3BCust%204%3C%2FP%3E%3CP%3EManuf%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bx%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%3C%2FP%3E%3CP%3EManuf%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bx%3C%2FP%3E%3CP%3EManuf%203%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bx%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%3C%2FP%3E%3CP%3EManuf%204%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20select%20either%20a%20manufacturer%20or%20a%20customer%20and%20see%20a%20list%20of%20available%20Manufacturers%20if%20I%20choose%20a%20customer%20or%20a%20list%20of%20Customers%20if%20I%20select%20a%20Manufacturer.%26nbsp%3B%20It%20seems%20like%20a%20pivot%20table%20should%20work%20but%20I%20can't%20figure%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2318853%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2319600%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319600%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044005%22%20target%3D%22_blank%22%3E%40jimszabo4663%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20in%20which%20form%20you'd%20like%20to%20receive%20the%20data%2C%20as%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20597px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277654i0ABC50184DFAD27B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(Table1%5B%23Headers%5D%2CXLOOKUP(I4%2CTable1%5BM%2FC%5D%2CTable1)%3D%22x%22)%0A%0Aand%0A%0A%3DTRANSPOSE(FILTER(Table1%5BM%2FC%5D%2CXLOOKUP(I6%2CTable1%5B%23Headers%5D%2CTable1)%3D%22x%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2323434%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2323434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044005%22%20target%3D%22_blank%22%3E%40jimszabo4663%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20stay%20on%20the%20table%20on%20ribbon%20Table%20Design%20tab%20appears%2C%20name%20of%20the%20table%20is%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20700px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F278008iBAFADD6EC013BAA5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EM%2FC%20is%20just%20the%20name%20of%20the%20column.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20use%20range%20you%20may%20transform%20it%20into%20table%20by%20clicking%20Ctrl%2BL%20on%20any%20cell%20within%20range.%20Or%20select%20the%20range%20and%20Insert-%26gt%3BTable.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a spreadsheet that has all off the manufacturers we represent as row headings. So all manufacturers are listed in Column A.  All of the Customers we sell to are Column Heading.  Customers are listed in row 1.  Not all customers are available to sell to a manufacturer, so there is an "X" in the the cells at the intersection of available customers and manufacturers.

                Cust 1    Cust 2    Cust 3   Cust 4

Manuf 1                     x                          x

Manuf 2       x

Manuf 3       x            x            x            x

Manuf 4                                  x            x

 

I want to be able to select either a manufacturer or a customer and see a list of available Manufacturers if I choose a customer or a list of Customers if I select a Manufacturer.  It seems like a pivot table should work but I can't figure it out.

 

5 Replies

@jimszabo4663 

Not sure in which form you'd like to receive the data, as variant

image.png

with

=FILTER(Table1[#Headers],XLOOKUP(I4,Table1[M/C],Table1)="x")

and

=TRANSPOSE(FILTER(Table1[M/C],XLOOKUP(I6,Table1[#Headers],Table1)="x"))

@Sergei Baklan 

 

I just don't understand the Table1[M/C]  I assume it refers to manufacturers and customers but it's not named anywhere in the sheet and I can figure out how to use it when I copy formulas to my sheet.

best response confirmed by allyreckerman (Microsoft)
Solution

@jimszabo4663 

If you stay on the table on ribbon Table Design tab appears, name of the table is here

image.png

M/C is just the name of the column.

 

If you use range you may transform it into table by clicking Ctrl+L on any cell within range. Or select the range and Insert->Table.

 

Thank you, I did figure that out. Not sure how I missed the M/C column heading. I appreciate your help. It's working great.

@jimszabo4663 , glad it helped