+ Reply to Thread
Results 1 to 9 of 9

Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

  1. #1
    Registered User
    Join Date
    12-14-2017
    Location
    Tennessee
    MS-Off Ver
    2007
    Posts
    17

    Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    I maintain a Travel Spreadsheet and want to incorporate searchability.
    I have it started, based on another sheets search ability on the "Search, Beta" sheet.

    With the formulas, I have, I cannot get only United States cities to list in "Possible Cities" or only Europe cities. Currently when I copy down the formula I also get duplicates but not sure where to put in the formula to not list duplicates.

    Eventually, I'd like "Possible Cities," "Possible Hotels," "Possible Food," etc to list what everything when EITHER State or City is typed in by the user.

    Hopefully I can get some help. Most of the formulas I found online and know somewhat how they work but am clueless when having to nest more parts to the formula.

    Michael

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    I would use a macro.

    Possibly a userform


    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    12-14-2017
    Location
    Tennessee
    MS-Off Ver
    2007
    Posts
    17

    Re: Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    Thanks for the quick reply. I'm not sure how I could get a samply workbook if I"m not sure how to create it in the first place - maybe I'm misunderstanding. No data in the Google doc is sensitive anyways.

    Michael

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    I dont use Docs, but it seems to me that you need to create a dependent Data Validation.

    Attached is a sample I made (using excel) that you could see if you can convert it to docs?
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    12-14-2017
    Location
    Tennessee
    MS-Off Ver
    2007
    Posts
    17

    Re: Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    As far as I know, most of Doc works the same way as Excel. A friend used Data Validation, I believe under the "interactive search." Not sure how he set it up. Was thinking this way might have a different display of information.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    OK great. See if you can "convert" what I have, to what you need?

  7. #7
    Registered User
    Join Date
    12-14-2017
    Location
    Tennessee
    MS-Off Ver
    2007
    Posts
    17

    Re: Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    I'll take some time to work through your example.
    Thanks!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    Keep me posted

  9. #9
    Registered User
    Join Date
    12-14-2017
    Location
    Tennessee
    MS-Off Ver
    2007
    Posts
    17

    Re: Searchable Travel Spreadsheet, Help with Nesting Existing Formulas

    I was unable to transpose the sample form with what I'm looking to do. I have a table that someone else created using data validation but I'd like to try something different.
    Currently I'm using: =ArrayFormula(If(isblank($A$2),"",IF(iserror(index('Travel Master'!$A$5:$H$937,SMALL(IF('Travel Master'!$A$5:$H$937=$A$2,ROW('Travel Master'!$A$5:$H$937)),ROW(1:1))-1,7)),"",index('Travel Master'!$A$5:$H$937,SMALL(IF('Travel Master'!$A$5:$H$937=$A$2,ROW('Travel Master'!$A$5:$H$937)),ROW(1:1))-8,7)))) where A2 is the input I want to search for and Travel Master is where it's searching for the keyword. The issue I'm having is if I type in "United States" and want all possible cities to come up, I get duplicates as well cities that are not part of my search criteria (United States).


    Michael
    Last edited by iflynething1; 08-09-2018 at 06:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Nesting IF formulas
    By mackan7695 in forum Excel General
    Replies: 2
    Last Post: 03-30-2017, 08:15 AM
  2. Group Travel and Hotel Spreadsheet
    By DuffyE in forum Excel General
    Replies: 6
    Last Post: 10-07-2015, 12:23 PM
  3. Programming Searchable Function - in a basic spreadsheet (for now)
    By excel.junkie in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-26-2015, 10:06 AM
  4. [SOLVED] Nesting IF and AND formulas within and existing formula
    By ExcelDummy77 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-15-2014, 04:48 PM
  5. Nesting If formulas
    By lmennucci in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-09-2013, 04:12 PM
  6. Time Sheet (Tie in extra spreadsheet to existing spreadsheet with formulas)
    By Eddie Elvis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-12-2013, 04:27 AM
  7. [SOLVED] [SOLVED] make up s spreadsheet for travel itenary
    By lilbit in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-25-2006, 04:06 PM

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