Searching For Results In Database With Certain Restrictions
May 28, 2014
I've got an question about using excel macros to search for information in a database with certain restriction. In my attachment you can see an example of the database I use.
Example.xls
I've got a database with certain packages, the length and the width of these packages is known.
I've also got a database with certain products, the length and the width of these products is also known.
I want to use a macro which allows me to see which packages are useable for a certain product. A package is useable if the length/width is less then 10 bigger then the product. So in the example if the width is between 10~20 and the length between 40~50.
Originally I thought I could use a macro with advanced filter and having restrictions width >10 <20 and length >40 <50. But I can't fit this in as macro because I already get the width and length from the product from another database and macro so it can't say >40 only 40.
I want to setup a worksheet that I can search the name of the comic book and the number of the book and have the results underneath the search criteria. I am attaching a sample workbook. On the search database sheet, I have setup how I would like the page to look.
The spots where Enter Comic Name and Enter Comic Number I would like to act as search boxes, as in Google.
I would like the Search and Clear to be buttons.
Is it possible to return the results I want? And can I keep my searches until I push the clear button? If not I will get rid of the clear button.
The last thing I want to see if possible is once I get a result, can I add a button to go to that comic books location to enter data. Or add a save button so I can edit any info right on the search page and save it in the database.
I'm trying to make a userform to show the results of a search from column A. This code will search the coulmn but when the results show it show's all ranges not just the ones that contain the string
VB: Private Sub BtnFind_Click() Dim strFind As String Dim c Dim rSearch As Range
I have created a form that will add new records to a database. On the database there is a predetermined ticket number. The "Create" form looks for the first blank row and adds the information on the form the database.
What I am trying to make happen is when a "Ticket Number" is entered in the appropriate field on the "edit" form, the "Date Raised" and "Raised By" fields populate based on the information stored on the row for the ticket number enter.
I'm trying to write a macro in Excel that will search certain values a Siebel database. I'm already working with a macro to ADD data to the database, so I have all the connections taken care of. But now I need to pull data from it. In looking for how to do this, I see the setsearchspec method that I need to use to set up the search then the ExecuteQuery to run it to get the pointer. But before that, I have to create the referencing object. I've noticed in several examples here and on a couple other sites, these two lines:
Set busObject = SiebelApplication.GetBusObject("Revenue") Set busComp = busObject.GetBusComp("Revenue")
So here's my question: in all of the examples I've seen, they used "Revenue" as the argument for these methods. Does it really matter what we use there? Or this just essentially a place holder? I've looked at some of the online documentation and was unable to glean an answer from that.
i have a spreadsheet that i use for work which has 1 page that contains all the data for the workbook. on other pages i use this data, and tables, to pull information to those pages or other pages which complete my work in a more timely fashion.
that issue i'm having is when i try to look up specific values within the data page tables and there are multiple values.
i need a way to choose different values rather than the "first" value in the instance of a vlookup which only finds the first value.
in the pictures i have picture 1 (vlookup samples) has a section where i can type in the "office" code and it will auto pull "switch name" and "switch clli" via vloookup.
in picture 2 (switches sample) i have created a table, which is on another page as stated above, but if i try to search for the example "LSGT5" it only returns a switch name value of "LFTYINXFDS0" and there is also a value of "LFYTINXFDS1".
there are many more within the table(for this office code alone) but this is just an example of how i need a way to choose the proper one or somehow have a drop down without having to create a bunch of tables for each office itself.
I have two sets of data, and one colum in each set is titled ID numbers. I would like to search both those colums and combine the matching ID numbers in one row?
I have multiple worksheets with part numbers and prices for different computer parts. On each worksheet I have multiple sets of part numbers and prices but I only need one the information from specific part from each worksheet. Luckily, the part number I need is also the name of whatever worksheet contains it. I want to take the part numbers and prices from the parts I need and put them in one master sheet called "PartsNumbersCombined". Currently I haven't even attempted to format the data in PartsNumbersCombined, I am just trying to actually pick up and move the correct data. Here is my code:
Sub harvest() ShtCount = ActiveWorkbook.Sheets.Count For i = 2 To ShtCount
[Code].....
The part numbers are in column A and the prices are one row down in column C. I feel like I am close but I can not get anything pasted on my master sheet.
I'm trying to get certain data from an export of a phone logging report for a call center. I have exported this data into an excel spreadsheet. I would now like to retrieve the following information from the sheet.
'Agent name, Date, time of Login, time of Logout (at end of day).'
I have coloured these fields in red for easy finding. I do not require anything else from this data, however, within this sheet there are multiple agent names and can span for over a month. All names and dates are required. I have attached the current sheet, and also what it will possibly look like once complete. I would prefer the whole process to be automated if possible.
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N Buyer P/N
HGFYE/12 111111
HYEYDH/14 222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N Buyer P/N
HGFYE/12 111111
HDGTEY/56 333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
I have very little experience with programming, as a matter of fact the code in the sample file is the extent of my knowledge. I want to use Excel because not everyone in the plant has access to Access, but everyone has Excel.
The idea is to allow searching for parts based on 2 different part numbers, category, drawer, manufacturer, or description (any or all).
My first problem is how to make excel search using a userform and find multiple results, not just stopping after it finds the first match. For instance if you only searched for a category it should find all results that match that category.
(I'll try to anticipate a question here: the part numbers should be unique but may not be, so using the part number as a key will not work.)
The second thing is I would like to know how to populate these results in a listbox and and have the selected part show the matching picture. (This maybe more clear after seeing the spreadsheet).
And finally, is there any way to make the comboboxes on the search userform populate themselves off of the list of data.
I apologize if any of these questions have been answered already but frankly I've been searching google for the past week and haven't found anything, (This may attest to my search skills as well, ha ha ha), and I really just want this to be finished.
I have a database of 13 columns and ever increasing rows.
I want the following to be there on my userform.
1 combobox: showing the list of categories from which to search.(The categories are the column headings in columns A1 to A13.The user will have to select one category.
1 textbox:Here the user will enter the search term.
1 Command Button: When the command button is clicked/entered, the code shall be such that it will search in the column corresponding to the category mentioned in the combobox and display the results( The entire 1 row x 13 cloumns containing the search term) in a Listbox. If the search term does not matches then a message box should appear with the message "No entries found" [b]
1 Listbox:to display the search result as mentioned above.
I have created a query in an Access database which returns multiple results. I need to be able to run this query within Excel, but only showing the total of the results in a single cell.
I also need to run it multiple times on the same sheet using different dates. The Access query asks for a date each time it's run
I have only found ways to retrieve the entire results of the query into Excel based on the settings within the query.
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered) Device (Filtered) Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
how do i protect a cell from being changed? I don't want to protect the whole worksheet, just a few cells within a worksheet? I have tried the Allow Range Users function, but cannot get it to work? Kind regards, Michele
Until lately users wanted to add comment as long as a paragraph , and it did not work any more . Is it a length restrictions , and how its possible to add longer comment .
I have tried text as length below @ 143 char (it did not work)
One Two Three Four Five Six Seven Eight Nine Ten Evelen Twelve Thirteen Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen Twenty TwentyOne
I get the results printed until Sixteen. @ 97 Character.
I have multiple tabs and each tab has an invoice. In the summary tab, I want to grab certain cells ie. Invoice #, Number of items, and total invoice amount.
The tricky part is, although the format for each invoice is the same, the product list for some invoices may be longer in some invoices (which will affect the total amount - aka total amount may be in cell F40 instead of F10).
Since I get these invoices on a regular basis, I would really like to create a macro where it will just find the items I need and paste it in the summary. I have no problems making this file work if the items are all in the same cell and format for each tab.
Is there a way that I add instructions/restrictions to the push buttons on my form (basically they perform filter functions). For instance, currently the only way to search the client or employee name is to enter the entire name. Is there a way to allow partial name searches?
Can I have a message appear that says "'Joe Shmo' can not be found".
Also I would like the input box not allow blank searches. If the input box is selected but then closed with no data entered, it filters for blanks which removes all data.
I have attached a sample of my form for you to look at.
Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc.
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database A B C D E F G 10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.
I have two cells, both of which have drop down lists for options 'a' & 'b'. When one equals 'a' the other should be restricted into only saying 'b' and vice versa. I would also like the user to be reminded that he can only select 'a' once from the two menus. Ideally I would like to do this in a formula rather than VBA?
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
I have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
I have doc with app 1000 rows of data, one col being product description. I want to simply search the entire description column for a particular string of characters and enter a 1 in a new adjacent column where there is a match. Where there is no match, I want a 0 or a blank.
I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....
