Posts by CapG

Yes, when I opened in notepad the values are correctly shown. I'm opening it in excel since that excel.csv should be fed to another application and I cannot upload .txt which is the notepad version of the same.

Is it possible to prevent the conversion when opening in excel?

Not onscreen. Only after when the file is saved and closed as .csv. When I re-open the same file the formats are gone.

I have macro that creates a new workbook process the data and converts column N into text and saves in .csv format.

Now, if Column N has value=052880806 saved as text. After saving as .csv that value truncates leading 0(s) and shows in a scientific manner 5.29E+14.

While debugging I see the text format preserving until the file save line, after wb.Saveas book1.csv,XLCSV line the formats are gone.

I cannot have the excel saved in other formats except .csv because the file is getting loaded to another application which allows only .csv type of files. Is there a workaround?

Hi,

Thank you rory and RoyUK for reverting but I figured later that the formula should be locked like this $C1:$C50.

=$O2*Index('[List macro.xlsm]GFR'!C:C, Match($F2,'[List macro.xlsm]GFR'!A:A,0))

in VBA is not working.

Great Info!

Thank you so much

Quote from Fluff13

Try it like

=SUMIF(Sheet1!B2:B27,"=>7 Days", Sheet1!D2:D27)

Hi Fluff13,

I just gave it a try and it worked like a charm. Can you please help me understand, how did placing an = sign resolved the issue?

Thanks!

Neither of it is working. ">7 Days" is a text in B2:B27

In sheet2, Cells C5 and E5 the formula

=SumIf(Sheet1!B2:B27,">7 Days", Sheet1!D2:D27)

=SumIf(Sheet1!B2:B27,">30 Days", Sheet1!D2:D27)

is returning 0. Any idea why this is happening?

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

Quote from royUK

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