Google-sheets – avoid references to cells in other sheets from breaking if I remove/add a row

google sheets

I have a Spreadsheets that has two sheets. One takes entries and a second manipulates content from the first and displays results.

If I remove or add a row, the references in sheet two change, killing the desired content in that same sheet. If I delete row two from sheet one, what becomes row two in sheet one does not replace the content that was in sheet two, instead the cell breaks, referencing a non-existent or irrelevant cell.

I know what's happening. Instead of referencing a position relative to the sheet, it's referencing the actual cell but this not only does that not make sense, it doesn't work for my intended purpose.

Can I change this functionality or is there a better way to approach the issue?

Best Answer

Make sure to reference columns and not ranges:

Column reference:

=SUM(A:A) or =SUM(A30:A)

Explicit reference:

=SUM(A2:A30)

At most, you need to re-populate the formula's. Walk through the spreadsheet I prepared and see the formula's for yourself: file I prepare with example

Related Solutions

Google-sheets – Format a cell in bold if its row has other nonempty cells

You don't need a script for that; the built-in conditional formatting tools are flexible enough. Assuming the first row is for headers, apply conditional formatting to the range A2:A, with custom formula =len(join("", B2:Z2)). This means: join the content of cells, and find its length; the formatting applies if the length is greater than 0.

Replace Z by AZ or something else if you have more columns. (The formula will automatically adjust if you insert more columns between A and Z later.)

Bold formatting is found in the formatting dropdown, select "Custom" as below.

example

Google Sheets – How to Sort Data with Named Ranges Without Breaking References

You can't really have "variables" in a spreadsheet; it's not a programming language. The values are contained in cells and can be referred to by location, possibly relative to location of other cells. If cells get rearranged so that the relations within rows or columns are lost, that is already a problem.

In your example, you want to refer to Count of Bananas in a way that is independent of sorting. The function vlookup does this nicely:

=vlookup("Banana", A:B, 2, False)

This means: get the value in column B that's in the row with "Banana".

Of course, if you sort column B but not column A, this is going to break; but then the entire logic of Fruit/Count columns is broken.

Best Answer

Related Solutions

Google-sheets – Format a cell in bold if its row has other nonempty cells

Google Sheets – How to Sort Data with Named Ranges Without Breaking References

Related Topic