Forum Discussion
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 down to row 26 is dived by the number in G28 so I have needed to use the $g$28 formula
when I copy the whole spec sheet again the formula stays on $g$28, how do I make it so that it changes to $g$61
cheers
- 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)
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).
You will have to enter this manually. However, it is sufficient if you do it in cell H40. For the range H41:H59 you can copy the formula from H40 into it.
By the way, you can leave out SUM() completely.
=I40/G$61- 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)- Hey Mtarler,
i used B, can't believe it was so simple after googling for a week, thanks for taking the time to answer this question
cheers
dave
