Search code examples
google-sheets

Getting the data from the new Google-sheet tab with INDIRECT

I am trying to have a Google-sheet formula which does following:

  1. There're cells A1 and B1 in the tab main-tab
  2. I add a string tab-name to A1 . The fromula in B1 goes to the Tab with the name tab-name
  3. In the tab-name the fromula picks the last value of a needed column from the bottom (let it be column H) and places it into B1 of main-tab.

I am blocked with part 2. I understand I need INDIRECT, but do not understand how to use it here. The problem I don't know the name of the tab preliminary and don't want to fix formula from below every time changing the tab name.

I've solved the part 3 with the following formula:

=FILTER( 'tab-name'!H2:H , ROW('tab-name'!H2:H) =MAX( FILTER( ArrayFormula(ROW('tab-name'!H2:H)) , NOT(ISBLANK('tab-name'!H2:H)))))

I understand I should somehow incorporate INDIRECT instead of tab-name, but I'm blocked here. Can someone advise here?

The value from another tab could be a text or a number. It depends.

Solution

  • You may try:

    =chooserows(tocol(indirect(A1&"!H:H"),1),-1)