How to keep formatted table column reference static when autofilling

[chrisjames25]

Hi all,

I have created a workbook with 2 sheets. the first sheet [Plugs] lists all of the plants that have been ordered for next year. The columns on this sheet include the supplier of the plants, the category of plant, the plant name and the week it is ordered for. For my query the delivered columns can be ignored.

On the second sheet [Summary] i have summarised what has been ordered from each plant category by total ordered for that category and total ordered by a particular supplier. My queries are (the second being the most important:

1. Is the following formula the best formula to use to give me a total number of plants ordered in a particular category and from a particular supplier:

=SUMPRODUCT(--(Plugs!C4:C104="Fuchsia")*(Table6[Supplier]="Delamore"),SUBTOTAL(9,OFFSET(Plugs!F4:F104,ROW(Plugs!F4:F104)-MIN(ROW(Plugs!F4:F104)),0,1)))

the formula works and found it on this forum but not sure if there is a simpler formula.

2. When i autofill rows across a spreadsheet, the (Table6[Supplier]="Hendriks") part of the formula automatically moves a column over each time i.e. (Table6[Category]="Hendriks") would be the first autfill change. I have stopped the reference to the Category of plant changing by adding $ in the relevant place (i.e $a4$4:$A$105 instead of A4:A105) but cannot figure out how to do the same when the reference is to a column in a formatted table. Had to manually go through and change each one back to [Supplier].

Any thoughts would be greatly appreciated.

Chris (Novice but trying to improve!!!!)

[Kevin UK]

Hi Chris

As you have Excel 2010 (Your Profile), you can use the SUMIFS function.

Please Login or Register  to view this content.

See attached, I have put formula on the Summary sheet, cell C48. Columns Absolute & Rows Relative.

Kevin

[Kevin UK]

Hi Chris

Thank you for the feed back. remember to mark the thread solved.

Kevin