I have a spreadsheet that will have values every 10 rows. I'd like to make reference to them but I'm having trouble doing this by clicking and dragging the corner handle to fill out the cells.
If I fill a cell with ='Sheet Name'!A$2
and the one below with ='Sheet Name'!A$12 I would expect to be able to select both, and use the corner handle to drag it down and the formula would increase by 10 every row.
This is not happening. Instead I get the same two rows repeated. Is it possible to increase the value of a formula when dragging it down? If not is there an alternative way of doing this?
Edit
Here's an example spreadsheet. I've had to make some changes, now the source cells may move down as users insert rows. So the cells might not be 10 rows apart, they could be more or less.
Best Answer
On Locking Ranges using "$"
The first problem I see is the
$sign is placed before the row. This tells the spreadsheet that it needs to LOCK that row (2 & 12) from changing numbers. The drag-handle operation relies on gsheets to find a pattern in your selection and repeat or continue it. If it doesn't find a pattern it repeats the elements you have selected. I tried what you were doing but with the dollar sign locking the column instead and Gsheets does not skip every 10 rows so I believe the handle-drag-operation just won't work for what you are trying to accomplish.The following only applies if there are exactly 10 rows difference between each reference ( I do NOT recommend this approach )
If you want to use the handle-drag operation try this:
=INDIRECT("Sheet4!$A"&(ROW(A1)*10-10+2))& in the cell directly beneath that one:=INDIRECT("Sheet4!$A"&(ROW(A2)*10-10+2))Select both of those cells and drag downwards. It will give you the desired effect.Keep only one set of Headers in Row-1 if you plan on doing Maths!
Although your spreadsheet is pleasing to look at it is not in a typical TABLE format. A better way to list your data is to have only one Header where the fields listed are: DATE, Booking-Time, Description Of Booking (leave blank if none), Choice 1, Choice 2, Choice 3, Price. Every Row after row one should be an instance of what your headers are describing ( in this case bookings ) where each field/Column defines the characteristics of that instance.
Here is a break down of the choices I made with your data:
The "Daily Totals" sheet should have 6 total columns/Fields: Date, Choice 1 Daily Sum, Choice 2 Daily Sum, Choice 3 daily Sum, Total Daily Sum, # of Bookings that day.
In "Daily Totals" Cell A1 you can have the Formula
=UNIQUE(Source!A2:A); replace A2:A with where your date column/Field is in sheet "Source" In "Choice 1 Daily Sum" I used the formula=SUM(FILTER(Source!D:D,$A2=Source!$A:$A)); Where D:D is the Choice1 column in sheet "Source", $A2 is the date column in sheet "Daily Totals," and $A:$A is the Date column in "Source"This formula can be copied down for each row corresponding to a new date, and right for each Column/Field ( Choice 2 Daily Sum, Choice 3 daily Sum, Total Daily Sum)
In "Daily Totals" Sheet for Column "# of Bookings that day" I used the formula
=COUNTA(FILTER(Source!C:C,$A2=Source!$A:$A)); Where C:C is The "Description Of Booking (leave blank if none)" column in the "Source" sheet, $A2 is the Date in the "Daily Totals" Sheet, & $A:$A is the Date in the "Source" sheet.đź”— Here is a copy of the sheet that you made with the adjustments that I have made.