Creating A Search Database..Need Help

[ajaffer]

Hi, i need help creating a search database using excel lookup functions: vlookup, match and index. The criteria is below:

I have a sheet filled with data. Data Sheet:
Title, Season, Air Date, Star Date, Synopsis (Respectively. Data is sorted alphabetically by Title)

I have to create another worksheet, in which a user types a Season number, and the formula will display the related results. For example:
User types in Season 1
Sheet displays:

Title Air Date Star Date Synopsis
Pilot xxx xxxx yyyyy
New aaa bbb zzzz
etc..etc..

Thanks

[Max]

One play using non-array formulas ..

Sample construct available at:
http://cjoint.com/?mdctMl4MQJ
Creating A Search Database_ajaffer_misc.xls

In Sheet1, assume the source table is in cols A to E,
data in row2 down, with the key col "Season" in col B

Put in F2:
=IF(OR(Sheet2!$A$1="",B2=""),"",IF(Sheet2!$A$1=B2,ROW(),""))
Copy F2 down to say, F20 to cover the max expected extent of data
(Leave F1 empty)

In Sheet2,
Cell A1 will be reserved for input, e.g. : Season 3

A2:E2 contains the same labels
> Title, Season, Air Date, Star Date, Synopsis

Put in A3:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
Copy across to E3, fill down to E21
(cover the same extent as was done in Sheet1's col F)

Sheet2 will return the required search results from Sheet1 for the season
input in A1, with all results neatly bunched at the top

--
And instead of manual input into A1, we could set up a Data Validation
droplist there for easy selection

In a new sheet: DV
put in A1: Season 1, fill down to A10 (say)

Then create a defined range "Season" via:
Click Insert>Name>Define
Names in workbook: Season
Refers to: =DV!$A$1:$A$10

Select Sheet2'sA1 and click Data > Validation
Set it as
Allow: List
Source: =Season
Click OK

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ajaffer" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, i need help creating a search database using excel lookup functions:
> vlookup, match and index. The criteria is below:
>
> I have a sheet filled with data. Data Sheet:
> Title, Season, Air Date, Star Date, Synopsis (Respectively. Data is
> sorted alphabetically by Title)
>
> I have to create another worksheet, in which a user types a Season
> number, and the formula will display the related results. For example:
> User types in Season 1
> Sheet displays:
>
> Title Air Date Star Date Synopsis
> Pilot xxx xxxx yyyyy
> New aaa bbb zzzz
> etc..etc..
>
> Thanks
>
>
> --
> ajaffer
> ------------------------------------------------------------------------
> ajaffer's Profile:
http://www.excelforum.com/member.php...o&userid=29316
> View this thread: http://www.excelforum.com/showthread...hreadid=490340
>

[Max]

(Re-sent: response sent earlier via OE, but didn't get thru')
One play using non-array formulas ..

Sample construct available at:
http://cjoint.com/?mdctMl4MQJ
Creating A Search Database_ajaffer_misc.xls

In Sheet1, assume the source table is in cols A to E,
data in row2 down, with the key col "Season" in col B

Put in F2:
=IF(OR(Sheet2!$A$1="",B2=""),"",IF(Sheet2!$A$1=B2,ROW(),""))
Copy F2 down to say, F20 to cover the max expected extent of data
(Leave F1 empty)

In Sheet2,
Cell A1 will be reserved for input, e.g. : Season 3

A2:E2 contains the same labels
> Title, Season, Air Date, Star Date, Synopsis

Put in A3:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
Copy across to E3, fill down to E21
(cover the same extent as was done in Sheet1's col F)

Sheet2 will return the required search results from Sheet1 for the season
input in A1, with all results neatly bunched at the top

--
And instead of manual input into A1, we could set up a Data Validation
droplist there for easy selection

In a new sheet: DV
put in A1: Season 1, fill down to A10 (say)

Then create a defined range "Season" via:
Click Insert>Name>Define
Names in workbook: Season
Refers to: =DV!$A$1:$A$10

Select Sheet2'sA1 and click Data > Validation
Set it as
Allow: List
Source: =Season
Click OK

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ajaffer" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, i need help creating a search database using excel lookup functions:
> vlookup, match and index. The criteria is below:
>
> I have a sheet filled with data. Data Sheet:
> Title, Season, Air Date, Star Date, Synopsis (Respectively. Data is
> sorted alphabetically by Title)
>
> I have to create another worksheet, in which a user types a Season
> number, and the formula will display the related results. For example:
> User types in Season 1
> Sheet displays:
>
> Title Air Date Star Date Synopsis
> Pilot xxx xxxx yyyyy
> New aaa bbb zzzz
> etc..etc..
>
> Thanks
>
>
> --
> ajaffer
> ------------------------------------------------------------------------
> ajaffer's Profile:
http://www.excelforum.com/member.php...o&userid=29316
> View this thread: http://www.excelforum.com/showthread...hreadid=490340
>

[ajaffer]

thanks for the help...

is it possible to do this without the DV. I tried removing it and replacing the data with something else and it didnt work.

[Max]

"ajaffer" wrote:
> .. is it possible to do this without the DV. I tried removing it and
> replacing the data with something else and it didnt work.

Of course. To clear the DV, just select the cell,
click Data > Validation, click "Clear All" > OK

> .. replacing the data with something else and it didnt work

well, this could probably be due to extraneous white spaces creeping into
either the manual input made in Sheet2!$A$1, and/or white spaces present
within the data in the "Season" col in Sheet1. The extra white spaces (not
readily visible, especially trailing spaces or an extra space in-between
text) could be throwing the matching off. We could wrap TRIM() around both
to increase robustness of matching. TRIM will remove all the extra white
spaces

Try this. Replace the criteria formula in Sheet1's F2 with:

=IF(OR(TRIM(Sheet2!$A$1)="",TRIM(B2)=""),"",
IF(TRIM(Sheet2!$A$1)=TRIM(B2),ROW(),""))

Copy F2 down as before

Let me know how this worked out for you.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[ajaffer]

that works, but another question:

is it possible to do without the row count in the F column.??

[Max]

"ajaffer" wrote:
> is it possible to do without the row count in the F column.??
No, for this non-array method, the criteria col F is required. But it's just
one col <g>, and you can copy it down way ahead of new data input in your
source table.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--