+ 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
    5,822

    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.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  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 2000/3/7/10/13/16
    Posts
    44,515

    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 some-1 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 2000/3/7/10/13/16
    Posts
    44,515

    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 2000/3/7/10/13/16
    Posts
    44,515

    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)

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