Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract data on one spreadsheet to another based on criteria | New Users to Excel | |||
Add a row based on other criteria | Excel Worksheet Functions | |||
IF-based SUMPRODUCT criteria | Excel Discussion (Misc queries) | |||
Summing based on 2 criteria | Excel Discussion (Misc queries) | |||
Need help looking up value based on criteria | Excel Worksheet Functions |