lookup help

[savbci]

in Sheet 1 i have the following columns:
Date/course/Location
the table including headers is from A1:C300

In Sheet 2 I want to extract the 'Chicago' Locations and have a similar
table with only Chicago values that are in Sheet 1.

ie.
Sheet 1:
Date/Course/Location
March 1/Math/Toronto
April 15/Science/Chicago
April 20/Biology/Pittsburg
June 3/Chemistry/Chicago

Result I want to achieve in Sheet 2:
Date/Course/Location
April 15/Science/Chicago
June 3/Chemistry/Chicago

It was suggested I use auto filter and cut and paste - however I want this
sheet to be constantly updated as people add in more info on sheet 1. thanks.

 

[Pete_UK]

Do you only want to see Chicago details on Sheet2, or might you want
to have it more flexible ? i.e. select the location from a drop-down
in a cell in Sheet2, and then have the sheet display the appropriate
data.

Pete

 

[savbci]

Pete - I do only want to see Chicago details on Sheet2. I want to somehow
keep generating all chicago information when info is added into the master. a
drop down won't work.
lemme know if you have any more suggestions

 

[Pete_UK]

Okay then, you need to add a formula to sheet 1 which will give a
sequential number for each Chicago entry. I think it would be better
to insert a new column A (which can later be hidden, so that your
sheet will look the same for data entry), and put this formula in the
new A2:

=IF(D2="Chicago",COUNTIF(D$22,"Chicago"),"-")

Then copy this down as far as you think you might need it and beyond
(eg to A10000). Strictly speaking, the "-" is not necessary and you
could just have "", but it helps you to see where you have copied the
formula. Now you can hide that column.

Then on Sheet2 A2 you can have this formula:

=IF(ISNA(MATCH(ROW(A1),Sheet1!A:A,0)),"",INDEX(Sheet1!B:B,MATCH(ROW
(A1),Sheet1!A:A,0)))

Put this in B2:

=IF(A2="","",INDEX(Sheet1!C:C,MATCH(ROW(A1),Sheet1!A:A,0)))

and this one in C2:

=IF(A2="","",INDEX(Sheet1!D,MATCH(ROW(A1),Sheet1!A:A,0)))

Format the cells how you wish them to appear (i.e. the Date column),
and then just copy these three formulae down as far as you need to
(and beyond).

You could put this formula in D1:

=MAX(Sheet1!A:A)

to tell you how many Chicago entries you have in Sheet1, so that you
can ensure that the 3 formulae have been copied down far enough.

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

Yo may refer to question 7 on http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
No formulas needed.
No copy/paste.
No helper columns.
Data entry and update dynamic.
Elaborate with other data without formulas.
http://www.mediafire.com/file/n3zwyemyqxm/09_19_09.xlsm