# Calc: How to drag Cell Content until very last Row?

Assumed we have a very long data series in sheet 1:

``````         A     B     C
1   Bla
2   Bla
3   Bla
4   Bla
5   .
6   .
7   .
75000   Blub
``````

In sheet 2, I want to reference/clone this data from sheet 1, but without manually extending/dragging the cell (by holding the left mouse pointer on cell A1 and scroll until row no. 75000):

``````        A                 B                C
1   =\$Sheet1.A1
2   =\$Sheet1.A2
3   =\$Sheet1.A3
4   =\$Sheet1.A4
5   .
6   .
7   .
75000   =\$Sheet1.A75000
``````

Is there an option to extend the cell values to row no. 75000 without needing to scroll down the whole table manually?

This is extremely time consuming on such huge tables, why I want to avoid it.

Hello,

• Enter `=\$Sheet1.A1` in cell `A1` of `Sheet2` (keep cell selected)
• Go to Name Box
• Type `A1:A75000` into the name box and press `ENTER` (75000 cells get selected)
• Type `CTRL+D`

Hope that helps.

1 Like

Thank you! `Ctrl+D` was the solution.

Itâ€™s not hard. Enter the formula in A1 and press Enter. If the option Press Enter to move selection is set to Down (default setting), cell A2 is now highlighted. Press Shift + Up Arrow. The range A1:A2 is now selected. This is what is written in the box at the top left - in the Sheet area box. Take a look at the Help at your leisure, there is a text like this:

So, press Ctrl+Shift+F5 or Ctrl+Shift+T, then End

now Backspace, type 75000 and press Enter.
Now press Ctrl+D

Thank you! `Ctrl+D` was the solution.

@Pantona Alternative. Go to Sheet1 and press Ctrl+* - the entire range from Bla-Bla-Bla will be selected. Copy it. Ctrl + PageDown to go to Sheet2, Ctrl+Shift+V to invoke a Special Paste, Alt+L to activate the Link option and Enter. Exactly as many formulas will be inserted as there is data in Sheet1

Whereas values are repeated.

(1) Copy source cell

(2) Use Name Box to indicate the location of the collage

(3) Enter

(4) Ctrl+V

## Use the formula:

=INDIRECT("\$Sheet1.A"&ROW()) in A1

Maybe easy with an array, which also is adapted automatically if you INSERT/DELETE row(s) in the referenced range. And also should generate a file with less size.

=Sheet1.A1:A75000