Drag Down Cell in Google Sheets

Topic

New Reply

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!

Reply | Quote

Replies

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.

WSJon5

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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.

WSJon5

Reply | Quote

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

Reply | Quote