SOLVED

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

Copper Contributor

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

 

Jimmyolo96_1-1701409309065.png

 

 

 

 

 

3 Replies
best response confirmed by Jimmyolo96 (Copper Contributor)
Solution

Hi @Jimmyolo96 

 

You have to use the INDIRECT function:

Sample.png

in E6 or wherever you want:

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

 

it works! thank you so much!
You're welcome & Thanks for providing feedback
1 best response

Accepted Solutions
best response confirmed by Jimmyolo96 (Copper Contributor)
Solution

Hi @Jimmyolo96 

 

You have to use the INDIRECT function:

Sample.png

in E6 or wherever you want:

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

 

View solution in original post