related cells

Gabo Flocco

Good morning, I'm experiencing trouble with linked cells that don't adapt when I insert new cells or move existing ones. While editing my spreadsheet and its predecessors, certain formulas fail to adjust to the new positions of the initial cell. This issue is causing a lot of rework for me, and I don't fully understand why it's happening. Could it be that I'm referencing cells by their numerical positions? How can I link cells so that the formulas adjust along with any movements, such as the introduction of new rows?

Thanks!

Arq. Flocco

Nick Korna

Hi @Gabo Flocco,

Can you show an example of an affected formula to help us work out the issue and a solution?

Thanks!

Gabo Flocco

Yes, I can. I forgot to upload the image, sorry!

Genevieve P.

Hi @Gabo Flocco

The cell references in your formula for your ranges should stay with the first row you reference and the last row you reference.

This means if you drag the first row in your reference to a different place, your formula will update to look at that row still (based on the rowID), updating the number in your formula so it follows the correct row content.

For example, here I reference row 2 and 3:

But then I drag row 3 to be in a different place in the sheet. The formula follows that row because it's the last one in my range reference, and automatically changes the number to follow the row:

Newly inserted rows between the start reference and the end reference will be included in the range. Does this help explain what you're seeing?

Cheers,

Genevieve

Gabo Flocco

Hi Genevieve!

Actually I need to be able to multiply each cell of one list to another exact row position in another list. Is there any workaround to do this?

According to the image attached, is like A1xB1+A2xB2+A3xB3...to the infinit

Thanks!

Genevieve P.

Hi @Gabo Flocco

In this instance I would use a column formula to simply multiply one value by another:

=[Column One]@row * [Column Two]@row

This will give you the multiplied value for each row, always set to the current row using the @row function instead of a row number.

Then from this column you can do other calculations, such as using SUM to add together a group of cells. If you just need to see the entire column total, you can SUM the entire column:

=SUM([Formula Column]:[Formula Column])

Here's more information on cell and column references: Create a cell or column reference in a formula

Gabo Flocco

Ok, thank you! I guess I'll use like that.

Regards

Gaby