Hello everyone,
I am looking for some assistance, in coding a spreadsheet to accommodate/include a search function AND (IF) statements...
If I'm barking up the wrong tree, then please let me know - but i'm sure excel can support these functions.
Please let me know if you need any more information - and i'd be happy to expand! Thanks in advance guys - I can always count on this site for help!
MikeWorkPermits_TestSheet.xlsx
Hi Junkie,
What is to be done with the search results?
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Take a look at this and see if we're on the same path.
David
(*) Reputation points appreciated.
Sorry for the late reply - this is kind of coming together with your efforts - much appreciated.
The search function will work great, if instead of searching for a date ("feb 3"), there was a drop list of months and a second for days - to be able to search the master list. What you did for "search by description" is excellent! Love it!
Would you have any idea as to how I can write an IF statement to copy cell A1-J1 to another sheet - IF - a selection is made? Specifically, if I were to create a drop box instead of the check box - would it be possible to write a statement to copy ALL hot work to a secondary sheet, and all High Cautions to a third sheet?
Yes of course. However, while they are pleasing to look at, the coding for the check boxes is a little hairy. You'll have to code their creation and the linking of each one. Because, when you get to the bottom of the list and you need more lines, you just can't copy and paste new ones and expect them to work with the underlying code. (This is more or less true for those expert lurking in the wings.) This is true for ANY control.would it be possible to write a statement to copy ALL hot work to a secondary sheet, and all High Cautions to a third sheet?
You would be much better off assigning a validation list with Y/N, use a Winding "A"(a checkmark), or even just Y/N. If you are going to be the owner of this list, then simpler is better. Then you can use Advanced Filter form the menu and create a list as you describe.
I can write code to do what you ask, but I'm just trying to get a feel for how you are going to use the database. I don't want to spend a lot of time creating a Ribbon with macros to perform multiple task just to have to say, "Oh, I'll just use Advanced Filter."
You still don't say how you are going to use the date lookup function. Will you only be looking up Start or End times or the range between start and end? Any when you find them, what do you want to do? Be taken to the record, like the description function? Copy all the founds to a new sheet?
Hi there - Thank you for taking the time to help me and walk me through this process.
In a nutshell - all i need is a database that will support 4 categories (Name (initiator)), Start Date, End Date, and a description) - to then be applied a work permit number (starting at 0001, and ending at 1500). The difficult part, is that the database will then need to be split into either a hot work permit, or a high caution work permit - depending on selection. I'm starting to think a userform may be the way to go - rather than the original idea of manually entering the data, and expecting it to be sorted into 2 different spreadsheets. If it could get it to apply on a "master sheet", and then be pushed out to whichever of the two databases (hot work/high caution) is selected.
See below...and I've deleted the search boxes for the start date and end date - that was all bonus stuff that is not necessary.
See here (had to use tinyurl - due to size restrictions)
Let me know if this is making any sense...
Will the priority be selected at entry, or later? Can both priorities be selected? or are they mutually exclusive.all i need is a database that will support 4 categories.... a hot work permit, or a high caution work permit - depending on selection.
Will the permit numbers be assigned by entry or prior to entry?
Dates: Do you want to pick a complate date. It's easy to split it up for the worksheet. (But harder when we start searching for it.) It's easier to store the whole date, then when you display a userform, show it seperate.
No, actually, splitting it is the easy part.The difficult part, is that the database will then need to be split...
Hi there. Looks like my last msg didn't post properly...
I suppose it'd be best to leave this up to you. Excel is a great tool..one I'm quite comfortable with, although I'm only recently exploring the more advanced side of the programs capabilities (vb,macros etc).
I'd like to answer your questions above by providing you with a little more context for you to grasp my hopes from this project. I truly don't have any expectations on what/how it looks...or how it functions for that matter. As long as it encompasses the following:
1- each entry is provided a number (in numerical order starting at 0001 and ending at 1500)
2- each entry includes the name, start date, end date, and a description - to be plotted on a master sheet
3- each entry on the master sheet has an indication as to whether it is a hot work permit, or a high caution permit
***bonus points if I can search by description (done) and able to automatically sort the entry into a second (list of hot work permits) and third sheet (list of high caution permits).
I hope I'm not coming across snotty...I really do appreciate the help! I've been trying to learn as I go and it is incredibly frustrating!!
Priority is mutually exclusive... Can only be one or the other, and must be done at the start. Permit numbers can be provided before or after... As long as the first entry receives "0001", second "0002"....twentieth receives "0020" etc.
The dates don't need to be searchable... That'd only be a bonus to the project. If your enjoying the challenge - feel free, as I have quickly learned that I am out of my element when using vb.
Each permit can be started and ended on any given day. One might be for a day, another for a week, and another for the full calendar year.
My thought on a user form, would that it'd be easier for the user to enter the data into the user form - and would help lower the risk of inadvertent/accidental errors on the master sheet. Enter on form, be provided a permit number...done! My task is to manage the process...administratively during audits/record searches etc.
Correct me, if I'm wrong, but you still haven't answered my question of post #2
Hi Xladept,
Sorry for missing you question -
all I needed was a search tool, to be able to search by keywords used in the "description" cell.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks