CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Bry
Bry

Reputation: 638

How to get the value from another sheet with variable sheet name using Google Sheets?

I need to get the sheet name from a cell.

On image below I'm trying to get '4/2'!$AB60.

  1. need to get the date which is 4/2 (cell D5)
  2. combine it with !$AB60
  3. now the cell D6 will have a formula value of '4/2'!$AB60
  4. it will display the value.

Is there a way to formulate this instead of what I currently be doing, manually updating the each column dates (from C6 to AF6)? Is this even possible?

enter image description here

Upvotes: 1

Views: 1435

Answers (1)

Wicket
Wicket

Reputation: 38131

Formula from the screenshot: =JOIN(,LEFT(D5,LEN(D5),!$AB60)

The problem with this formula is !$AB60 as it is not a valid value, instead use "!$AB60" or use =TO_TEXT(D5)&"!$AB60". To use the result of this formula as a cell reference use INDIRECT, i.e.:

=INDIRECT(TO_TEXT(D5)&"!$AB60")

Upvotes: 2

Related Questions