+ Reply to Thread
Results 1 to 8 of 8

Multiple search and data return formulas

  1. #1
    Registered User
    Join Date
    11-28-2011
    Location
    London, Englan
    MS-Off Ver
    Excel 2010
    Posts
    8

    Multiple search and data return formulas

    Hello All,

    First time posting in an Excel forum, and would appreciate some good advice to achieve my aim with my spreadsheet.

    Sheet 1 contains 7 columns, all with raw data, some columns require date dd/mm/yyyy, some have names, some have numerical quantities with no decimals, some have numerical quantities with 3 decimals. This data is all raw data to be entered by me.

    Sheet 2 contains a range of search criteria to extract data from the database in sheet 1. I would like sheet 2 to offer a range of search functions to show data from sheet one. So in its simplest terms, sheet 2, I would select 2 date ranges, and it would then display the data from sheet 1 where these data ranges are met. In its most complex form, sheet 2 may be used to select data between 2 ranges, for a particular customer, between certain price ranges.

    I have attached the spreadsheet.

    Please advise or ask any more questions if possible.
    Attached Files Attached Files

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Excel 2010 - Multiple search and data return formulas

    jackifizzle,

    Welcome to the forum!
    Attached is a modified version of your exammple workbook. In sheet 'Search' cell A10 is the following array formula so it needs to be entered with Ctrl+Shift+Enter:
    Please Login or Register  to view this content.


    And then its copied over to column G and down for 30 rows. While this solution works, it isn't exactly efficient... at all. If you have experience with VBA (macros and userforms) I would instead recommend making a userform to pull the desired data. What you're looking for could also be accomplished via Access queries, which is probably the best method to obtain the desired results.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    11-28-2011
    Location
    London, Englan
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Excel 2010 - Multiple search and data return formulas

    Thank you, that formula is working very well for me, much appreciated. One small question, obviously on sheet 1 there are only 3 rows of data under the headings, if i add a 4th row of data, I am struggling to get this to appear on the second sheet using the search box's. I have tried changing some of the formula but cant seem to get it to work. How can I edit the formula so that it will search every single row on sheet 1 as in the end I believe there will be thousands of rows. I am happy with sheet 2 only displaying up to 30 rows, thats fine, but how can i increase the search field?

    Additionally, if i change the invoice number to include text at the beginning, this also affects the functionailty, do i need to keep column A purely as numerical or is there a way to include say 4 letters of text followed by 7 digits of numbers starting at ABCD0000001, ABCD0000002, etc.

    Many thanks for your swift help.

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Multiple search and data return formulas

    jackifizzle,

    If possible, I would recommend keeping the Invoice Number as just a number instead of adding letters to it. The formula could be adjusted, but it would get ugly fast. Attached is an updated version of the workbook. I put in 5000 random records, and am using dynamic named ranges to define where Excel looks. You can see how Excel lags with just that many records when using dynamic named ranges and complex array formulas. Like I said earlier, this is not a very efficient method for accomplishing this task. I would still recommend using Access and then running queries to get the desired results.

    However, this is still good info to learn, so here's some links to information on Dynamic Named Ranges:
    http://support.microsoft.com/kb/830287
    http://www.contextures.com/xlnames01.html
    http://www.cpearson.com/excel/DefinedNames.aspx
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-28-2011
    Location
    London, Englan
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Multiple search and data return formulas

    Tigeravatar,

    thank you for your sheet, works perfectly for what I need at the moment, need something up an drunning soon so this is a great help. Will explore Access options next year.

    I have made some changes to the cell formats, but now my search function doesnt seem to work. If i search using invoice number it is ok, however if i search using cutomers, with the invoice fields blank, I am not getting any results. Think I may have damaged something in my editing.

    Would appreciate another quick look if you can.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-28-2011
    Location
    London, Englan
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Multiple search and data return formulas

    In fact appears to be same issue when using any other search field other than invoicce number.

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Multiple search and data return formulas

    jackifizzle,

    It was because of the 'Invoice List'!A5002:G5004 merged cells that contain the text: "End of sheet, start new blank sheet"

    It caused an issue with the named range formulas which caused the array formula to evaluate to an error, which triggered the IfError() clause to result in a blank cell. You can leave the cells merged and highlighted as reminder for yourself, just delete the text and that will fix it.

  8. #8
    Registered User
    Join Date
    11-28-2011
    Location
    London, Englan
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Multiple search and data return formulas

    Ok thanks, got it working perfectly now.

    I have also created another sheet at the end of this workbook, all it literally does is pull in data from teh sheet previous, using very simple - =VALUE('Search Invoices'!B17) So all it does is display another cell from another sheet. However, if the initial sheet has a blank cell, then my final sheet displays #VALUE rather than just being empty. What can I add to the above foruma string to prevent this.

    Kind Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1