Forum

Filter sheet name f...
 
Notifications
Clear all

Filter sheet name from Excel worksheet dynamically via parameter

5 Posts
2 Users
0 Reactions
117 Views
(@optimaoffice)
Posts: 17
Eminent Member
Topic starter
 

Hi,

I am using data from a worksheet to which I add a new tab every month. I am naming each new sheet with the name of the month in the format MMMYY, e.g. Dec22 for December 2022. I need to use data every month from this workbook, but from the sheet that corresponds to the current month. So i set up a parameter that every month returns the name of the month and then when I am pulling into the query the workbook with all the months, I'd like to automatically filter the sheet of the workbook that corresponds with the current month. This always returns an empty table. I assume it has something to do with the syntax. Can anyone help? Can I not use a parameter to filter the name of the sheet? Do I need to use a function?

I am uploading screenshots of the query and the steps. When I manually filter the sheet to "Nov22" it works. But when I use the parameter CurMoName (which is a single item list containing "Nov22") it does not return any results.

Thank you!

 
Posted : 09/01/2023 10:31 pm
(@optimaoffice)
Posts: 17
Eminent Member
Topic starter
 

The attachment might not have saved in my post. Here it is.

 
Posted : 09/01/2023 10:34 pm
(@catalinb)
Posts: 1937
Member Admin
 

Hi Dana,
"I am uploading screenshots of the query and the steps. When I manually filter the sheet to "Nov22" it works. But when I use the parameter CurMoName (which is a single item list containing "Nov22") it does not return any results."

Data type is essential, a list is not the same thing as a text string, you need to provide a text instead of a list. CurMoName{0} for example returns the first item in the list.
Instead of a parameter, you can use the system date:
DateTime.ToText(DateTime.LocalNow(),"MMMyy")

 
Posted : 10/01/2023 1:21 am
(@optimaoffice)
Posts: 17
Eminent Member
Topic starter
 

Hi Catalin,

Thank you for clarifying that aspect of list versus text. I updated the filter to CurMoName{0} and it worked. Very helpful hint. Appreciate the help.

Multumesc mult! 🙂
Dana

 
Posted : 11/01/2023 1:13 am
(@catalinb)
Posts: 1937
Member Admin
 

You're welcome, glad to hear you managed to solve it!

Cu placere 🙂
Catalin

 
Posted : 11/01/2023 2:22 am
Share: