Google Sheets - stretch formula down column, but reuse input from same cell

Question

Let me start off by emphasizing that this is NOT an excel question. This is a Google Sheets question.

1. On line 19 I have entered a formula into the cell of a google sheets spreadsheet that looks like this:

   =(E19/B5)

2. I then drag/stretch down the formula to populate the cells below it in the same column.

3. Here is the issue. As I drag the cell down to populate the cells below it, the following cells should automatically populate as such:

   Line 20 should have the following formula: =(E20/B5)

   Line 21 should have the following formula: =(E21/B5)

   Line 22 should have the following formula: =(E22/B5)

   Line 23 should have the following formula: =(E23/B5)

   ...

Notice how a reference to B5 should be in each cell.

Instead, this is what I get. Please understand that this is what I do not want to happen:

Line 20 incorrectly has the formula: =(E20/B5)

Line 21 incorrectly has the formula::=(E21/B6)

Line 22 incorrectly has the formula:=(E22/B7)

Line 23 incorrectly has the formula:=(E23/B8)

...

To flog a horse, all formulas should contain the reference to B5. What formula do I need to enter so that it always references B5 as I drag/stretch the formula down the column?

Answer 1

You need to "lock" the row portion of your B5 reference with a dollar sign in the row-19 version:

=(E19/B$5)

However, since the formula is repetitious, you can construct it as a single array formula without the need to drag-copy it down. If you'd like to explore that approach, share a link to your sheet (or a copy of it) here. In the meantime, the above change to the formula will allow you to drag-copy without the B5 reference changing.