Edit: This question is completely revised to incorporate screenshots as suggested by the commenters.
I track student grades in Excel using a spreadsheet something like this
I often find myself writing Excel formulas that use the same range over and over again:
And then, I want to add just a few more columns to the range, that is, to edit it to:
Then, I have to either drag each of the boxes one at a time, or edit each of the instances in the formula itself one at a time to get:
But is there a way to drag all of them at the same time? (From the answers, apparently not.) This becomes more important as the formulas become more complex, e.g:
This is a formula for dropping the lowest grade. I copy-paste this formula once for each row (for each student in a class). Then, if I want to add an assignment, I need to edit every single copy of the range AF5:AO5 to be AF5:AN5.
Is there a way to re-write this formula to only use the referenced range once? I prefer to use a single cell with no VBA for simplicity.
Research: I can't seem to find anything remotely related on Google. The closest I could find was this discussion of how to edit selections, not formulas.
@Michthan mentions named ranges with an offset, but that does not allow the same name to be used for multiple student records.