0

I have a spreadsheet with multiple tabs. An especific tab ("main_tab") gets data from another tab ("other_tab") using the INDIRECT function. I'm using INDIRECT function beacause I need that the reference keep unchanged every time I change the name of another tab.

As can be seen in the video, the data in "main_tab" changes only after I cut and paste in the same place the content of the cells (?!?!). It should change in the moment I change the content of the "another_tab".

The video above starts after I change the data and show the cut ("recortar" in portuguese) and paste ("colar" in portuguese).

https://www.youtube.com/watch?v=CBdoTGy7Iv0&feature=youtu.be

Any clues how to solve this, or it's just a Google Sheets bug?

player0
  • 124,011
  • 12
  • 67
  • 124
Bruno
  • 31
  • 2
  • 4
  • If you could write down the formulas you are using or sharing a link to a sample Google Sheet it would be easier to help out. – deags Oct 23 '19 at 17:13
  • Here is a copy of the spreadsheet: https://docs.google.com/spreadsheets/d/1T3j6ZoJTyEjRy3MKdMY68RABMnyQt-r2zP0rQFizwwE/edit?usp=sharing When I change the name of the tabs "Cha_XX_XX_2019" to "Chamados", it must change the values in "Auxiliar" tab. But the change only happens when i cut and paste the cells with the values. – Bruno Oct 25 '19 at 13:45

1 Answers1

1

This other question might serve you right from the start: References changing in Google Sheets with new forms submissions

You can also try to use the formula like an array. For example:

For direct reference:

=ArrayFormula(INDIRECT("'main_tab'!A:A"))

If you are using formulas:

=COUNTA(indirect("main_tab!I2:I"))

Or if you want nested formulas:

=COUNTIFS(indirect("main_tab!$AD2:$AD"),"data_A ",INDIRECT("other tab!$AA2:$AA"),"data_b")

For references and documentation:

-Here is the Indirect function official explanation from Google: NDIRECTO (INDIRECT)

-Here is a tutorial with examples: Google Sheets Indirect Function Examples and Usage

-And if you require ranges: Google Sheets: INDIRECT() with a Range

deags
  • 494
  • 1
  • 6
  • 21