Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 63
Default Look up from spreadsheet based on 6 criteria

Hi,

In Excel 2003, I have a page where my people will put in 6 criteria; Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers that
match ( or <) all 6 criteria. Can anyone please tell me how to do this?
--
Rich D
Armstrong Custom Homes
Redmond
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 109
Default Look up from spreadsheet based on 6 criteria

Rich

Add a column with a formula that concatenates your six criteria and
use that for you look up column.

Good luck.

Ken
Norfolk, Va


On May 22, 4:40*pm, Rich D wrote:
Hi,

In Excel 2003, I have a page where my people will put in 6 criteria; Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers that
match ( or <) all 6 criteria. *Can anyone please tell me how to do this?
--
Rich D
Armstrong Custom Homes
Redmond


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 63
Default Look up from spreadsheet based on 6 criteria

Ken,

You're assuming I know more than I do. Can you give me an example?

thanks
--
Rich D
Armstrong Custom Homes
Redmond


" wrote:

Rich

Add a column with a formula that concatenates your six criteria and
use that for you look up column.

Good luck.

Ken
Norfolk, Va


On May 22, 4:40 pm, Rich D wrote:
Hi,

In Excel 2003, I have a page where my people will put in 6 criteria; Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers that
match ( or <) all 6 criteria. Can anyone please tell me how to do this?
--
Rich D
Armstrong Custom Homes
Redmond



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default Look up from spreadsheet based on 6 criteria

You're assuming we know as much as you do. What does the "plans data base"
look like? Is it in Excel? Are the six criteria all in one cell, or separate
cells? Does each criteria match exactly, or do you, for example, want to
match 2.5 baths to 2 baths?

Regards,
Fred.

"Rich D" wrote in message
...
Ken,

You're assuming I know more than I do. Can you give me an example?

thanks
--
Rich D
Armstrong Custom Homes
Redmond


" wrote:

Rich

Add a column with a formula that concatenates your six criteria and
use that for you look up column.

Good luck.

Ken
Norfolk, Va


On May 22, 4:40 pm, Rich D wrote:
Hi,

In Excel 2003, I have a page where my people will put in 6 criteria;
Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers that
match ( or <) all 6 criteria. Can anyone please tell me how to do
this?
--
Rich D
Armstrong Custom Homes
Redmond




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 63
Default Look up from spreadsheet based on 6 criteria

Fred,
The plans db is in Excel also. The columns are labelled the same and the
criteria needs to either match exactly in some cases or be <= or + in some
cases, depending on which column I'm comparing to (looking in). This would
be much simpler if I could just email you the file. thanks for any help.
--
Rich D
Armstrong Custom Homes
Redmond


"Fred Smith" wrote:

You're assuming we know as much as you do. What does the "plans data base"
look like? Is it in Excel? Are the six criteria all in one cell, or separate
cells? Does each criteria match exactly, or do you, for example, want to
match 2.5 baths to 2 baths?

Regards,
Fred.

"Rich D" wrote in message
...
Ken,

You're assuming I know more than I do. Can you give me an example?

thanks
--
Rich D
Armstrong Custom Homes
Redmond


" wrote:

Rich

Add a column with a formula that concatenates your six criteria and
use that for you look up column.

Good luck.

Ken
Norfolk, Va


On May 22, 4:40 pm, Rich D wrote:
Hi,

In Excel 2003, I have a page where my people will put in 6 criteria;
Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers that
match ( or <) all 6 criteria. Can anyone please tell me how to do
this?
--
Rich D
Armstrong Custom Homes
Redmond






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 63
Default Look up from spreadsheet based on 6 criteria

Frd,

Also, yes, the criteria are in separate cells.
--
Rich D
Armstrong Custom Homes
Redmond


"Fred Smith" wrote:

You're assuming we know as much as you do. What does the "plans data base"
look like? Is it in Excel? Are the six criteria all in one cell, or separate
cells? Does each criteria match exactly, or do you, for example, want to
match 2.5 baths to 2 baths?

Regards,
Fred.

"Rich D" wrote in message
...
Ken,

You're assuming I know more than I do. Can you give me an example?

thanks
--
Rich D
Armstrong Custom Homes
Redmond


" wrote:

Rich

Add a column with a formula that concatenates your six criteria and
use that for you look up column.

Good luck.

Ken
Norfolk, Va


On May 22, 4:40 pm, Rich D wrote:
Hi,

In Excel 2003, I have a page where my people will put in 6 criteria;
Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers that
match ( or <) all 6 criteria. Can anyone please tell me how to do
this?
--
Rich D
Armstrong Custom Homes
Redmond




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Look up from spreadsheet based on 6 criteria

Rich,

Select all the cells in your database, then choose Data / Filter....
AutoFilter.

At the top of each column will appear a small dropdown. Use those dropdowns
to either
select a specific criteria (such as style) or select Custom and use the Less
than / Less than or equal to / Greater than/ etc. options to set the
limits. Excel will hide the rows where one or more values don't meet your
criteria.

If you want to have the values entered elsewhere, you would require a macro
to apply the filters. Much better is to just get into the habit of using
the dropdowns to directly select the criteria.

HTH,
Bernie
MS Excel MVP


"Rich D" wrote in message
...
Hi,

In Excel 2003, I have a page where my people will put in 6 criteria;
Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers that
match ( or <) all 6 criteria. Can anyone please tell me how to do this?
--
Rich D
Armstrong Custom Homes
Redmond



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default Look up from spreadsheet based on 6 criteria

Most of us don't mind helping, but we're certainly not looking for extra
work. Unfortunately there's become too much risk from accepting e-mails from
unknown sources due to viruses. So you likely won't get any takeup on that.

Searching on 6 criteria won't be that hard, especially if you are willing to
create a concatenated key. However, it's the "some cases <= or =" that will
be the monkey wrench. You will have to define these cases very well before
you can get Excel to look them up properly.

I like Bernie's idea. Why have them enter the criteria at all? Why not just
look it up in the data base using the AutoFilter?

Regards,
Fred.

"Rich D" wrote in message
...
Fred,
The plans db is in Excel also. The columns are labelled the same and the
criteria needs to either match exactly in some cases or be <= or + in
some
cases, depending on which column I'm comparing to (looking in). This
would
be much simpler if I could just email you the file. thanks for any help.
--
Rich D
Armstrong Custom Homes
Redmond


"Fred Smith" wrote:

You're assuming we know as much as you do. What does the "plans data
base"
look like? Is it in Excel? Are the six criteria all in one cell, or
separate
cells? Does each criteria match exactly, or do you, for example, want to
match 2.5 baths to 2 baths?

Regards,
Fred.

"Rich D" wrote in message
...
Ken,

You're assuming I know more than I do. Can you give me an example?

thanks
--
Rich D
Armstrong Custom Homes
Redmond


" wrote:

Rich

Add a column with a formula that concatenates your six criteria and
use that for you look up column.

Good luck.

Ken
Norfolk, Va


On May 22, 4:40 pm, Rich D wrote:
Hi,

In Excel 2003, I have a page where my people will put in 6 criteria;
Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers
that
match ( or <) all 6 criteria. Can anyone please tell me how to do
this?
--
Rich D
Armstrong Custom Homes
Redmond





  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 63
Default Look up from spreadsheet based on 6 criteria

Bernie,

I use auto filter all the time...love it. I showed it to the people at the
home office and, while impressed, they said no way could our sales people do
that. It needs to be less complicated. So, I am trying to come up with a
screen where the only cells showing on the form are the 5 or 6 criteria.
Then using that input, I want to be able to find all the records in the other
spreadsheets that fit and list them by plan number. Make sense?
--
Rich D
Armstrong Custom Homes
Redmond


"Bernie Deitrick" wrote:

Rich,

Select all the cells in your database, then choose Data / Filter....
AutoFilter.

At the top of each column will appear a small dropdown. Use those dropdowns
to either
select a specific criteria (such as style) or select Custom and use the Less
than / Less than or equal to / Greater than/ etc. options to set the
limits. Excel will hide the rows where one or more values don't meet your
criteria.

If you want to have the values entered elsewhere, you would require a macro
to apply the filters. Much better is to just get into the habit of using
the dropdowns to directly select the criteria.

HTH,
Bernie
MS Excel MVP


"Rich D" wrote in message
...
Hi,

In Excel 2003, I have a page where my people will put in 6 criteria;
Style,
Min Sq Footage, Max Sq Footage,# Bedrooms, # Baths, Garage stalls.

I need to search thru the plans data base to list the plan numbers that
match ( or <) all 6 criteria. Can anyone please tell me how to do this?
--
Rich D
Armstrong Custom Homes
Redmond




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
Extract data on one spreadsheet to another based on criteria AndyJ New Users to Excel 5 January 4th 08 01:43 AM
Add a row based on other criteria Joe Gieder Excel Worksheet Functions 4 June 15th 07 06:22 PM
IF-based SUMPRODUCT criteria creativeops Excel Discussion (Misc queries) 4 January 18th 07 06:36 PM
Summing based on 2 criteria cubsfan Excel Discussion (Misc queries) 2 April 20th 06 04:21 PM
Need help looking up value based on criteria akbreezo Excel Worksheet Functions 7 June 15th 05 10:53 PM


All times are GMT +1. The time now is 12:52 AM.

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"