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

    All G:AD
    Production AU:BR
    Non Prod CI:DF
    No Field DW:ET

    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.

    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

    Files

    • Sample.xlsx

      (25.32 kB, downloaded 111 times, last: )

    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

    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)



    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?

    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


    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]




    Hi S O,


    Thanks it worked like a charm.


    royUK,


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