• ## Drag Down Cell in Google Sheets

• This topic has 5 replies, 3 voices, and was last updated 1 month ago.

Tags:

Author
Topic
#2524716

Hi all,

I suspect the solution to what I want to do is simple, but I can’t figure it out, and Google searches didn’t help.

Take a look at the attached screenshot from one of my Google Sheets. I manually fill in column B; Google auto calculates column C. When I add a new row, I want to drag column C down to auto calculate the new cell. Simple, right?

Here’s the problem: Only 1 of the 2 things must change in each row. So, if I add a row below Microsoft, add \$2,500 for the amount, then drag 23% down, I get “#DIV/0!” That’s because Google wants the Microsoft “percentage” to be “=sum(B5/B9).”

But because I want to change only 1 thing, the formula should be “=sum(B5/B8).”

In other words: The B8 (the “total”) always stays the same; it’s the B5 (the “amount”) which changes.

Any idea how to make this happen? Thanks a ton!

Author
Replies
• #2524858

The B8 (the “total”) always stays the same; it’s the B5 (the “amount”) which changes.

Use a \$ ahead of the cell location you want stay put.

Solution: =sum(B5/\$B8)

###### 1 user thanked author for this post.
• #2535792

Hi @TechTango,

Many thanks for your reply! (Unfortunately, the Lounge didn’t notify me via email that you had done so; hence my delay).

Thanks also for your answer! With a small teak — to `=sum(B5/\$B\$8)` — this did the trick!

All the best,
J

• #2537567

Dear @TechTango,

Hi again. May I trouble you with a follow-up question?

In another spreadsheet, I’m trying to “drag down” the following formula:

`=sum(C16*'2022 Key'!B5)`

Do you know how I can tweak this formula, so that C16 becomes C17 but ‘2022 Key’!B5 stays the same?

Thanks so much!

• #2537580

To anchor a cell you prepend the dollar (\$) to either the row, column or both. This also applies to references to cells in other sheets.
You want to stop the row count increasing on a drag down, so anchor the row number.
`=sum(C16*'2022 Key'!B\$5)`

cheers, Paul

###### 1 user thanked author for this post.
• #2537599

Thanks so much, Paul. This did the trick, and I appreciate your help and expertise greatly!