Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup help
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup help
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 On Sep 18, 8:00*pm, savbci wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup help
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" wrote: 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 On Sep 18, 8:00 pm, savbci wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup help
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$2:D2,"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(She et1!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: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 On Sep 18, 8:56*pm, savbci wrote: 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" wrote: 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 On Sep 18, 8:00 pm, savbci wrote: 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.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup help
Hi,
Yo may refer to question 7 on http://ashishmathur.com/knowledgebaseII.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "savbci" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup help
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |