Formula issue

The whole point of the $ is to 'lock' that value in and NOT change. Here are few options:
a) don't copy down, copy to the right so Cocktails per KEG are on the same ROW for each case so then you use G$28 and then the column will move accordingly
b) create 1 template with every cell in column H set to G28 (i.e. not locked) and then when you copy the whole set all the cells in H will offset correctly
c) create a 'database' on 1 tab that has all the correct information organized in a table and then have a 'report' tab that has the fancy formatting and design you want and then you enter 1 or more values and the rest of the 'sheet' is populated based on lookup values from the 'database'
d) instead of $G$28 you can use XLOOKUP("Cocktails Per KEG", F9:F28, G9:G28)