Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
ajaffer
 
Posts: n/a
Default Creating A Search Database..Need Help


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default Creating A Search Database..Need Help

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 InsertNameDefine
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" wrote in
message ...

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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default Creating A Search Database..Need Help

(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 InsertNameDefine
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" wrote in
message ...

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
ajaffer
 
Posts: n/a
Default Creating A Search Database..Need Help


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.


--
ajaffer
------------------------------------------------------------------------
ajaffer's Profile: http://www.excelforum.com/member.php...o&userid=29316
View this thread: http://www.excelforum.com/showthread...hreadid=490340

  #5   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default Creating A Search Database..Need Help

"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
--




  #6   Report Post  
Posted to microsoft.public.excel.misc
ajaffer
 
Posts: n/a
Default Creating A Search Database..Need Help


that works, but another question:

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


--
ajaffer
------------------------------------------------------------------------
ajaffer's Profile: http://www.excelforum.com/member.php...o&userid=29316
View this thread: http://www.excelforum.com/showthread...hreadid=490340

  #7   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default Creating A Search Database..Need Help

"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
--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database functions should use criteria in formula, as 1-2-3 does 123user Excel Worksheet Functions 8 September 29th 05 08:57 PM
Creating multiple reports from a database malvis Excel Discussion (Misc queries) 0 July 28th 05 11:00 PM
Creating a Search Form Karen Excel Discussion (Misc queries) 12 April 1st 05 07:02 AM
How do I create a "List If" function.I need to search a database . Flying Solo Excel Worksheet Functions 2 December 7th 04 04:44 PM
PLEASE HELP!? Creating a simple database with excel, minor setback Pre-construction Manager & Excel !? Excel Worksheet Functions 1 November 30th 04 09:59 PM


All times are GMT +1. The time now is 11:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"