Adding a column to Google Sheet changes the output of a cell in a different sheet without intending to

Jetninja 0
Viewed 23

I have a Google Spreadsheet. It has three sheets in it: Adoption Centers, Cats, and Rescue Pets.

Rescue Pets is animal adoption center. Animals of different kinds are housed in it. Rows in 'Rescue Pets' provide data on these animals. Row 1 (A1:F1) looks like:

Mater / 9 years old / Siamese / is good with kids / FALSE / TRUE

where "/" represents a break in column, with formatting written as:

  • NAME - Column A
  • AGE - Column B
  • SPECIES - Column C
  • NOTES - Column D
  • DOG? - Column E
  • CAT? - Column F

The 'Adoption Centers' sheet provides a list of all the adoption centers used in this analysis. The text "Rescue Pets" is in cell A1 of the 'Adoption Centers' list.

Back to the 'Rescue Pets' sheet. Row 2 (A2:F2) of the 'Rescue Pets' sheet contains:

Hunter / 2 years old / corgi / can't drink milk / TRUE / FALSE.

Row 3 (A3:F3) of the 'Rescue Pets' sheet:

Casper / 4 years old / tabby / previous liver disease / FALSE / TRUE.

Now, the 'Cats' sheet filters data from the rows in the 'Rescue Pets' sheet with a TRUE boolean in the CAT column. I do this with the following line (written in cell A1 of the 'Cats' sheet):

=FILTER(INDIRECT('Adoption Centers'!A1&"!A1:D3"),INDIRECT('Adoption Centers'!A1&"!F1:F3")=True)

The Problem: I want to add a new column of data (Adoption Cost) to the 'Rescue Pets' sheet. I want to place this column between Column C (Species) and Column D (Notes) so that each row in the 'Rescue Pets' sheet now looks something like this:

Row 1 (A1:G1): Mater / 9 years old / Siamese / $100 / is good with kids / FALSE / TRUE,

Row 2 (A2:G2): Hunter / 2 years old / corgi / $300 / can't drink milk / TRUE / FALSE,

Row 3 (A3:G3): Casper / 4 years old / tabby / $150 / previous liver disease / FALSE / TRUE.

The Question: is there any way to change

=FILTER(INDIRECT('Adoption Centers'!A1&"!A1:D3"),INDIRECT('Adoption Centers'!A1&"!F1:F3")=True)

from cell A1 of the 'Cats' sheet so that, without having to explicitly go in and edit the references made to D3 and F1:F3 respectively to E3 and G1:G3, it can still filter the 'Rescue Pets' sheet for all its data on adoptable cats?

offset filtering google-sheets google-apps-script excel
1 Answers

Your filter function is basically not built at all the way it should be (even though it works). It's referenceing a different sheet to then get a different sheet. It can be simplified considerably to just be:

=filter('Rescue Pets'!A:D,'Rescue Pets'!F:F=true)

TO account for the new costs column, you should be fine to INSERT a new column (not copy paste over your data) as it it will dynmically update. Or you could just rewrite your formula to expand the full area as shown below:

=filter('Rescue Pets'!A:E,'Rescue Pets'!F:F=true)
pgSystemTester0
Related