Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: lookup simultaneous 3 columns

  1. #11
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin
    Thanks for the tip.
    But there seems to be something that I am not doing correctly...
    When I write Drange in the pivot table it says refernce not valid.
    Instead of data! I am replacing it with the actual sheet name, and I hope Data!$A:A$ stays as it is?
    If u could expln a little further pls.

    Thanks

    On 2002-04-27 07:37, saurabh_sharma wrote:
    Thanks for teh help wit pivot table
    can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

    On 2002-04-27 06:52, Aladin Akyurek wrote:
    On 2002-04-27 06:22, saurabh_sharma wrote:
    Thanks for the help.

    I am facing an issue with the file below.
    I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
    Pls. advise and help wit this…
    calls contacts
    4/25/02 GCVBMVT 10554 9497
    4/25/02 GCVBSFT 7471 6483
    4/25/02 GCVBMVT 10554 9497
    4/24/02 GCVBIUT 607 537
    4/24/02 GCVBIPT 11993 10859
    4/24/02 GCVBKGT 5873 5085
    4/24/02 GCVBIZT 10865 9958
    4/23/02 GCVBIUT 607 537
    4/23/02 GCVBIPT 11993 10859
    4/23/02 GCVBKGT 5873 5085
    4/23/02 GCVBIZT 10865 9958
    4/23/02 GCVGSBT 4749 4255
    4/23/02 GCVGVFT 9350 8428
    4/23/02 GCVBIKT 8135 7319
    4/23/02 GCVBPET 7954 7157
    4/23/02 GCVBMZT 10426 9353
    4/23/02 GCVBMWT 5935 5270
    4/23/02 GCVBPAT 9740 8723
    4/23/02 GCVBMVT 10554 9497
    If you're going to create a pivot table from your data, you better use a dynamic name range and give this name as Range to the Wizard.

    I'll assume the sample above to be in A1:D20 including labels/column headings in a worksheet called Data.

    Activate Insert|Name|Define.
    Enter Nrecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,Data!$A:$A)

    Note that this formula uses your dates column.

    Activate Add. (Don't leave yet the Define Name window.)

    Enter Drange as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$A$1,0,0,Nrecs,4)

    Note that 4 indicates how many columns your data area covers.

    Activate OK.

    Now give Drange as value of Range in PivotTable Wizard.

    The above allows you to add to or delete from your data area as many records as you wish. At refresh PivotTable will know the new range.

    Aladin




  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-27 08:32, saurabh_sharma wrote:
    Thanks for seeking more info.
    I need to automate reports where performance by differnt associates appears in a consolidated report for 1 day. The macro is copying all the data one below the other. I need a graph of 1 particular id for all dates as a run chart and for 1 parameter at a time.
    Hope it clarifies...
    On 2002-04-27 08:19, Aladin Akyurek wrote:

    On 2002-04-27 07:37, saurabh_sharma wrote:
    Thanks for teh help wit pivot table
    can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?


    You appear to want a formula-based approach. OK, but What is "GCVB ids for the diff. Dates for the diff parameters"? I mean it's not clear to me what the lookup values are? Care to elaborate on that?
    Saurabh,

    You didn't exactly answer my question regarding the lookup value & showed impatience .

    In the mean time, I've been working on your problem. Before I post, I want a straight answer to the following question:

    Is GCVB* the lookup value, that is, you want to extract all IDs beginning with GCVB and dates, calls, and contacts associated with them, say, to a separate sheet. And, you want to use the extracted data to produce a graph. Is that right?

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-27 12:11 ]

  3. #13
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin
    I am forwarding you the file…thanks for your support
    I am trying to create a run chart that can show me a daily trend of charts that will tell me how an associate is trending on the diff. Parameters.
    All GCVB ids are collated in a daily format. Similar data is stored on a sheet for diff. Dates. I need to sort the data as per the ids and dates as well which can give me the performance trend as a run chart…- (data as on sheet 1 as forwarded to your id)


    On 2002-04-27 12:00, Aladin Akyurek wrote:
    On 2002-04-27 08:32, saurabh_sharma wrote:
    Thanks for seeking more info.
    I need to automate reports where performance by differnt associates appears in a consolidated report for 1 day. The macro is copying all the data one below the other. I need a graph of 1 particular id for all dates as a run chart and for 1 parameter at a time.
    Hope it clarifies...
    On 2002-04-27 08:19, Aladin Akyurek wrote:

    On 2002-04-27 07:37, saurabh_sharma wrote:
    Thanks for teh help wit pivot table
    can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?


    You appear to want a formula-based approach. OK, but What is "GCVB ids for the diff. Dates for the diff parameters"? I mean it's not clear to me what the lookup values are? Care to elaborate on that?
    Saurabh,

    You didn't exactly answer my question regarding the lookup value & showed impatience .

    In the mean time, I've been working on your problem. Before I post, I want a straight answer to the following question:

    Is GCVB* the lookup value, that is, you want to extract all IDs beginning with GCVB and dates, calls, and contacts associated with them, say, to a separate sheet. And, you want to use the extracted data to produce a graph. Is that right?

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-27 12:11 ]

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    [quote]
    On 2002-04-27 11:07, saurabh_sharma wrote:
    Hi Aladin
    Thanks for the tip.
    But there seems to be something that I am not doing correctly...
    When I write Drange in the pivot table it says refernce not valid.
    Instead of data! I am replacing it with the actual sheet name, and I hope Data!$A:A$ stays as it is?
    If u could expln a little further pls.

    Thanks
    [quote]

    Because your dates in column A are not true dates. Test any of the cells, say, A5, as follows:

    =ISNUMBER(A5)

    which should return TRUE

    or

    =MONTH(A5)

    which should return a month number.

    You need to repair that column.

    Another thing: You need to replace "Data" in formulas like

    =MATCH(9.99999999999999E+307,Data!$A:$A)

    by the name of the sheet in which your data is.

    Aladin



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •