0

I have a table with a list of manufacturers (Manufacturer 1, Manufacturer 2, Manufacturer 3). Then for each manufacturer listed in that table there is a corresponding table that list all the items that manufacturer offers. Those tables have multiple columns for different criteria but they all have a column named position. All those tables are named consistently with something at the beginning of the name, the manufacturer's name and the column name being position.

So the table and column name is something like this:

Metal_Supplier_Manufacturer_1[Position].

I want a formula that does what this formula does: =MAX(Metal_Supplier_Manufacturer_1[Position],Metal_Supplier_Manufacturer_2[Position],Metal_Supplier_Manufacturer_3[Position])

I want to return the highest number in the position column but I want it to look at the table of manufacturers to populate the names of the tables. Each manufacturer table has about 30-50 manufacturers listed and will keep growing so I don't want to keep coming back to this formula to update it when something gets added.

Edit: Sample I added a sample picture. I want to replace the formula in D1763 with one that does the same thing but looks at the Manufactures table to populate the array of table names, all just looking at the position column to return the max number. So then if I add a Manufacture 4 to the Manufactures table and a corresponding table consistently named like the others with a column named position (Metal_Supplier_Manufacturer_4[Position]), my formula would adjust and add the new table to the array.

I require a solution using only worksheet formulas.

4
  • I believe you want the =MAXIFS() function. – gns100 May 6 '20 at 21:55
  • For better understanding please edit you post & share some sample data, help us to fix the issue! – Rajesh Sinha May 7 '20 at 5:41
  • @gns100 I have excel 2010 so I don't have MAXIF function. Would assume MAX and IF could do the same thing but I'm unsure what to write that. – DevinBaker May 8 '20 at 12:35
  • @Rajesh I added a sample picture and some more explanation. – DevinBaker May 8 '20 at 12:41
0

I think it will work in excel 2010 but I can't test it:

enter image description here

there are various components (A2=$F$2:$F$5) tests if whatever in cell A1 matches with what is in the table in column F. Similar explanation for (B2=$G$2:$G$5). These parts of the formula normally return a TRUE or FALSE value, which is not directly helpful. However, adding the -- in front of it causes it to convert to 1 or 0. The last part is the value you are seeking (only works with numbers). By multiplying these three components together you are creating an array of values. The Max() grabs the largest value from the array.

Let me know if you nee help with my explanation.

0

Easiest way to do this is going to be to add a helper column to your manufacturer table (side note: I would really encourage you to spell 'manufacturer' correctly more consistently).

  1. Add a 'MAX' field to the Manufacturers table. Use INDIRECT to build a structured reference to get the MAX of the correspondingly named table. I named my tables "Mfr_Table[n]" for this demo because I didn't feel like typing out the names of your tables, so you'll update the strings in this formula:

=MAX(INDIRECT("Mfr_"&[@MANUFACTURERS]&"[Position]"))

But you can see how you use INDIRECT to combine literal strings with cell references. Here's how that looks in action:

enter image description here

The advantage here is that because it's in a Table, it automatically updates when you add rows to it, so once you've added Mfr_Table4, you just go to the next line (D8 in this example), add "Table4" and the new MAX is pulled in.

  1. The next part should now be obvious. Your overall MAX formula:

=MAX(Manufacturers[MAX])

enter image description here

1
  • Do you know of a way to do this with just a formula? It's a long explanation to fully explain but I can't add a max column to that table. There's other things looking at that table already that would cause problems if I added another column. I can get what I need by adding another table and using your method above but I was hoping there was a way to do it with just a formula. – DevinBaker May 9 '20 at 23:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.