Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts

    Doing a query of data with a dialog box in Excel

    Well, Im impressed with the disposition of the people here and their mastery of Excel. Thanks! 2nd question here and it's kind of vague and exploratory.

    I'm exploring using Excel as a database but would like to know if you can create a dialog box in VBA which might permit me to do a query in Excel and dump the results in a particular range or even another worksheet.

    As an example, say, I have a table with employees and their rank in sales for every month.

    Column A would have the employee names. On Column B would have employee location. Columns C onward would have the months of the year, Jan, Feb, Mar, etc...

    If I have 100 employees, then every cell within the table would show their rank in sales for that month, going from No. 1 to No. 100.

    On another worksheet I would list the locations on Column A, and the next columns would list product types, say computers, desks and pens, for simplicity's sake, which would occupy Columns B, C, D. Each location would have the amount of items sold of each type of product in the corresponding cell.

    What I'm wondering is if there's code, or a dialog box I could search in that I could say something like:


    1. Go to the locations table
    2. Find all locations where - at least 50 computers were sold and 25 desks were sold
    3. Go to employee table and get me the employees whose rankings were in the Top 25 at those locations which met that criteria.
    4. Print, or dump, the results into a specified range or worksheet


    So, for example, I might end up with the following output:

    John Dow 4 17 23 21 13
    Bob Smith 11 16 22 6
    Mark Jones 3 8 4

    Because all the people named worked at locations which met the criteria and the numbers beside their names indicate their rank each time they were in the Top 25 of sales for the months where they were (!) in the Top 25. John was in the Top 25 5 times in the year, Bob was 4 times and Mark was 3 times.

    I know this is probably pretty complicated. Right now the easiest thing would probably be to have a dialog box which would let me choose the criteria, but if VBA code works, I'd be so (!) happy to try it!

    For whatever it's worth, I've attached a 2003 file with some dummy data to illustrate what I'm thinking. It shows a section called 'Report' where I've slotted in the desired output. I know it's all kind of unpolished, but I'm just trying to flesh out an idea.

    I probably should be using a database for this but I'm just wondering if this can be done in Excel.

    I don't know anythng about Excel dashboards, or how to build them, but maybe that's what I'm describing. I just don't know and wanted some ideas.

    Thanks, folks!

    DEMO File.xls
    Last edited by olives; 2015-04-08 at 09:47.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Yes, this can be done.
    Will post something back here when I've thought about it a bit more.

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-07)

  4. #3
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Just wanted to say thanks to everyone who is participating in this thread. In my Intro to Computers class today, we are talking about Excel Macros and VBA, and I used this as an example of a real life macro that was designed to save someone time. We didn't get into the specifics of the workbook, but just reading the initial plea for help was enough to get them to understand the enormity of the emailing task, should they have been trying to accomplish it manually. It's a great scenario, so thank you for asking the initial question!

  5. The Following User Says Thank You to 13ILGal For This Useful Post:

    olives (2015-04-07)

  6. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Your sample file is lacking info needed. Pls provide a file with before/after worksheets along with the logic of where to where.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  7. The Following User Says Thank You to Supershoe For This Useful Post:

    olives (2015-04-07)

  8. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi olives

    See attached file.

    NOTES:
    1. On sheet [Location Sales],
    enter your required products criteria in cells [i2:k2]

    2. Click button [Click here to ..]
    matching locations will be output to column [O],
    the range name "critLocation" will be updated for matching Locations that meet your products criteria.

    3. Switch to [Employee Sales By Month]
    Click button [Click here to filter Employees..]

    The data range has conditional formats which show rankings 1 to 25 as 'green'
    (you can delete columns [W:AD], these shown for reference to your first sample)

    I added more sample data and locations, for testing.

    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-07)

  10. #6
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    ... See attached file... I added more sample data and locations, for testing....
    zeddy... it looks beautiful... you're an artist! I like it... I'm in awe! Beautiful! One more question, zeddy, I'd like a macro to 'erase' or delete all the values 'greater than 25' in the sales rankings... how do I write one?... I've recorded macros to subsitute numbers with "", a null character, but wouldn't know how to say 'erase all numbers greater than 25 for the selected table... I know I can 'hide' them with conditional formatting but I'd want to 'erase' those values so I can cut or paste to manipulate further... Thank you, zeddy!
    Last edited by olives; 2015-04-08 at 07:46.

  11. #7
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Supershoe View Post
    Your sample file is lacking info.... Pls provide... before/after worksheets along with the logic of where to where.
    I don't know if this is what you need, but the file has two worksheets 1) Employee Sales By Month and 2) Location Sales. Both of those are raw data. That is the 'Before'. The 1st tab - Employee Sales By Month - has a section off to the right with a heading called 'Report' (Column W). The names in that section are followed by each employee's ranking in the Top 25 they had throughout the year. That section (the 'Report) is the 'After'. That 'output' matches the criteria I set out in my OP.

    zeddy did a beautiful job of populating the database (how did you do that zeddy?) so it may be clearer from his example worksheet. I've asked him (above) for a macro to delete all values greater than 25, so I'm a little closer to my 'dream' output.

    The next step might be doing another macro that 'joins' the displayed Top 25 rankings so there's no empty cells between them, as it appears on my orginal DEMO file. Let me know if you understood or if you'd like directions in the spreadsheet itself... Thanks, shoe!

  12. #8
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by 13ILGal View Post
    Just wanted to say thanks to everyone who is participating in this thread...
    Nice note, thanks...

  13. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi olives

    See attached version 2.

    I have added a routine to 'erase all numbers greater than 25'. Instead of reading all cells in the range one-at-a-time, it is more efficient to read the data into VBA in one go, make the required adjustments, and then write the data back to the worksheet in a single operation (rather than cell-by-cell).

    After these values have been removed, the existing conditional formats would now make all these empty cells 'green' (since an empty cell is 'less-than-26'), so I added a line to remove the conditional formats as no longer required.

    Populating the database basically involved sorting a column of numbers 1 to 100 into a random order and then 'pasting' these into the month columns. I 'preserved' your initial values for the first 4 records. I used my Sample Excel Data file (posted in a previous thread) to find some random names and Locations.

    I have added a routine which extracts non-blank values from the month columns, into adjacent cells as requested. It uses a devious transpose trick (because Excel VBA doesn't like shifting cells to the left after deleting blank cells, but will happily do a shift-cells-up).

    I noticed in your example file that you are familiar with the use of double-click events on your sheet [Location Sales]. So I added my own favourite double-click event for the header row on sheet [Employee Sales By Month]. If you double click any cell in the header row, it will sort on that particular column. Double-clicking the chosen header cell again, will reverse the sort. Double-clicking in cell [A1] will put the records back into original order. This sorting is useful to see who had the most-times-in-top-25 (by double-clicking in cell [S1]) Or you could sort by Employee name, or by Location etc etc. with just a double-click.

    I documented each line of code to help others following this.

    zeddy
    Attached Files Attached Files

  14. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-08)

  15. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi olives

    ..so just to be clear, in the version2 file, click the button [1. Click here to..], and then click the button [2. Click here to..]


    zeddy

  16. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-08)

  17. #11
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    ... I added my own favourite double-click event.. sort on that particular column....
    zeddy, can I hug you?... It's just beautiful... I'm just reading this after having posted the other thread, I can't wait to show version 2 off to the people at work tomorrow... You make me look good! Thanks...

  18. #12
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Zeddy, Might I suggest this because it will allow clicking on any cell. It also doesn't rely on u1 but on the min/max in the column.
    '===========
    option explicit
    Private Sub Worksheet_BeforeDoubleClick _
    (ByVal Target As Range, Cancel As Boolean)

    Dim tc As Long
    tc = Target.Column
    If Cells(2, tc) > Cells(Rows.Count, tc).End(xlUp) Then
    Target.Sort key1:=Target.Offset(1), order1:= _
    xlAscending, Header:=xlYes
    Else
    Target.Sort key1:=Target.Offset(1), order1:= _
    xlDescending, Header:=xlYes
    End If
    Cells(1, tc).Select
    End Sub
    '========
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  19. The Following 2 Users Say Thank You to Supershoe For This Useful Post:

    olives (2015-04-09),zeddy (2015-04-08)

  20. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Don

    That is brilliant!
    I shall be using that better method from now on.

    However, I still prefer to have my double-click-sort based only on the header row, because I use the double-click below the header row for other purposes. Basically, when the 'computer' makes 'suggested' changes to certain cell values, I have the cell automatically colour-coded to indicate it is a 'computer-generated' value. They can acknowledge 'acceptance' of the suggested value by double-clicking the cell, (which then changes the cell colour), or, by 'manually' entering their 'own' value, the cell colour is also changed etc etc.

    zeddy

  21. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-09)

  22. #14
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Supershoe View Post
    .. it will allow clicking on any cell...
    shoe, what does this do... let me do the sort by clicking on any cell in a particular column?...

  23. #15
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    ..so just to be clear...
    zeddy I've been playing with the sheet a little, I'm making the columns on the [Location] tab sortable, too, for example, which I love being able to do and I have some questions about the buttons and the sequence...

    1. If I wanted to, I can move the 1st button, the one that sets the criteria, or a copy of it, to the first tab [Employee Sales], right?

    2. I just realized something stupid in my workflow... If I clear all the vales '>25', I can't save the worksheet without losing my original data, with the '<25' values, right?...

    3. The first time I used it, I

    - clicked to extract the locations on the [Locations] tab
    - clicked on the 'filter by location' on the [Employee Sales] tab
    - clicked on the 'clear top 25' button
    - then clicked on the 'extract no-blank values' button

    When I hit the last button in the sequence, which is pure eye candy for me! , the one that 'extracts non-blank values', the results revert to the full employee list. I did it in the other sequence, and I got what I wanted. That's really not a problem, but I just have to make sure I do the steps in the right order, right?

    4. I added a MIN function on each row, at the end of the 'Extracted values' column (Column AF), but when I try to sort the extarcated 'non-blank' table by that column, by double-clicking on it (Column AF), I get an error in the code. I've highlighted (well, formatted it in red 'cause I don't know how to highlight it) the display I get as a bug below. I have no idea what to do to fix it...

    'The following routine sorts the data records when any column header
    'in row 1 is double-clicked.
    'Double-clicking again will reverse the sort.
    'Double-click in column [A] to sort the records back into original numeric order

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("1:1")) Is Nothing Then Exit Sub
    Cancel = True 'cancel normal double-click-to-edit-cell mode

    If [u1] = 0 Then 'fetch value from this cell
    Target.Sort key1:=Target.Offset(1), order1:=xlAscending, Header:=xlYes
    [u1] = 1 'change value to allow reverse sort on next double-click
    Else 'sort descending..
    Target.Sort key1:=Target.Offset(1), order1:=xlDescending, Header:=xlYes
    [u1] = 0 'change value to allow reverse sort on next double-click
    End If

    End Sub
    Last edited by olives; 2015-04-09 at 09:40.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •