Hi,
In the attached sample file, I have two fields under the User Input tab; ID and SBU_Code. The user will enter a range of values under either of two fields(not both). Upon refreshing, it should dynamically filter the data in query1 and query2 to match the entered ID or SBU_Code.
Currently, I have used List.Contains() to filter just one field list(ID) at a time. I need to add SBU_Code using OR condition or something so that it satisfies the above requirement.
Thank you!
This is what I needed.
I apologize for the inadequate data set.I have revised my attachments here. The question remains as is.
If I enter SBUID as 6,7,8 and hit refresh all, it should fetch relevant records in Query 1 and Query 2 tabs of all ID(s)- 6,7,8
Right now it is taking only one parameter
Since the data is an external source connected to a corporate database to which only I have access and I am not supposed to reveal sensitive info that will be in M code.
So I deleted the database connection and saved two tables in two workbooks(Query 1 and Query 2) and imported them as workbooks to this Test-Pram file. Does, it helps now?
Here you go!
Sheet 1 has the screenshots attached.Hope it helps
Hi,
How to create a parameter query that takes multiple values?
I have 2 query tabs(PQ) initially eventually it will be 3 tabs(different views- detailed and specific) that are embedded with SQL code. In the parameter tab, I have the parameter query table that only takes one value for now. When the value is entered under the Value field and refreshed it should fetch one or more ID details in the query tabs.
Adding a slicer would not be much of help here as a slicer is applied per table/pivot. Correct me if I'm wrong.
Is there a workaround?
Thanks
Why have you got three data sheets?
Three data tabs are for three different views(Summary, detailed and specify)
To rephrase my question:
I have three data tabs connected to a database through SQL with three different views which depict the description of an item whose ID(s) is/are entered by the user in the input tab(the user can only enter in one of the fields; either SKU or Web ID or DVCS).
The input can only be one of the following:
i. One or a List of SKUIDs
OR
ii. One or a List of WEB_IDs :
OR
iii. One or a List of D-V-S-Cs
So whenever the above input changes the data tabs should be refreshed automatically with the SQL code I have, to retrieve records of those ID(s).
In the attached sample file I have only given one ID for the SKU field as an example and its corresponding data points are shown in the other three tabs.
What would be the approach?
I have three data tabs that are connected to the same database but are three different tables and there is another tab-input tab that records the following that should be connected to three data tabs.
The input can be only one of the following (it cannot be a combination of multiple) :
i. One or a List of A : Example : 24148992,24148978,24148953
ii. One or a List of B : 28-3336-9965-75,644-0086-3476-54
iii. One or a List of C : P000629440,P000639254
So whenever the above input changes the data tabs should be refreshed automatically.
Hi
I am trying to create a hyperlink to a file since it is very large to send as an attachment. But the path containing spaces is truncated and the hyperlink stops working in the email body
There is no target cell per say. Because this in the middle of execution the control shifts from primary code to template code.
Primary code here is a loop having to split one workbook to multiple workbooks and
each workbook should have incorporated change event macro in it. The template file is used for this purpose.
below is the primary code, after the line highlighted in yellow executes, the control transfers to template cod
this code is in a template file so the new workbooks that are created(in a loop) goes through this template file to incorporate event handler in every workbook that it undergoes. It didnt occur for other workbook though. Do I have to convert r to range in global declaration?
This is what it looks like while stepping into the template code:
First these two lines were stepped into without error
worksheetChange sub exists since Target.countlarge>1 next the control goes to selectionChange sub. See below screenshot where the overflow error occurs when I hover over r it displays=> r=Empty
Sure I will get back to you
Understood, I will look into it.
Thanks!
I also used the same code to overcome the error, it worked. Thanks Roy for your help.
But out of curiosity just wanted to know why did that overflow error occurred only for one file
this code is in a template file so the new workbooks that are created(in a loop) goes through this template file to incorporate event handler in every workbook that it undergoes. It didnt occur for other workbook though. Do I have to convert r to range in global declaration?
Like I said the error appears only for one file out of 40 files.
Yes I agree r cannot be range.
I tried setting r=target.value(r declared as variant globally) it throws same error saying "Overflow".
BUT i havent tried declaring r as string explicitly as I thought variant would implicitly convert to particular datatype.
To my understanding the purpose of r in selection change event is to- when cell value in in range BM-BO is deleted the deletion should reflect in the corresponding row having that header(BM-BO has a data validation list).
Now when I have used SET r= target this functionality is not working.
Thanks Carim SET seemingly worked! I need to understand and restructure the code.
this code is in a template file so the new workbooks that are created(in a loop) goes through this template file to incorporate event handler in every workbook that it undergoes. It didnt occur for other workbook though. Do I have to convert r to range in global declaration?