Forum Discussion
appletonthecat
Dec 30, 2022Copper Contributor
Formula issue
Hey hoping someone can help with this I have attached an example of a problem I am having when I copy the SPEC sheet below the original one in the worksheet, column H starting at row 7 going d...
- Dec 30, 2022The 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)
Patrick2788
Dec 30, 2022Silver Contributor
This may work for you:
=LET(KegVol,FILTER(Vol,(Keg="Cocktails Per Keg")*(ISNUMBER(Vol))),k,COUNTIF($F$7:F7,"Cocktails Per Keg"),I7/INDEX(KegVol,IF(k=0,1,k)))
The SUM is not needed so I removed it. FILTER obtains all the numbers across from cells with "Cocktails Per Keg". The COUNTIF is determining how many times "Cocktails Per Keg" has appeared. If it's appeared once, the denominator is the first number obtained from FILTER, if it's appeared twice, pull the second, etc. I realize that phrase appears in spots where there are no numbers, but it works out even if the first set is an exception (the count is 0).