SOLVED

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

Copper Contributor

# 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

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

# Re: Using a value in a cell as a sheet name in a function

You have to use the INDIRECT function:

in E6 or wherever you want:

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

# Re: Using a value in a cell as a sheet name in a function

it works! thank you so much!

# Re: Using a value in a cell as a sheet name in a function

You're welcome & Thanks for providing feedback
1 best response

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

# Re: Using a value in a cell as a sheet name in a function

You have to use the INDIRECT function:

in E6 or wherever you want:

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