1

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
2
  • I added an example spreadsheet to the original question! Commented Mar 30, 2017 at 10:12
  • @dlanmaar I updated my response. I hope you will find it useful! Commented Mar 31, 2017 at 16:24
1

It would seem from the description that you need all non-empty values of this column. In this case perhaps this formula would give you what you want:

=UNIQUE('Sheet Name'!A$2:A)

or if you want not unique, but non-blanks simply

=FILTER('Sheet Name'!A$2:A, NOT(ISBLANK('Sheet Name'!A$2:A)))
Improve this answer
0

use the corner handle to drag it down and the formula would increase by 10 every row

=INDIRECT("Sheet1!A"&(ROW(A2)&0)-10)

0

=FILTER(Sheet1!A1:A, MOD(ROW(A1:A), 10)=0)

0

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.