Forum Discussion

Jimmyolo96's avatar
Jimmyolo96
Copper Contributor
Dec 01, 2023

Using a value in a cell as a sheet name in a function

Hi,

 

I have a situation where i want to use a value in a cell (Cost Code - column F) as a sheet number in my function.

In this example, i have sum + filter function where i am trying to sum all items in the sheet - 09 that matches the reference (20016.01 - Cell A1).

This is the equation i came up with -> =SUM(FILTER('09'!H6:H58,'09'!A6:A58=A6))

 

Instead of manually inputting '09', how can i do this automatically? 

 

Any suggestions will be greatly appreciated.

 

thank you.

Jimmy

 

 

 

 

 

 

  • Hi Jimmyolo96 

     

    You have to use the INDIRECT function:

    in E6 or wherever you want:

    =SUM(
      FILTER(
        INDIRECT("'" & F6 & "'! H6:H15"),
        INDIRECT("'" & F6 & "'! A6:A15") = A6
      )
    )

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    Hi Jimmyolo96 

     

    You have to use the INDIRECT function:

    in E6 or wherever you want:

    =SUM(
      FILTER(
        INDIRECT("'" & F6 & "'! H6:H15"),
        INDIRECT("'" & F6 & "'! A6:A15") = A6
      )
    )

     

Resources