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

[Pantona]

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?

---

With “scrolling down the whole table” I am talking about this feature here:

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

[anon73440385]

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.

[Pantona]

Thank you! Ctrl+D was the solution.

[JohnSUN]

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

[Pantona]

Thank you! Ctrl+D was the solution.

[JohnSUN]

@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

[schiavinatto]

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

[mariosv]

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
and instead [enter] use [Shift+Ctrl+Enter]
What is an Array?