Forum Discussion
Jimmyolo96
Dec 01, 2023Copper 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
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 ) )
- LorenzoSilver 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 ) )
- Jimmyolo96Copper Contributorit works! thank you so much!
- LorenzoSilver ContributorYou're welcome & Thanks for providing feedback