google sheets
I want to drag a formula to copy it down a column, but the formula includes a reference to a box on a different page in the document i.e. '1' etc. I want the page number to increase by 1 for each new box I drag to.
When I drag, the page number stays the same, instead of changing/ increasing by 1.
As explained here when drag copy formula horizontally incrementation happens with anything related to columns. Nothing happens with rows. In order to get an increasing number one can use COLUMN() function in this scenario.
We get row number of original list with a calculation involving COLUMN() function, but then we have to create a string defining our original list's range and get the value in that cell. This is done by INDIRECT() function. (VLOOKUP method not good)
=INDIRECT("$B"&COLUMN()/2)
Now selecting ID and Machinery and dragging right fills all consecutive columns as desired.
Another alternative:
=INDEX($B:$B;COLUMN()/2)
=average(C:C)
should average the whole of ColumnC's numeric values, without difficulties over text or spaces in that column, while responsive to changes in ColumnC entries - with the proviso that the formula should not be in ColumnC.
Best Answer
Use
Row(1:1)instead of1. For example try inserting and dragging down:This could be extended to say:
where autofill may not give the desired results.