+ Reply to Thread
Results 1 to 7 of 7

How do i enable my form to search other sheets for multiple criteria upon a click

  1. #1
    Registered User
    Join Date
    11-25-2016
    Location
    Warrington
    MS-Off Ver
    2010
    Posts
    60

    How do i enable my form to search other sheets for multiple criteria upon a click

    Hello!

    Firstly I apologise for the vague title!

    I have a spread sheet which I a using as an individual user booking form, to explain the background I work for a company which requires "floor walkers" to aid other members of staff.
    To ensure the planning of this time is correct and that a floor walker cannot be double booked I created a "booking form"

    Currently the person wanting to book the individual looks on a preloaded sheet for the month they want "January" for example.
    They then look at the individual peoples "availability" and then return to sheet 1, which contains the booking form, the person inputs the name of who they want with the date and time and then hits submit.

    I currently have a macro that copies the booking form information and then pastes it into a "collation" sheet as well as emailing the "floor walking" team to state who has been booked and for when / what.

    I then have to manually go into the form and go into the month and change them from "available" to "booked out".

    So that's the background covered...

    here is my question!

    Is there anyway of me building in something so when they hit the "submit" button it firstly checks the month which has been entered for the word "available" if "available" is true then allow the code to run and then change that cell to "booked out"?

    to try and explain the spread sheet I have attached a copy to try and help make sense of what I am asking (the code might not work for you guys but the form is only to see if my idea is possible?) its also password protected but you can still see the code and the pages.

    Any advice would be greatly appreciated
    Attached Files Attached Files
    Last edited by ahalliwell; 05-28-2017 at 03:43 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How do i go about executing an idea i have

    Quote Originally Posted by ahalliwell View Post
    Firstly I apologise for the vague title!
    Since you know it's a vague title, please take a minute to provide a title that tells us something about your question, according to our rules.

    Your post does not comply with Rule 1 of our Forum Rules. Please update your title to tell us something about your specific question. Describe your problem, not your anticipated solution. We ask this for two reasons:

    1. Members scan the list of threads to decide where they can help. The title helps us decide which questions we have the knowledge to solve.
    2. After a question is answered, the title helps people with similar questions find it in a search.

    Use terms appropriate to a Google search. Examples of poor thread titles are Please Help, Urgent, Need Help, Formula Problem, Excel Question, Code Problem, and Need Advice.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-25-2016
    Location
    Warrington
    MS-Off Ver
    2010
    Posts
    60

    Re: How do i enable my form to search other sheets for multiple criteria upon a click

    All done, still seems vague as my question is too large for the little box but it's kinda says more I think?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How do i enable my form to search other sheets for multiple criteria upon a click

    Hi and welcome to the forum, and thanks for changing the title.

    You've undoubtedly put a lot of work into this which is commendable, but since you ask for advice here it is.

    As I seem to say more and more, you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

    A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.

    You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
    You will also throw open the whole wonderful world of the powerful Pivot table functionality. To this end individual sheets for things like months, departments, products, ..etc should be avoided. All data should be contained in a single table.

    So before you get too far with this I'd seriously rethink my strategy, The essence of what you're trying to achieve is to know which floor walkers are available. Hence I'd create a table that contained details of all floor walkers that have been booked. i.e. it would have columns for

    Date
    Name
    Time Slot.

    Then the way I'd organise the system would be to have a form similar to what you have now which would be used to 'ask' whether a particular person was available. The system would look for that person/date/time in the 'booked' table, and if no match was found then that record would be added to the table. Obviously if that person was already booked at the time requested the user would be asked to select again.

    A similar or even the same form could be used to delete an entry on the booked table.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    11-25-2016
    Location
    Warrington
    MS-Off Ver
    2010
    Posts
    60
    Hi,

    Thanks for the speedy response, i see what you are saying and it does make total sense, the form initially was set up to trial the idea and now it has taken off and people want me to upgrade the form so i think i will in a sense start again as you suggest.

    Thanks for your advice!

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How do i enable my form to search other sheets for multiple criteria upon a click

    Yes I'm sure you'll find that approach far simpler.

    I should have added an extra 4th field in the database table I suggested. This should be a formula which concatenates the other 3 values. i.e. assuming the field headers are in row 1 add in D2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will make searching the table much easier since all you need do when someone formulates the request for a date/name/timeslot is to concatenate those separated by a _ and use a MATCH function to find the concatenated string in column D of the database. If a match is found you know that person is already booked out on that date & time and you can can code the system accordingly.

  7. #7
    Registered User
    Join Date
    11-25-2016
    Location
    Warrington
    MS-Off Ver
    2010
    Posts
    60

    Re: How do i enable my form to search other sheets for multiple criteria upon a click

    Brilliant, Richard your a genius, I didn't think of doing that!

    Thanks for your help

+ 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. Have no idea how to get this done. Please help.
    By Leonard99321 in forum Excel General
    Replies: 4
    Last Post: 08-17-2013, 06:03 AM
  2. No Idea What I Am Doing
    By Phil8872 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-10-2013, 02:15 PM
  3. Need help please, no idea how to do this.
    By ElTeniente in forum Excel General
    Replies: 2
    Last Post: 04-15-2011, 10:39 AM
  4. Please Help, no idea
    By JayMill in forum Excel General
    Replies: 6
    Last Post: 10-27-2006, 02:33 AM
  5. Any idea?
    By nempo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2005, 11:05 PM
  6. know possible, but have no idea how
    By compound in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2005, 01:17 PM
  7. [SOLVED] any idea
    By CC in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2005, 12:06 AM
  8. any idea??
    By NF in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-23-2005, 10: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