How would I write a formula to achieve this? http://office.microsoft.com/global/... lt.aspx?AssetID=ZA010900521033 This is an example from Microsoft. Where Car Type (Column B) = Guzzler, return columns A, C, E. I basically need to create a View based on the preset conditions, I was going to dump this into a separate sheet, so that Sheet 1 is master data, Sheet 2 would be "Guzzler" Sheet 3 would be "Belcher" and Sheet 4 would be "All data where Column A="Wang"" Thank you VERY Much for your assistance!

Hi, Your link doesn't work, so it is not possible to see what you are trying to achieve. Please edit your post and test that the link works. Also what do you mean by a 'View' and what are your 'preset conditions' Regards

I am not sure why the link did not post correct. <a href="http://office.microsoft.com/global/images/default.aspx? AssetID=ZA010900521033">Let's try it this way. View - I want to create a separate sheet showing only the data that is in the preset array formula to report back. Preset Conditions - whatever I determine to be the limiting factor, in this example, "Guzzler" or "Belcher" would be a limiter or the salesperson would be another example.

Hi, There are various ways to go about this. I would first of all complete the Sales Person column, so that every row is complete. In A3 put the formula =A2, then copy the formula into the empty cells in column A This is what the data looks like: A B C D E 1 Sales Car Number Unit Total Person Type Sold Price Sales 2 Barnhill Guzzler 5 2200 11000 3 Barnhill Belcher 4 1800 7200 4 Ingle Guzzler 6 2300 13800 5 Ingle Belcher 8 1700 13600 6 Jordan Guzzler 3 2000 6000 7 Jordan Belcher 1 1600 1600 8 Pike Guzzler 9 2150 19350 9 Pike Belcher 5 1950 9750 10 Sanchez Guzzler 6 2250 13500 11 Sanchez Belcher 8 2000 16000 12 Teal Guzzler 6 2500 15000 13 Teal Belcher 7 1900 13300 14 Wang Guzzler 4 2200 8800 15 Wang Belcher 3 2000 6000 16 Young Guzzler 8 2300 18400 17 Young Belcher 8 2100 16800 The simple way is to use the Data - Filter - Auto Filter which will allow you to select any entry from any column, or any range in any column. Select A1 to E1 From the tollbar Data-Filter - Auto Filter You will get a drop down list for each column. Select any value to filter the list. Use Custom from the drop-down to select ranges e.g., Sales >5 Select the resulting filtered list , Copy and Paste to a new sheet. To do more interactive selection, you can use Excel's database functions I will do this on the same sheet as it is easier to see what is happening The database function requires two areas to be set up 1. Criteria 2. Results In this example I am using three criteria: a. Sales Person b. Car type c. Unit price Create the following: G H I 3 Min 2000 4 Max 2500 5 Sales Person Car Type Price 6 Note that 'Sales Person' and 'Car Type' are identical to headings in the source database Price is not the same Create a list of sales person names and a list of car types In cell G6 add data - validation - List and use the list of salesperson names plus one blank cell at the end In cell H6 do the same for the car type In cell I6 enter the formula: =AND(D2>=$I$3,D2<=$I$4) This formula uses the max and min values you enter just above in cells I3 and I4 and they must have absolute references (i.e., with the $ signs) D2 has no $ signs and is the first cell under the heading you want to filter by - in this case Unit Price. Now a results section G H I 8 Number Sold Unit Price Total Sales 9 The headings in row 8 must be identical to the column headings in the Source database In cell G9 enter the formula: =DSUM($A$1:$E$17,G8,$G$5:$I$6) In cell H9 enter the formula: =DAVERAGE($A$1:$E$17,H8,$G$5:$I$6) In cell I9 enter the formula: =DSUM($A$1:$E$17,I8,$G$5:$I$6) The formulas basically all start with the whole range of the database including the headings (essential) then the heading of the column to be calculated (can be entered as text or, as here, as a reference to the cell above and finally the two rows that make up the criteria. Now make selections in the criteria cells, such as selecting sales person Barnhill and no car type and price between 2000 and 2500 The result: Number Sold Unit Price Total Sales 5 2200 11000 To add information to the result section use additional formulas, for example in cell F9 enter =IF(G6="","All",G6) This will show the Sales person, or 'All' if the results include all sales persons. Lookup the other Database functions that can be used. Hope this gives you some ideas for what can be done. There are many other ways with ordinary formulas or even array formulas (which are a very specific type of formula in Excel). I don't think that you were referring to array formulas such as this {=SUM(IF($B$2:$B$17="Belcher",IF($A$2:$A$17="Jordan",$E$2:$E$17,0),0))} which are entered with Ctrl+Shift+Enter and have curly brackets, when you talked about 'preset array formula', but I may have been mistaken. Hope this gives you some ideas. Regards

WOW, what an answer, and THANK YOU! I have learned a lot from your posting and you have helped me with 2/3 of the project - amazing. The last bit is what I will call a "quick link". I am generating a report that says something along the lines of "We had to fire Jordan because he sold less than everyone else" and I need to link to a sheet that will only show Jordan's results. But I do need to show his results and hide the rest. And I would like to do this dynamically - just by changing ="CRITERIA" in the array formula. In a nutshell - function like a database SELECT WHERE statement would but by using Excel (Google Spreadsheets, actually, so the Filter/ Auto Filter tool would be simple but not applicable). Thanks again, you are amazing!

Hi, The following should give you an idea of how to achieve what you want. I haven't attempted to do it exactly, but you should have enough information to modify it to meet your needs. The Database is in A1 to E17 (including headers) 1. You need to create the statistics you need such as average and minimum so that you can select who to let go! 1 A create the raw data Create two columns, One headed Total sales and the second (must be to the right of Total sales) headed Sales person. Put the list of sales persons in the second column H I 1 Total Sales Sales Person 2 18200 Barnhill 3 27400 Ingle 4 7600 Jordan 5 29100 Pike 6 29500 Sanchez 7 28300 Teal 8 14800 Wang 9 35200 Young The Total sales uses the SUMIF() function that sums one column if the value in another column matches a 'criteria' The formula in H2 is: =SUMIF($A$2:$A$17,I2,$E$2:$E$17) Note the $ signs. I2 contains the criteria, in this case a sales person name. A2 to A17 is the column of sales person names in the database E2 to E17 is the range to sum. These two ranges must be identical sizes. Drag the formula down from H2. 1 B create the statistics G H I 10 Average 23762.5 11 Max 35200 Young 12 Min 7600 Jordan 13 No. staff 8 The formulas are: H10 =AVERAGE(H2:H9) H11 =MAX(H2:H9) H12 =MIN(H2:H9) H13 =COUNTA(I2:I9) To find the staff with the lowest sales, use VLOOKUP to find the lowest value and return the adjacent name. Vlookup only looks up a value in the leftmost column of a range and returns the value from the matching row in a column to the right of the lookup column. Thats why the sales person names must be to the right of the Total sales. In I12 put this formula:=VLOOKUP(H12,$H$2:$I$9,2,FALSE) 2. Create your final message Put pieces of text in a series of cells and use "&" to concatenate (join) pieces of text and the relevant statistics. 3.The final result came out like this: We have had to let go Jordan because his average sales were $7600, which was the lowest of all our 8 sales staff, who averaged $23762.5 4. You could use a textbox to hold the text. Then click on the textbox frame and in the formula bar enter the cell address of your finished text. Regards