Lookup / Summary Table

I want Excel to lookup a value in a table and return each row it finds that 
value in.

As an example:

Lookup Value: Ford

Lookup Table:

A1: Ford	B1: Focus
A2: Chevy	B2: Malibu
A3: Chevy	B3: S10
A4: Ford	B4: Ranger

Results Table:

A1: Ford	B1: Focus
A2: Ford	B2: Ranger

What I don’t want is:

A1: Ford	B1: Focus
A2:	B2: 
A3:	B3: 
A4: Ford	B4: Ranger

In other words, I don’t want a bunch of blank rows in the new summary table. 
 What function(s) can I use to create my summary table?
0
Utf
5/3/2010 7:46:46 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

8 Replies
631 Views

Similar Articles

[PageSpeed] 12

How about PIVOT table?


"Joe" wrote:

> I want Excel to lookup a value in a table and return each row it finds that 
> value in.
> 
> As an example:
> 
> Lookup Value: Ford
> 
> Lookup Table:
> 
> A1: Ford	B1: Focus
> A2: Chevy	B2: Malibu
> A3: Chevy	B3: S10
> A4: Ford	B4: Ranger
> 
> Results Table:
> 
> A1: Ford	B1: Focus
> A2: Ford	B2: Ranger
> 
> What I don’t want is:
> 
> A1: Ford	B1: Focus
> A2:	B2: 
> A3:	B3: 
> A4: Ford	B4: Ranger
> 
> In other words, I don’t want a bunch of blank rows in the new summary table. 
>  What function(s) can I use to create my summary table?
0
Utf
5/3/2010 8:09:01 PM
Hi Joe,

have you tried using AutoFilter?

If you particularly need the results in a seperate table, follow this. 
Assuming your lookup table is in Sheet1 and your results table will be in 
Sheet2:

Put your lookup value in Sheet2!A1, then
in Sheet2!B1:

=IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(1:1),
INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$A$50=$A$1)*
(ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),),
COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-")

in Sheet2!A2:

=IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(2:2),$A$1,"-")

Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as required.



"Joe" <Joe@discussions.microsoft.com> wrote in message 
news:D9E3FF27-5CAD-4EF5-AD06-400D3B107C8E@microsoft.com...
>I want Excel to lookup a value in a table and return each row it finds that
> value in.
>
> As an example:
>
> Lookup Value: Ford
>
> Lookup Table:
>
> A1: Ford B1: Focus
> A2: Chevy B2: Malibu
> A3: Chevy B3: S10
> A4: Ford B4: Ranger
>
> Results Table:
>
> A1: Ford B1: Focus
> A2: Ford B2: Ranger
>
> What I don’t want is:
>
> A1: Ford B1: Focus
> A2: B2:
> A3: B3:
> A4: Ford B4: Ranger
>
> In other words, I don’t want a bunch of blank rows in the new summary 
> table.
> What function(s) can I use to create my summary table? 

0
Steve
5/4/2010 3:24:39 PM
Normally a pivot table would work.  I didn't mention in my original post that 
the data that I need to summarize will be imported into Microsoft Streets & 
Trips.  The example I gave was an oversimplification of what I'm doing.  I 
really have a list of locations and their corresponding longitude / latitudes 
that Streets & Trips will display on a map.  The import feature is not very 
fancy and can not handle things like filters / pivot tables.  Thanks for the 
suggestion though, it would work if I didn't have to use the data for 
importing.

"Teethless mama" wrote:

> How about PIVOT table?
> 
> 
> "Joe" wrote:
> 
> > I want Excel to lookup a value in a table and return each row it finds that 
> > value in.
> > 
> > As an example:
> > 
> > Lookup Value: Ford
> > 
> > Lookup Table:
> > 
> > A1: Ford	B1: Focus
> > A2: Chevy	B2: Malibu
> > A3: Chevy	B3: S10
> > A4: Ford	B4: Ranger
> > 
> > Results Table:
> > 
> > A1: Ford	B1: Focus
> > A2: Ford	B2: Ranger
> > 
> > What I don’t want is:
> > 
> > A1: Ford	B1: Focus
> > A2:	B2: 
> > A3:	B3: 
> > A4: Ford	B4: Ranger
> > 
> > In other words, I don’t want a bunch of blank rows in the new summary table. 
> >  What function(s) can I use to create my summary table?
0
Utf
5/4/2010 5:46:01 PM
Steve,

Normally AutoFilter would work.  The example I gave was an 
oversimplification of what I am doing.  The summary table I am trying to 
create will be imported by Microsoft Streets & Trips.  The data I am working 
with is actually a list of locations with their respective longitude / 
latitude.  Streets & Trips can't handle filters and pivot tables.  Therefore 
there is a need to create a new separate table to use for the import process. 
 To add a few questions to your original answer (which worked by the way, 
thanks)...

1. How do I show more columns in my new summary table?
2. How do I add a second value for Excel to lookup?

Once again, thanks for your help.

Joe

"Steve Dunn" wrote:

> Hi Joe,
> 
> have you tried using AutoFilter?
> 
> If you particularly need the results in a seperate table, follow this. 
> Assuming your lookup table is in Sheet1 and your results table will be in 
> Sheet2:
> 
> Put your lookup value in Sheet2!A1, then
> in Sheet2!B1:
> 
> =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(1:1),
> INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$A$50=$A$1)*
> (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),),
> COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-")
> 
> in Sheet2!A2:
> 
> =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(2:2),$A$1,"-")
> 
> Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as required.
> 
> 
> 
> "Joe" <Joe@discussions.microsoft.com> wrote in message 
> news:D9E3FF27-5CAD-4EF5-AD06-400D3B107C8E@microsoft.com...
> >I want Excel to lookup a value in a table and return each row it finds that
> > value in.
> >
> > As an example:
> >
> > Lookup Value: Ford
> >
> > Lookup Table:
> >
> > A1: Ford B1: Focus
> > A2: Chevy B2: Malibu
> > A3: Chevy B3: S10
> > A4: Ford B4: Ranger
> >
> > Results Table:
> >
> > A1: Ford B1: Focus
> > A2: Ford B2: Ranger
> >
> > What I don’t want is:
> >
> > A1: Ford B1: Focus
> > A2: B2:
> > A3: B3:
> > A4: Ford B4: Ranger
> >
> > In other words, I don’t want a bunch of blank rows in the new summary 
> > table.
> > What function(s) can I use to create my summary table? 
> 
0
Utf
5/4/2010 6:08:01 PM
Ok here goes...

(Put your list of criteria in Sheet2!Z1:Z10 for this example - obviously you 
can change that reference to whatever you need.)

In Sheet2!A1

=IF($Z$1="","-",$Z$1)


in Sheet2!A2

=IF($A1="-","-",
IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)>COUNTIF($A$1:$A1,$A1),$A1,
IF(MATCH($A1,$Z$1:$Z$10)=COUNTA($Z$1:$Z$10),"-",
INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1))))


in Sheet2!B1:B2

=IF($A1="-","-",
INDEX(Sheet1!B$1:B$50,LARGE(INDEX((Sheet1!$A$1:$A$50=$A1)*
(ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),),
COUNTIF(Sheet1!$A$1:$A$50,$A1)+1-COUNTIF($A$1:$A1,$A1))))


Copy A2:B2 down as far as required, then copy B2:Bx to C2:Cx (or, for other 
purposes, as far across as required).

I suspect these could be simplified further, especially if that second one 
was re-created as an array formula (which I prefer to avoid where possible), 
but my eyes are starting to itch, so I'm off to bed.  Night!

HTH
Steve D.



"Joe" <Joe@discussions.microsoft.com> wrote in message 
news:22D9E892-1A72-486C-B809-835A46B348E9@microsoft.com...
> Steve,
>
> Normally AutoFilter would work.  The example I gave was an
> oversimplification of what I am doing.  The summary table I am trying to
> create will be imported by Microsoft Streets & Trips.  The data I am 
> working
> with is actually a list of locations with their respective longitude /
> latitude.  Streets & Trips can't handle filters and pivot tables. 
> Therefore
> there is a need to create a new separate table to use for the import 
> process.
> To add a few questions to your original answer (which worked by the way,
> thanks)...
>
> 1. How do I show more columns in my new summary table?
> 2. How do I add a second value for Excel to lookup?
>
> Once again, thanks for your help.
>
> Joe
>
> "Steve Dunn" wrote:
>
>> Hi Joe,
>>
>> have you tried using AutoFilter?
>>
>> If you particularly need the results in a seperate table, follow this.
>> Assuming your lookup table is in Sheet1 and your results table will be in
>> Sheet2:
>>
>> Put your lookup value in Sheet2!A1, then
>> in Sheet2!B1:
>>
>> =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(1:1),
>> INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$A$50=$A$1)*
>> (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),),
>> COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-")
>>
>> in Sheet2!A2:
>>
>> =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(2:2),$A$1,"-")
>>
>> Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as 
>> required.
>>
>>
>>
>> "Joe" <Joe@discussions.microsoft.com> wrote in message
>> news:D9E3FF27-5CAD-4EF5-AD06-400D3B107C8E@microsoft.com...
>> >I want Excel to lookup a value in a table and return each row it finds 
>> >that
>> > value in.
>> >
>> > As an example:
>> >
>> > Lookup Value: Ford
>> >
>> > Lookup Table:
>> >
>> > A1: Ford B1: Focus
>> > A2: Chevy B2: Malibu
>> > A3: Chevy B3: S10
>> > A4: Ford B4: Ranger
>> >
>> > Results Table:
>> >
>> > A1: Ford B1: Focus
>> > A2: Ford B2: Ranger
>> >
>> > What I don’t want is:
>> >
>> > A1: Ford B1: Focus
>> > A2: B2:
>> > A3: B3:
>> > A4: Ford B4: Ranger
>> >
>> > In other words, I don’t want a bunch of blank rows in the new summary
>> > table.
>> > What function(s) can I use to create my summary table?
>> 

0
Steve
5/4/2010 9:13:16 PM
Slight amendments: the first MATCH in the second formula should have ,0 at 
the end, and using SMALL rather than LARGE (with slight change to the final 
argument) may make the third formula a little more readable.

=IF($A1="-","-",
IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)>COUNTIF($A$1:$A1,$A1),$A1,
IF(MATCH($A1,$Z$1:$Z$10,0)=COUNTA($Z$1:$Z$10),"-",
INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1))))

=IF($A1="-","-",
INDEX(Sheet1!B$1:B$50,SMALL(INDEX((Sheet1!$A$1:$A$50=$A1)*
(ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),),
COUNTIF(Sheet1!$A$1:$A$50,"<>"&$A1)+COUNTIF($A$1:$A1,$A1))))



"Steve Dunn" <stunn@sky.com> wrote in message 
news:FDD2C883-BA41-4262-B959-184DD247EBA2@microsoft.com...
> Ok here goes...
>
> (Put your list of criteria in Sheet2!Z1:Z10 for this example - obviously 
> you can change that reference to whatever you need.)
>
> In Sheet2!A1
>
> =IF($Z$1="","-",$Z$1)
>
>
> in Sheet2!A2
>
> =IF($A1="-","-",
> IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)>COUNTIF($A$1:$A1,$A1),$A1,
> IF(MATCH($A1,$Z$1:$Z$10)=COUNTA($Z$1:$Z$10),"-",
> INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1))))
>
>
> in Sheet2!B1:B2
>
> =IF($A1="-","-",
> INDEX(Sheet1!B$1:B$50,LARGE(INDEX((Sheet1!$A$1:$A$50=$A1)*
> (ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),),
> COUNTIF(Sheet1!$A$1:$A$50,$A1)+1-COUNTIF($A$1:$A1,$A1))))
>
>
> Copy A2:B2 down as far as required, then copy B2:Bx to C2:Cx (or, for 
> other purposes, as far across as required).
>
> I suspect these could be simplified further, especially if that second one 
> was re-created as an array formula (which I prefer to avoid where 
> possible), but my eyes are starting to itch, so I'm off to bed.  Night!
>
> HTH
> Steve D.
>
>
>
> "Joe" <Joe@discussions.microsoft.com> wrote in message 
> news:22D9E892-1A72-486C-B809-835A46B348E9@microsoft.com...
>> Steve,
>>
>> Normally AutoFilter would work.  The example I gave was an
>> oversimplification of what I am doing.  The summary table I am trying to
>> create will be imported by Microsoft Streets & Trips.  The data I am 
>> working
>> with is actually a list of locations with their respective longitude /
>> latitude.  Streets & Trips can't handle filters and pivot tables. 
>> Therefore
>> there is a need to create a new separate table to use for the import 
>> process.
>> To add a few questions to your original answer (which worked by the way,
>> thanks)...
>>
>> 1. How do I show more columns in my new summary table?
>> 2. How do I add a second value for Excel to lookup?
>>
>> Once again, thanks for your help.
>>
>> Joe
>>
>> "Steve Dunn" wrote:
>>
>>> Hi Joe,
>>>
>>> have you tried using AutoFilter?
>>>
>>> If you particularly need the results in a seperate table, follow this.
>>> Assuming your lookup table is in Sheet1 and your results table will be 
>>> in
>>> Sheet2:
>>>
>>> Put your lookup value in Sheet2!A1, then
>>> in Sheet2!B1:
>>>
>>> =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(1:1),
>>> INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$A$50=$A$1)*
>>> (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),),
>>> COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-")
>>>
>>> in Sheet2!A2:
>>>
>>> =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(2:2),$A$1,"-")
>>>
>>> Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as 
>>> required.
>>>
>>>
>>>
>>> "Joe" <Joe@discussions.microsoft.com> wrote in message
>>> news:D9E3FF27-5CAD-4EF5-AD06-400D3B107C8E@microsoft.com...
>>> >I want Excel to lookup a value in a table and return each row it finds 
>>> >that
>>> > value in.
>>> >
>>> > As an example:
>>> >
>>> > Lookup Value: Ford
>>> >
>>> > Lookup Table:
>>> >
>>> > A1: Ford B1: Focus
>>> > A2: Chevy B2: Malibu
>>> > A3: Chevy B3: S10
>>> > A4: Ford B4: Ranger
>>> >
>>> > Results Table:
>>> >
>>> > A1: Ford B1: Focus
>>> > A2: Ford B2: Ranger
>>> >
>>> > What I don’t want is:
>>> >
>>> > A1: Ford B1: Focus
>>> > A2: B2:
>>> > A3: B3:
>>> > A4: Ford B4: Ranger
>>> >
>>> > In other words, I don’t want a bunch of blank rows in the new summary
>>> > table.
>>> > What function(s) can I use to create my summary table?
>>>
> 

0
Steve
5/5/2010 8:36:31 AM
Worked like a charm. Thanks for all your help!

"Steve Dunn" wrote:

> Slight amendments: the first MATCH in the second formula should have ,0 at 
> the end, and using SMALL rather than LARGE (with slight change to the final 
> argument) may make the third formula a little more readable.
> 
> =IF($A1="-","-",
> IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)>COUNTIF($A$1:$A1,$A1),$A1,
> IF(MATCH($A1,$Z$1:$Z$10,0)=COUNTA($Z$1:$Z$10),"-",
> INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1))))
> 
> =IF($A1="-","-",
> INDEX(Sheet1!B$1:B$50,SMALL(INDEX((Sheet1!$A$1:$A$50=$A1)*
> (ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),),
> COUNTIF(Sheet1!$A$1:$A$50,"<>"&$A1)+COUNTIF($A$1:$A1,$A1))))
> 
> 
> 
> "Steve Dunn" <stunn@sky.com> wrote in message 
> news:FDD2C883-BA41-4262-B959-184DD247EBA2@microsoft.com...
> > Ok here goes...
> >
> > (Put your list of criteria in Sheet2!Z1:Z10 for this example - obviously 
> > you can change that reference to whatever you need.)
> >
> > In Sheet2!A1
> >
> > =IF($Z$1="","-",$Z$1)
> >
> >
> > in Sheet2!A2
> >
> > =IF($A1="-","-",
> > IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)>COUNTIF($A$1:$A1,$A1),$A1,
> > IF(MATCH($A1,$Z$1:$Z$10)=COUNTA($Z$1:$Z$10),"-",
> > INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1))))
> >
> >
> > in Sheet2!B1:B2
> >
> > =IF($A1="-","-",
> > INDEX(Sheet1!B$1:B$50,LARGE(INDEX((Sheet1!$A$1:$A$50=$A1)*
> > (ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),),
> > COUNTIF(Sheet1!$A$1:$A$50,$A1)+1-COUNTIF($A$1:$A1,$A1))))
> >
> >
> > Copy A2:B2 down as far as required, then copy B2:Bx to C2:Cx (or, for 
> > other purposes, as far across as required).
> >
> > I suspect these could be simplified further, especially if that second one 
> > was re-created as an array formula (which I prefer to avoid where 
> > possible), but my eyes are starting to itch, so I'm off to bed.  Night!
> >
> > HTH
> > Steve D.
> >
> >
> >
> > "Joe" <Joe@discussions.microsoft.com> wrote in message 
> > news:22D9E892-1A72-486C-B809-835A46B348E9@microsoft.com...
> >> Steve,
> >>
> >> Normally AutoFilter would work.  The example I gave was an
> >> oversimplification of what I am doing.  The summary table I am trying to
> >> create will be imported by Microsoft Streets & Trips.  The data I am 
> >> working
> >> with is actually a list of locations with their respective longitude /
> >> latitude.  Streets & Trips can't handle filters and pivot tables. 
> >> Therefore
> >> there is a need to create a new separate table to use for the import 
> >> process.
> >> To add a few questions to your original answer (which worked by the way,
> >> thanks)...
> >>
> >> 1. How do I show more columns in my new summary table?
> >> 2. How do I add a second value for Excel to lookup?
> >>
> >> Once again, thanks for your help.
> >>
> >> Joe
> >>
> >> "Steve Dunn" wrote:
> >>
> >>> Hi Joe,
> >>>
> >>> have you tried using AutoFilter?
> >>>
> >>> If you particularly need the results in a seperate table, follow this.
> >>> Assuming your lookup table is in Sheet1 and your results table will be 
> >>> in
> >>> Sheet2:
> >>>
> >>> Put your lookup value in Sheet2!A1, then
> >>> in Sheet2!B1:
> >>>
> >>> =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(1:1),
> >>> INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$A$50=$A$1)*
> >>> (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),),
> >>> COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-")
> >>>
> >>> in Sheet2!A2:
> >>>
> >>> =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)>=ROW(2:2),$A$1,"-")
> >>>
> >>> Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as 
> >>> required.
> >>>
> >>>
> >>>
> >>> "Joe" <Joe@discussions.microsoft.com> wrote in message
> >>> news:D9E3FF27-5CAD-4EF5-AD06-400D3B107C8E@microsoft.com...
> >>> >I want Excel to lookup a value in a table and return each row it finds 
> >>> >that
> >>> > value in.
> >>> >
> >>> > As an example:
> >>> >
> >>> > Lookup Value: Ford
> >>> >
> >>> > Lookup Table:
> >>> >
> >>> > A1: Ford B1: Focus
> >>> > A2: Chevy B2: Malibu
> >>> > A3: Chevy B3: S10
> >>> > A4: Ford B4: Ranger
> >>> >
> >>> > Results Table:
> >>> >
> >>> > A1: Ford B1: Focus
> >>> > A2: Ford B2: Ranger
> >>> >
> >>> > What I don’t want is:
> >>> >
> >>> > A1: Ford B1: Focus
> >>> > A2: B2:
> >>> > A3: B3:
> >>> > A4: Ford B4: Ranger
> >>> >
> >>> > In other words, I don’t want a bunch of blank rows in the new summary
> >>> > table.
> >>> > What function(s) can I use to create my summary table?
> >>>
> > 
> 
0
Utf
5/5/2010 2:59:01 PM
You're welcome Joe, glad to help.

"Joe" <Joe@discussions.microsoft.com> wrote in message 
news:0BA572C8-E54F-4548-B682-B17ACE592677@microsoft.com...
> Worked like a charm. Thanks for all your help!
>

0
Steve
5/5/2010 5:47:13 PM
Reply:

Similar Artilces:

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Copy whole table into one cell
Office Excel 2003 on Windows XP I'm trying to create a report-maker with excel... ugh! Anyways, there is a template that users are filling out saying whether something is defected or not... that's not important, the important part is, that I'm having VBA go though the table (using a button after it is filled out) and deleting the rows that aren't defected... in other words, if there are blank cells in column C (for example), the whole row in which that blank cell is located is deleted. I'll never have more than 40 rows and 5 columns in the table so I don'...

Merging two tables into one table
Can anyone help me with this problem, I have been trying umpteen different ways of doing this with SQL (Which I don't entirely understand...) I want to merge the contents of two tables, fields into one set of consolidated fields but in a particular order. Namely, Run_No and OrderSeq. This is the SQL: SELECT A.Run_No, B.Run_No, A.Point_ID, B.Point_ID, A.OrderSeq, B.OrderSeq FROM tbl_Points AS A INNER JOIN tbl_Points AS B ON (A.Run_No+1=B.Run_No) AND (A.OrderSeq-9=B.OrderSeq); I want to merge the first 9 records of A.Run_No with the first 9 records of B.Run_No, along with their cor...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Copying Pivot Table to new file--why does it still refer to original file?
Hello, I created a Pivot Table in an Excel file sent to me by a co-worker. She sends a updated file each month with the same layout, and I use the Move/Copy Sheet function to add my Pivot Table to the new file. The problem is, the table still points to the consolidation ranges in the file I copied from. So, I must manually redefine all the page fields to point to the current file. Is there any way around this? Thanks! LJ You could record a macro that creates a pivot table from the ranges in the active workbook. Store the macro in Personal.xls, or another workbook that is always ...

League Table 02-22-10
Hi! I am trying to create a league table suing quite a lot of data. Scenario: I have 20 shops who each use on average 25 intermediaries. I have obtained the montly sales revenue from each of the shops (so i have 20 separate spreadsheets) and would like to create a league table of the highest selling intermediaries. Any ideas, if of course you even understand what I am saying? Many thanks. Given the fact that you have not yet begun, I suggest that you first browse the available templates to see if something exists that can be adapted to your needs: http://office.micros...

linking tables from different Access templates?
Hello I have been playing with Access 2007 and am at the stage of almost being ready to ditch the "toy" database and prepare the real thing. I have been working on a database set up for us which has worked well but is showing its limitations (and highlighting mine!) I like the look of the Access 2007 Contacts and Events templates which, with some tweaking, could work well for us. My question is - is it possible to link the Contacts table with the Events table created in the templates? At the moment, I have an Events table (which includes various information abo...

Table properties
Is there a way to determine what the cell height is in Pub 2002? I know I can change it by dragging but I'm rather anal - I want to make all of them the same. -- The problem with resting on your laurels is that eventually you are sitting on dead branches. JoAnn What you do is select the whole table and say enter font size 16. Now when you go to make the Table smaller, the cells will only go to that point size. Consequently they will now all be the same height. You can now select the whole table again and change it to the point size of the font you want to use. Don't forget ...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

CSV import into table not working...?
I'm trying to use this command to import a CSV file into a table... DoCmd.TransferText acImportDelim, "RetSpec", "tblRet", "\\drake\subcon \ret\data\ret.csv", False it never does it...any ideas? If I do a straight import using those Specifications "RetSpec" tblRet is poppulated with the data from the CSV file without any problems. Right above the DoCMD i have: On Error GoTo BadImport and BadImport is: BadImport: MsgBox "Ret data was NOT imported!!", vbOKOnly, "File Import Problems" Exit Sub Any ideas? I always get my...

Blank Screen without Summary in Outlook Today Help Please
When I open my outlook today I see my summary flash just for a milisecond and then the screen is blank except for the words for the messages, tasks and calendar. Help if you can. What can I do to correct this? Thanks Steve ...

Maintaining table formatting when pasting into web client email body?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Hi, can anyone tell me how to paste a portion of an excel table into the body of a Microsoft Outlook Web Access email such that the table maintains all of its formatting (alignment)? I can't seem to figure it out. <br> Thx. <br> Bri ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Pivot Table Calculations #3
I am trying to run a pivot table report from an access database and I cannot get the pivot table to sum. It will show a count, but not a sum. When I choose the sum function, I get zero. I have used pt but this is the first time that I have had a problem and I am wondering if it has something to do with the way the database was set up in access. PB Most likely the numbers are brought in as Text. Ont the sheet in which they reside, format an empty cell as General. Copy that cell and select the data. Paste Special>Add>OK>Esc. Data should now be real numbers. Gord Dibben Excel...

Pivot table and OLAP excel 2000
Hi! When I create a pivot table report from OLAP source and then filter some data I get totals including the hidden fields. How could I display the totals of only filterd fields and not all? I notices that this works in Excel 2003, but one of our clients is using excel 2000 and the only thing I got is to export it to web component and then there turn of the totals of all field. Tnx in advance ...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

table doesn't update until after I press the escape key
I have a form with subform working but with one issue - when I enter data into a row I start on the next row, but get error about duplicate key. I press the escape key, it clears the data I just tried inputing, and it updates the key field. The key field causing the error uses this in the default value property field- =Nz(DMax("SongID","MusicOnPC_Songs"),0)+1 Is there something missing to make it update after I tab out of the last field for that row, and move into the next row? try removing the expression from the DefaultValue property of the SongID field. instead, ...

Create a pivot table via applescript
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hi, This is probably something simple that I'm missing but can anyone explain how to create a pivot table in excel via applescript? No matter what I do I keep getting the error &quot;can't make class pivot table&quot;. I've found an example online but it doesn't work either. <br><br><a href="http://lists.apple.com/archives/applesc">http://lists.apple.com/archives/applesc</a> � 00218.html <br><br>any help would be greatly appreciated <br><...

Lookup #4
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right? Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"? Mine is not working very well. Maybe my syntax is wrong. Any ideas? TIA Is there anyway you can st...

Filter message by reverse domain lookup
Hello all, I am kinda new to Exchange and was wondering if there was a function built into Exchange 2000 that would "look" at the source domain of the email message and then perform a "reverse" DNS lookup to verify that the domain actually exists and if it doesn't then have Exchange discard the email without sending an NDR? Is this possible strait out of the box? It seems like I remember iMail gateway having this feature. I am trying to filter out some of the spam that fills up my users inboxes. Thanks in advance. Jeremy jsteger@bellsouth.net (Jeremy Steger) wrote...

Can you change the default lookup from Account to Contact?
Is it possible to set the lookup for a customer field to default on "contacts" rather then "accounts?" We do more business with contacts so it is a pain to have to change this all the time. there is no supported way to do this in the current release -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Martin Flaherty" <mflaherty@techpg.com> wrote in message news:ucdNM74uEHA.3416@TK2MSFTNGP09.phx.gbl... > Is it possible to set the lookup for a customer field to default on > "contac...