related cells
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
Answers
-
Hi @Gabo Flocco,
Can you show an example of an affected formula to help us work out the issue and a solution?
Thanks!
-
Yes, I can. I forgot to upload the image, sorry!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Ok, thank you! I guess I'll use like that.
Regards
Gaby
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives