Finding a row/column header in Pivottable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven Cheng - 15 Jun 2007 01:56 GMT

Okay, this is somewhat backwards but it is a necessary evil...

I have data that goes like this:

Transaction Date Location Account Amount
10/06/2006 1 200 $300
10/06/2006 2 100 $500
10/06/2006 1 100 $100
10/07/2006 3 200 $400

and this data is in Access and brought into a pivottable in excel such as
this...
Location (Column Headings)
Transaction Date (Row Heading 1)
Account (Row Heading 2)
SumofAmount (Values)

is there a "reverse lookup function" for pivottables that would tell me the
transaction date for particular amount, account, and location?

Reply to this Message

Teethless mama - 15 Jun 2007 02:40 GMT

=SUMPRODUCT(--(Location=2),--(Account=100),--(Amount=500),Transaction)

> Okay, this is somewhat backwards but it is a necessary evil...
>
[quoted text clipped - 15 lines]
> is there a "reverse lookup function" for pivottables that would tell me the
> transaction date for particular amount, account, and location?

Reply to this Message

Steven Cheng - 15 Jun 2007 02:47 GMT

what do the "--" mean?

> =SUMPRODUCT(--(Location=2),--(Account=100),--(Amount=500),Transaction)
>
[quoted text clipped - 17 lines]
> > is there a "reverse lookup function" for pivottables that would tell me the
> > transaction date for particular amount, account, and location?

Reply to this Message

Steven Cheng - 15 Jun 2007 23:28 GMT

Sorry, can you elaborate in what this "--" means for the sumproduct
function...I don't use it too often...

> what do the "--" mean?
>
[quoted text clipped - 19 lines]
> > > is there a "reverse lookup function" for pivottables that would tell me the
> > > transaction date for particular amount, account, and location?

Reply to this Message

David Biddulph - 15 Jun 2007 23:54 GMT

If you Google for "double unary minus" in conjunction with Excel and
SUMPRODUCT, you'll find explanations in places such as
http://www.mcgimpsey.com/excel/variablerate1.html

Signature

David Biddulph

> Sorry, can you elaborate in what this "--" means for the sumproduct
> function...I don't use it too often...
[quoted text clipped - 24 lines]
>> > > me the
>> > > transaction date for particular amount, account, and location?

Reply to this Message

Steven Cheng - 16 Jun 2007 00:22 GMT

thanks. I had tried but tooo much information came down.

> If you Google for "double unary minus" in conjunction with Excel and
> SUMPRODUCT, you'll find explanations in places such as
[quoted text clipped - 27 lines]
> >> > > me the
> >> > > transaction date for particular amount, account, and location?

Reply to this Message