Spreadsheet Query

One of my clients has devised a list of items in a Microsoft Excel 
spreadsheet.  Against each item (in a different cell) is an amount in �, 
which represtents the items value.

My client wants to be able to use this spreadsheet, so that he can 
select all items under �6,000 for example.  Is this possible in Excel, 
or should he be using a database ?

Any suggestions would be appreciated.

Michael.
0
Mike6827 (2)
6/30/2004 3:50:22 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
417 Views

Similar Articles

[PageSpeed] 17

Hi Michael

click in the list, choose data / filter / autofilter
from the drop down list on the value column choose custom
set the first box to less than and type 6000 in the second - click OK
- to get all values back click on drop down arrow choose All (or data /
filter / show all)
- to turn the arrows off choose data / filter / autofilter again

Cheers
JulieD

"Michael Dedman" <Mike@Le-Software-Man.com> wrote in message
news:1088610030.66633.4@doris.uk.clara.net...
> One of my clients has devised a list of items in a Microsoft Excel
> spreadsheet.  Against each item (in a different cell) is an amount in �,
> which represtents the items value.
>
> My client wants to be able to use this spreadsheet, so that he can
> select all items under �6,000 for example.  Is this possible in Excel,
> or should he be using a database ?
>
> Any suggestions would be appreciated.
>
> Michael.


0
JulieD1 (2295)
6/30/2004 3:48:17 PM
Michael

Data>Filter>Autofilter>Custom.  Less than 6000.

Gord Dibben Excel MVP

On Wed, 30 Jun 2004 16:50:22 +0100, Michael Dedman <Mike@Le-Software-Man.com>
wrote:

>One of my clients has devised a list of items in a Microsoft Excel 
>spreadsheet.  Against each item (in a different cell) is an amount in �, 
>which represtents the items value.
>
>My client wants to be able to use this spreadsheet, so that he can 
>select all items under �6,000 for example.  Is this possible in Excel, 
>or should he be using a database ?
>
>Any suggestions would be appreciated.
>
>Michael.

0
Gord
6/30/2004 5:17:23 PM
Reply:

Similar Artilces:

Calculating time in query
Hi there, I'm searching for some help with my query. I'm making access to be my administration program for my freelance job. I've made a table with a row 'starttime' and a row 'endtime'. Now in my query I want to calculate the difference between these two rows. And then I want to make another query to calculate the totals of these differences. Is this a logic way to do this? And I'm trying to make a expression to calculate the difference but with no luck. The result is something like this: 3,2515785. I've found a kb article but I still can't figu...

Query File As
My Table Fields First Middle Last Suffix Need the new field in the qurey Will be FileAs: What is the expression so that FileAs displays Last (space) Suffix (if not null) then a (,) and a (space) then Fist (space) Middle Can anyone give me the correct expression for this so I can copy and paste from your reply Thank You from Marsman Try this -- IIF([Last] Is Not Null, [Last] & IIF([Suffix] Is Not Null, " " & [Suffix], "") & ", " & [First], & " " & [Middle], "") -- Build a little, test a little...

how do I open an excel spreadsheet attachment from microsoft word?
I received an email with an attachment that is an excel spreadsheet. My computer is coming up with a message to get program from online. I go to the website and click on microsoft excel and download, but I still can not open the attachment. What am I doing wrong, and how do I open this attachment, without having to tell my customer to send it to me as a word document? There is an Excel Viewer, a free download which will enable you to open Excel files, available from here: http://www.microsoft.com/downloads/details/aspx Also, you could think about Openoffice - I can't find a suita...

Query based DL ?
Upgrading from E2K to E2K3. By default there is a All Groups Address List were all of the Dl are nested. When you create a new Query Based DL it does not show up in the All groups List but does under the GAL. The reason is the filter string for the All Groups DL does not contain the (objectCategory=msExchDynamicDistributionList) attribute. I wanted to add this additional filter to the default All groups Address List but it is greyed out. Sure if I create a new All goups I can edit to my hearts desire. Is there a way to edit the default All Groups Address List??? ...

Can't edit SQL question in MS Excel/Query
Hello, I'm running Win2k SP3, Excel 2000 SP-1 and have created SQL questions that fetch data from an Oracle 8i DB via ODBC. Sometimes (or actually quite often) I can't edit the SQL questions in MS Query. The MS Query icon just flashes in the taskbar and disappears. Any wiz got any idea what can be done about it? Regards Hans Hans I'm not sure about the MS Query icon you are talking about in the taskbar but the way I can get to MS Query using Excel 2000 and connectiong to Oracle database is choosing 'Data' followed by 'Get External Data', and then choosing '...

Writing an IF statement in Microsoft Query
I'm querying our Oracle data to Excel and can't seem to get a formula return as a new field. I need the proper way to write this sequence: IF(RM.RM_STD= "LOCKER",50,RM.RM_AREA*RMSTD.COST_OF_SPACE) Which would basically say that if the room standard is a locker, we charge $50 - otherwise, take the room area and multiply it by the cost per square foot. But it doesn't matter how I write it...it's not working! HELP??!!! hi MS Query uses SQL and in SQL, you have to use the SQL IIf instead of the vb IF. and the IIf statement goes in the MSQ SQL stateme...

Simple Access Query/Form Question
Hello all, I know this may be a stupid question but i'm a newbie to Access. Here the background on what I'm designing. I'm creating a database to track special orders for our store customers. Employees enter information about the order including customers name, contact information, item to be ordered and etc. The main form has multiple check box to note if the customer has paid, the order has been placed, arrived, when the customer was notified and when the customer picks up the item. I'm looking to create a query that checks the field to see if a specific check box is checke...

2nd Posting
Haven't had an answer to this one, so I'll try again, I really need some pointers: I've got a query that I'm not sure how to develop. My tables: Quotes - QuoteNo, RaisedBy, Customer QuoteItems - RecordID, QuoteNo, PartNo, Lifecycle, Value There's a one-to-many relationship between Quotes and QuoteItems, i.e. one quote can have many items. I need to run a query to show a list of quotes with totals from the QuoteItems table i.e. QuoteNo, RaisedBy, Customer, List of PartNos, List of Lifecycles, TotalValue I haven't got a clue how to start this, I know it needs to b...

Difference between sum and groupby in query time
Can you help with this one? SUM fails - Data type mismatch in criteria expretion SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report]. [Last Name], Sum([Time Keeping Base Report].[Total Hours]) AS [SumOfTotal Hours] FROM [Time Keeping Base Report] GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report]. [Last Name]; works ok SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report]. [Last Name], [Time Keeping Base Report].[Total Hours] FROM [Time Keeping Base Report] GROUP BY [Time Keeping Base Report].[First Name], ...

MS QUERY COL HEADINGS
I have a query from Excel into a SQL2000 Database. I have some calculated fields like datepart and also some combined fields. The resultant query is retrieved without headings on calculated Columns. Also using a field with AS statement does not work and after saving the query and accessing again, the AS portion is gone. Example: I enter: ihead.invoice_no AS 'Invoice', save query, query returns column with heading of invoice_no. Look at SQL again and it shows ihead.invoice_no , Just putting a space where the AS 'Invoice' was. Probably a flag or setting, can't fin...

Grouping and Renaming Fields in Crosstab Queries
Hello, I have built a crosstab query showing counts by crew numbers for a basis of a graph that will be shown by only 'Inside' and 'Outside'. I would like to group together in the crosstab all the 'outside' crews i.e. Crew 1,2,3 and show them all together in the crosstab as 'Outside'; conversely, I would like to group together 4,5,6 and show them all together as 'Inside'. Is anyone able to provide me with the expression to do this. Thank you It surely might help if you post your crosstab SQL. -- KARL DEWEY Build a little - Test a little &quo...

Countif Query
Hi All. I got a query today. I really hope you'll be able to help. I have a list of ages, and i want to count how many people are between the ages of 29 and 40. I though that the countif function would work but to my despare i couldn't get it to work. could someone please help me. It would be much appreciated. Thanks Dewald dewald Try this: =SUMPRODUCT((A2:A250>=29)*(A2:A250<=40)) Andy. "dewald" <loverboy_04@msn.com> wrote in message news:blea50$3ls$1@ctb-nnrp2.saix.net... > Hi All. > > I got a query today. I really hope you'll be able to help...

Number records in a query
Hi everyone - I am hoping someone can help me out with a problem that I am having. I have a table that contains customer account information, basically what securities they own. I am trying to create a query that will number (from 1 to n) the records belonging to each account number, basically giving me the following: (New Field) Account # Security Number 1 1 1 1 2 2 1 3 3 2 1 ...

Sum Query
My table name is trade - it has 5,000,000 records. I would like all fields in the table to be displayed in the qury result. For each Date and c=Customer, I need to sum Qty. I think this is easy but cannot get it to work. Thank you in advance. SELECT Trade.Customer, Trade.[Date], Sum(Trade.Qty) AS SumOfQty FROM Trade GROUP BY Trade.Customer, Trade.[Date], ORDER BY Trade.Customer, Trade.[Date]; You may need to plug in the correct table and field names. Also if the Date field include time, or it isn't an actual Date/Time datatype, you may get some strange results. With 5 milli...

Sumproduct query
I have named ranges as follows: Area refers to $C$11:$C$22 Option refers to $D$11:$D$22 Pessimistic refers to $E$11:$E$22 Formula =SUMPRODUCT(Pessimistic*(Area=$C4)*(Option=$D4)) Returns the correct result, being the same as =SUBTOTAL(9,Pessimistic) after autofiltering database $C$11:$E$22 on Area set to value in C4 and Option set to value in D4. However, formula =SUMPRODUCT(Pessimistic,Area=$C4,Option=$D4) Returns value zero (not desired). Could someone please tell me what is different about the logical process in evaluating SUMPRODUCT function in each of the above cases? I understand the...

Unprotect my Spreadsheet
I wanted to set my spreadsheet up so that certain cells were protected from change while other could have data entered. I chose the cells that I wanted access to and protected the others using Sheet Protect from the Tool Menu. I used a password and confirmed it. Now I can't unprotect my sheet to make changes. I am the only user of the spreadsheet. How can I fix Please send answer to trumanhmason@shaw.ca What can't you unprotect it? Have you forgotten the password? If so, search Google for Excel password to find some "crackers" that may help. On Fri, 25 Jul 2003 ...

Need formilas value to dynamicaly update on 2 different worksheets in the same spreadsheet
I have a formula in an Excel spreadsheet that gets updated frequently. When it updates I currently copy and paste the value to another worksheet in the same spread sheet. I know that it is possible to have that update automatically, but cannot figure it out. I tried Paste Special, but it seems that works between completely separate documents. What am I missing? Thanks cjcoates@chartermi.net Why would you not just link it with a formula? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------...

Project Accounting related Query
Hi, We have situation, where the company is in the process of selling software to there clients. We need to book the product cost in Project accounting. With out using retention fees,project fees and other fees. can you help me out in booking the product cost in project accounting. with thanks ...

Write Name of Report Query in Report Footer
Is it possible to programmatically write the name of the query the report is based on in a text box in the report footer. "Steve Stad" <SteveStad@discussions.microsoft.com> wrote in message news:A1B0F793-C14A-4101-AB45-807BE72A4A11@microsoft.com... > Is it possible to programmatically write the name of the query the report > is based on in a text box in the report footer. Place a text box on your report, and set its Control Source property to: =[Report].[RecordSource] -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users...

how do i save spreadsheet to cdr disc
how do i save a spreedsheet to cdr disc Save the workbook to your hard disk, then use whatever CD burning software you have installed to get it onto the CD. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "karen" <karen@discussions.microsoft.com> wrote in message news:2E4F93C1-7A8F-48D9-8DEB-B050618F6381@microsoft.com... > how do i save a spreedsheet to cdr disc ...

Help with a spreadsheet
Hi there, hope someone can help. I've got a spreadsheet which has 4 sheets: 1 Reserve Stock (RES STOCK), 2 Available Stock (AVL STOCK), 3 Bul (BULK) and 4 Variance (VARIANCE). In each sheet is a list of product codes and their various quantitie (sheet 4 is blank). I want to add up the quantities for each code from sheets 1 and 2 an compare the result to sheet 3, displaying the results on sheet 4. Note, not all codes will appear on all 3 sheets. Quick example: Sheet 1 Code = ABCD112 Qty = 5 Sheet 2 Code = ABCD112 Qty = 22 Sheet 3 Code = ABCD112 Qty = 28 Sheet 4 Code = ABCD112 Match = ...

Web Query Refersh Interval
Hi I am using Excel 2002 to get a web query, it work fine but the AUTOMATIC REFERESH Interval is min 1 minute. I want set the Interval to 20 seconds, can any one help me about how to? Using VBA ? can give some example code ? Thank you Kelvin Hi AFAIK this is the best you can get. If you need shorter intervals you have to use the OnTime method and program your data access yourself -- Regards Frank Kabel Frankfurt, Germany Kelvin wrote: > Hi > > I am using Excel 2002 to get a web query, it work fine but the > AUTOMATIC REFERESH Interval is min 1 minute. > > I want set...

Export a query to a new database
Access 2003 I have a large database with several tables. One of the tables has about 74 fields and I have created a quety using only 12 fields and criteria to filter only certain records. I need to send the results of the query (basically the records and fields) to an associate. How can I export the records and fields of that query to a new Access MDB file to send to them without sending my other data? Does it need to be in an .mdb file? You can export the results to a text file (using TransferText) or to an EXCEL file (using TransferSpreadsheet) -- these will be a bit easier to c...

Running MS Query on an Excel Spreadsheet
Is it possible to run 2007 excel MS Query on an Excel spreadsheet with the same results as if you'd run it on an ODBC table like SQL? I have an export from a data table into an Excel spreadsheet and searching keywords is pretty cumbersome so I was hoping that MS Query might yield a better result. TIA hi the guts of Microsoft Query (MSQ) is ODBC and the language behind it is SQL. it is just conveniently packaged as a MS Wizard ie point and click. can you say the words "recorded macro"? tell us more about this "searching keywords is pretty cumbersome" so that we mi...

Crosstab query question
I use a crosstab query in a database as a first step in recalling multiple monetary transactions made on the same day that, when added together, exceed $10,000.00 and then generate a report based on the results. The query currently returns multiple transactions made on the current day as per the design. The OrderDate field in the query's design view is as follows: Field: OrderDate Table: Transaction Total: Where Crosstab: Sort: Criteria: "Date( )" Or: I would like to be able to bring this data up from dates in the past a...