Sorry for the weird title. So to explain here is my very convoluted formula

=IF(D5="Fired","",If(K5=TRUE,'Data Storage'!C60*G5,If(C5="Owner",G5*'Data Storage'!C57,If(C5="Co Owner",G5*'Data Storage'!C57,If(C5="Manager",G5*'Data Storage'!C54,If(C5="DJ",G5*'Data Storage'!C51,G5*'Data Storage'!C48))))))

so I know that I can drag the black box in the bottom right corner of the cell and it will update for each cell selected, but that also updates the 'Data Storage'!CX cell which throws everything off. In the sheet Data Storage I store all my values to be used with the rest of the sheets. I'd like that to remain the same and just update the cells on the actual sheet I'm using.

All sheets are on the same google sheet. If anyone knows how to keep the 'Data Storage' cells the same and not have them updated by +1 like the other cells that would be amazing. Of course if you know a way to make my formula less convoluted I'd accept advice on that as well.

So basically I want the cells on the sheet I'm using to update, but not the one referencing the Data Storage sheet. so like one cell would look like this

If(K5=TRUE,'Data Storage'!C60*G5

then the next

If(K6=TRUE,'Data Storage'!C60*G6

and so on.

1 Answer 1


Place the symbol $ before 60, to make it like:

If(K5=TRUE,'Data Storage'!C$60*G5

This turns the reference into an absolute reference which doesn't change, so the next one will be:

If(K6=TRUE,'Data Storage'!C$60*G6

A less convoluted formula could be:

K5=TRUE,'Data Storage'!C$60*G5,
C5="Owner",'Data Storage'!C$57*G5,
C5="Co Owner",'Data Storage'!C$57*G5,
C5="Manager",'Data Storage'!C$54*G5,
C5="DJ",'Data Storage'!C$51*G5,
TRUE,'Data Storage'!C$48*G5

Note that it is possible to apply the arrayformula(), so to not have to drag down the black box, but it is necessary to modify the formula considering also the cases of empty cells.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.