lookup simultaneous 3 columns

[saurabh_sharma]

how can we look up values by lookup function if the data apoints are alongside in 3 diff columns- one after the other. Can we look up a no. of ids against a series of dates in the adjacent columns and their performance metrics in teh following columns.. an advanced for of step by step column filtering ?

 

[saurabh_sharma]

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

 

[Aladin Akyurek]

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

 

[saurabh_sharma]

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

 

[Aladin Akyurek]

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

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