May 03 2021 12:54 PM
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.
May 03 2021 03:55 PM
Not sure in which form you'd like to receive the data, as variant
with
=FILTER(Table1[#Headers],XLOOKUP(I4,Table1[M/C],Table1)="x")
and
=TRANSPOSE(FILTER(Table1[M/C],XLOOKUP(I6,Table1[#Headers],Table1)="x"))
May 04 2021 09:35 AM
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.
May 04 2021 12:50 PM
SolutionIf you stay on the table on ribbon Table Design tab appears, name of the table is here
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.
May 04 2021 01:04 PM
May 04 2021 12:50 PM
SolutionIf you stay on the table on ribbon Table Design tab appears, name of the table is here
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.