Posts by kumarma

Thanks Glenn,

Here is the new sample attached.

Dashboard Sheet:

Columns B to F --> These are the Quarterly numbers that are summed up based on the data query numbers in the range (G to AD)

Columns G to AD --> Month on Month numbers to be queried based on the drop down selection

Columns AE to AM --> These are the Quarterly numbers that are summed up based on the data query number in the range (G to AD)

Team 1 Sheet:

Exact same data range replicated in the Team1 to Team 9 (for sample I have put only 1).

However I need to display or query only these number rest I will calculate using sum or other formulas. Also there will be some calculation in the rows as well. I mean to say Row 1 should be queried by formula but Row 2 has a % calculation and based on the values from Row 1 and Row 2 I will calculate the value in the Row 3,

Row 4, 5,6 to be queried by formula.

Hope you can help on this.

Thanks in advance.

Regards

Ajay

Sorry for the delay in responding. Many Thanks Glenn it worked like a charm.

However the number of columns has increased that has to be queried. Earlier in the sample it was 10 columns. I have seen you have used a =check (indirect) function to check the number of columns that have to queried.

Current formula is returning only 10 column values. Please help in modifying the formula to query 24 columns.

guys any solution for this

Team, do we have any solution for this please?

Hi Guys,

Need help with the Data Query based on the drop down selection based on the dependent/independent drop down validation list

Sample File attached

I tried various formulas but was not able to get it working.

I cannot use INDIRECT because these drop down list are independent (or may be dependent)

I have a Metrics Table in my Consolidated Sheet and i have 10 teams (Team 1, Team 2...Team 9), 3 Environments (Production, Non Prod, No Field, All).

I have 10 other Sheets (Team 1, Team 2... Team 9). Each Sheet has same Metrics Table for All, Production, Non Prod, No Field. From Team 1 Sheet to Team 9 Sheet same format.

In the Consolidated, it is the same Metrics Table and format, however i have put 2 drop down Validation Lists. Independent Drop Downs (INDIRECT is not required) or what if that is made as related field and display only when the other Drop down is selected. This looks like a good one. So i will go with the dependent dropdowns

Requirement is, when i select the Team from the Team Drop Down List, and based on the selection from the dependent drop down list that is (Environment) it should pull the data from the respective teams sheet.

Not sure what is the best approach to get this working. Tried vlooks but was not able to achieve.

Hope there is a alternative to get this working.

Regards Ajay

Re: Macro to Auto update the formulas on a daily basis without opening Excel

Hi,

Any chance

Hi,

I have created a Day Wise Tracking Report.
Everyday my application will download a copy of the Raw data in a Shared File (Raw Data used for the Day Wise Report)
Raw Data has only 2 columns, KPI's (ColumnA) and count for that KPI (Column B)

In my Day Wise Tracking Report Column B lists the KPI which i want to track and i have put a INDEX MATCH to pull the information from the Exported Raw Data.

I have copied the same formula for all the dates.

I am stuck here, my application will download a copy with a file name and the same file will be Overwritten every day. (One Single file in the Shared Drive and it will be overwritten on a daily basis).

So is there a macro where we can make the macro to run at a particular time that will auto update the links (as i am using the INDEX MATCH to pull the information) and once done just change the Formulas to Values for that particular date.

Is it feasible to do all this without opening the file.

Need your help in resolving this.

Adding to the above i have added the total for every week, the macro should skip removing the SUM Total for the Week

Re: Macro to pull out multiple entries of same pattern in a cell is not working

Hi Pike,

Thanks, but it is not working. It says subscript out of Range.

As per the pattern I think the code needs to be modified

"*??##-###*" to "*??####-###*"

Total 8 characters and before - there are 4 characters

I have a worksheet (Sheet1) that contains about 10,000 cells in ColumnD full of textual data

Each Cell in Column D (From D2) contains lot of text with some special charecters in it and out of that i want to pull out a pattern like this ####-### where each # is a digit.

This pattern does not appear at a set place in each cell and there are multiple entries of this in each cell.

My requirement is to pull out a pattern and put it in Column E (If it is only 1 entry as per the pattern)

If there are multiple entries of the same pattern (the return value should be placed in the same row and subsequent columnns)

If it is a single entry i can use this formula but i have multiple entried in one cell. So i am using the below code but it is not working. Not sure what wrong i am doing.

=MID(D2,FIND("-",D2)-4,8)

Sub ExtractPattern()
    On Error Resume Next
    Set SourceSheet = ActiveSheet
    Set TargetSheet = ActiveWorkbook.Sheets("Results")
    If Err = 0 Then
        Worksheets("Results").Delete
    End If
    Worksheets.Add
    ActiveSheet.Name = "Results"
    Set TargetSheet = ActiveSheet
    Cells(1, 1).Value = "Found Codes"
    Cells(1, 1).Font.Bold = True
    iTargetRow = 2
    SourceSheet.Select
    Selection.SpecialCells(xlCellTypeLastCell).Select
    Range(Selection, Cells(1)).Select
    For Each c In Selection.Cells
        If c.Value Like "*####-###*" Then
            sRaw = c.Value
            iPos = InStr(sRaw, "-")
            Do While iPos > 0
                If iPos < 4 Then
                    sRaw = "  " & sRaw
                    iPos = iPos + 4
                End If
                sTemp = Mid(sRaw, iPos - 4, 8)
                sRaw = Mid(sRaw, iPos + 6, Len(sRaw))
                If sTemp Like "##-#####" Then
                    TargetSheet.Cells(iTargetRow, 1) = sTemp
                    iTargetRow = iTargetRow + 1
                Else
                    sRaw = Mid(sTemp, 4, 5) & sRaw
                End If
                iPos = InStr(sRaw, "-")
            Loop
        End If
    Next c
End Sub

D2 - MS09-062

D3 -
MS06-030
MS08-068
It should Return (MS06-030 in E3, MS08-068 in F3)

D4 -
MS09-037, MS08-048
It should Return (MS09-037 in E4, MS08-048 in F4)

D5-
MS08-033, MS09-028, and MS09-047

D6-
MS08-055, MS09-017, MS10-017, MS10-004, MS10-023, MS10-028, MS09-068, and MS09-027
It should Return (MS08-055 in E6, MS10-017 in F6, MS10-023 in G6,...........)

Hi Guys,

I have four a list of text that is in different format, i have consolidated that into 4 types of text.

The Column C has n number of records like this but if i have to categorize the type i can say that the lenght of the text is dynamic but the pattern is as shown below (4 patterns)

1) Auto Close [Sample Text] - (exp: OFF / curr: OFF)
2) Disable Test Test [Sample Test] Compliant
3) Test Agent Startup Test - (exp: AUTO / curr: AUTO) Compliant
4) Sample Test Data Startup Test Non-Compliant

My Requirement is

1) The text before the charecter ']' should be captured in Column D (D2) and the text within () to be captured in Column E (E2) but only curr: AUTO
it also can be curr: ON, curr: OFF, but irrespective of the text after 'curr:' and before ')' should be captured.

In this case i want to return

Auto Close [Sample Text] (In D2) curr: OFF (In E2)

2) Same as 1) but here i want to capture if the cell contains Compliant or Non-Compliant (exact text match)

Disable Test Test [Sample Test] (In D3) Compliant (In E3)

3) Same as 1). if 'curr:' is present then Compliant or Non-Compliant condition should be ignored.

Test Agent Startup Test (In D4) curr: AUTO (In E4)

4) Same as 2

Sample Test Data Startup Test (In D5) Non-Compliant (In E5)

Is this possible?

Re: Nested IF Condition Help required in finding a particular text in a cell.

Hi Team,

Was anyone able to figure out a way to resolve this.

Re: Nested IF Condition Help required in finding a particular text in a cell.

asdf

Hi,

I have 2 sheets in a Workbook. Sheet1 and Template Sheet.

Sheet 1 contains the Raw Data and template is the place where i want the result to be displayed.

Formula has to be displayed in Template Sheet B2 and the Account Name is in Column A in this sheet (Unique Column)

In Sheet 1, I have a Account column in Column C and Domain in Column E and Member Column in Column D. Account column contains alphanumeric charecters and the conditions which i want to query is as follows:

1) If The text in Column D (D2) Cotains any of the text "Domain1, Domain2, Domain3, Domain4, Domain5, Domain6, Domain7" (Sample Text) then it should check Column C Value (C2). The Account name should either start with any of the Country Codes (Listed Below) or the Domain Names in the prevous condition (Domain1, Domain2, Domain3..... Domain7) (Sample Domainxxxxxxxxxxsvc1 or INxxxxxxxxxxservice (Upper/Lower Case should be acceptable), and should end with either the text in Column C should end with "svc, SVC, Svc or Service, service, SERVICE"

AND

2) If the text in Column D (D2) contains any of the text "Sample1, Sample2, Sample3 then it should check if the text in the Column C Value (C2) should start with the country code or the Domain name (Upper/Lower Case should be acceptable), and the text in Column C (C2) should end with "DSA, dsa, Dsa or MSA, msa, Msa"
I want the formula to be put in E2

Is this possible?

Already Posted

http://www.ozgrid.com/forum/showthread.php?t=197681

Please help

Re: Query based on the particular text in the string

Hi,

was any one able to find out a way.

Re: Query based on the particular text in the string

Modification to the current one.

The Raw Data is in Sheet1

I have left the Raw Data intact and i have created a Sheet 'Template' and have put the consolidated Account Name in Column A and in Column B i want the formula for the above conditions to be applied.

I have a Account column in Column C and Domain in Column D. Account column contains alphanumeric charecters and the conditions which i want to query is as follows:

1) If The String in Column D (Starting from D2 with dynamic range) Cotains any of the text "Sample1, Sample2, Sample3, Sample4, Sample5, Sample6, Sample7" then it should check Column C Value (C2 with Dynamic Range) Sample xxxxxxxxxxxxxsvc or xxxxxxxxxxxxxservice (Upper/Lower Case should be acceptable), the string in Column C should end with "svc, SVC, Svc or Service, service, SERVICE"

2) If the String in Column D (Starting from D2 with dynamic range) contains any of the text "Simple1, Simple2, Simple3 then it should check Column C Value (C2 with Dynamic Range) Sample xxxxxxxxxxxxxdsa or xxxxxxxxxxxxxmsa (Upper/Lower Case should be acceptable), the string in Column C should end with "DSA, dsa, Dsa or MSA, msa, Msa"
I want the formula to be put in E2

Is this possible?

Please help

Re: Vlook Up Macro for a Dynamic Range

Hi Max,

Sorry for not not providing the complete information.

That worked like a charm. I am exactly looking for the same code.

It will be helpful if that is refined at bit as below,

I would like a column to be inserted after Column A based on the number of Sheets to be compared to show the mapping result (Even if there is no empty column to show the result).

If I want to compare Sheet 1 (Column A) list with the Sheet 2, Sheet 3 (Column A everywhere) list, then in Sheet 1 after column A it should insert 2 columns to show the mapping result.

If I want to compare sheet 1 (Column A) list with sheet 2 (Column A) list, then in sheet 1 after column A it should insert 1 column to show the mapping result.

If possible I would also like the same thing If there is Sheet 4 as well (comparing 4 sheets).

And if there is a match then it should display "Matching" else "Not Matching" in all the columns (based on the comparing sheets).

Is this possible?

Thanks in advance

Hi,

I have a workbook where i have 3 worksheets.

Sheet 1 Column A has the Consolidated List of information with mixed combination of Alpha Numeric Characters (All the 3 Sheets contains the Similar Data in Column A).
There is same kind of list information in Sheet 2 and Sheet 3.
My Requirement is, every time I will get a request to do the comparison between two or 3 files.
In order to avoid that, is there a way with Macro where we can perform the VLook up function through a click on a button or Something like that.
Please note that the Macro should work for the Dynamic Range in the Column A.

Is that Possible?

I want to give this them to avoid any type of comparison requests coming to me.

Suggest the best possible way.

Sample attached.

Re: VBA Code to allow only the selected users to open the file

Quote from S O;753003

[wbc]*[/wbc]

Code

Private Sub Workbook_Open()


'// enter all usernames in lowercase


    Select Case LCase(Environ("USERNAME"))
        Case "bloggsj"
        Case "bloggsa"
        Case "doej"
        Case "macroman"
        Case Else
            MsgBox "You do not have access to view this workbook.", vbExclamation + vbOkOnly
            ThisWorkbook.Close False
    End Select
End Sub

Display More

Hi S O,

Thanks it worked like a charm.

royUK,

Thanks for the suggestion, but I am not aware of that

Re: VBA Code to allow only the selected users to open the file

Hi S O,

I am not able to add the Username code to my dashboard Code, please help me where I can add the code so that it will only allow the user ID's in the code to open the file. For Others a text should be displayed.