I have a table which details an applicable fee given a level of performance. However the levels of performance can be any value and the fee level table which i need to look up gives conditional ranges such as if (out performance is) >4.5% and <5.00% then applicable fee will be 0.7%. However my table is large enough that using a nested IF formula seems unfeasable. I've attached a sample of what i mean which explains it better.
View 2 Replies
I have my data arranged in a table on sheet1 as shown below.
Columns B to H contain the hours each person normally works on the days of the week Sun to Sat. In columns I to O are the hours that each person has booked as holiday for that week.
On sheet2 I would like to be able to enter a holiday day in C1. In A3 to A15 I would then have a list of all the people who will be working on that day and in C3 to C15 have a list of the people who are on holiday so the output on sheet 2 would look like this.
Basically I'm after the formulas for A3 and C3 that can be copied down the column depending on how big my table gets. I am using Excel 2010.
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
View 9 Replies View RelatedI have a workbook with two worksheets,
sheet 1 contains student data, name dob, actual age, raw score and an empty column standardized score
sheet 2 contains a conversion table, using the actual age from sheet one you locate the age in the top row of table in sheet 2, and using the raw score from sheet 1 you locate the matching raw score from the first column of the table in sheet 2, going down and acrosss until you meet this gives you a standardised score
i want the standardized column in sheet one to fill by using a formula which looks at the table in sheet two locates the two values and returns the result.
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
View 3 Replies View RelatedI have "Sheet1" with thousands lines of data like following
ID Customer value date etc,......
2434 ABC 500 2012 data
2654 DEF 600 2013 data
and another sheet "sheet2" to hilight attention to a risk or issue like following
ID description status to do by etc,....
2434 missing items follow-up and fulfill Eng. A
I need to add a hyperlink to the ID number in BOLD to link me to the line matching in sheet1.
I want to highlight particular characters in an excel cell. This cell may contain other words as well.
For example, if i search for the word "apple" in the excel sheet, only that particular word in the cell should be made bold. the cell may contain " I like apple". in this case "I like" should be left as it is.
I would like to summarize all "yes" respose contained in 15 tabs. See the attached.
I would like results to be returned to the "summary tab" when user answer "yes" in the signficant column in the "process activity 1 through 15" tabs. If the answer is yes, I would like to pull the process name, aspect and impact detail.
Is it possible to download search results in Windows 7 to an Excel spreadsheet? I searched for file names that started with a specific alpha character in a specific folder using Windows 7. The results came back with several hundred file names. I'd like to create a spreadsheet with columns for the file name and the sub folder name that contains the file. Is that possible?
View 1 Replies View RelatedI have two worksheets in worksheet 1 I have a bunch of data and I want to be able to filter out to only include relevant results.
Eg. If I search for 'Dog' I want search column A in another sheet and find all full and partial matches then display the entire row of each of these matches.
I have to extract all the entries that have certain words.
The worksheet has 600 rows, setup like
Column A=unique document number (never repeats)
Column B=Document Titles (some repeats, though the documents that are the same are not titled the same, though they have certain keywords in like documents)
Column C=Document Types
I want to search Column B for all titles containing the word/text string WOR, and then copy the row of A, B, and C when WOR appears in Column B, and put it into another worksheet.
Now obviously, I can do a "Find All" for WOR on Column B, but I can't cut and paste the results into another worksheet.
I prefer a simple formula, cause my VB and Macro skills are pretty non exsistant, though I will learn if that is what it takes.
I want to create a searchbox in Excel which will locate text in a massive amount of data, for example, if a user types into the box....
"123"
I want the search box to filter the spreadsheet using the autofilter from cell B3, thus filtering out all results that are NOT "123".
Currently I have a button to press which brings up the CTRL + F screen, but that isn't exactly what is required in this instance.
I've put together a workbook that has 2 sheets that contain stock details and location info (i.e. part number, description, Location & stock level). I need to have a search function outside of the 'built-in' Ctrl+F (or Edit > Find) search function, i'd assume this would mean using VBA?I invisiage making a userform embedded within the very first sheet of the workbook and having this form be opened when accessing the worbook - maybe hide the other two sheets containing the data? Within the search userform, i would have a 'part number' & 'description' search input boxes for data input, either one could be blank but atleast ONE must be inputfor the search to work. On pressing the 'Search now' command button in the userform, any reults found would be displayed in a listbox at the bottom of the said userform. If no results found, a dialog box would pop-up saying 'No Items Matched"
View 7 Replies View RelatedAt the end of a long macro, I want to search the value in Column A and if value = 0 then it'd return the values in adjacent cell to the right in a msgbox.
All that is returned is the text I inputted.
What I have so far (I picked it up on the internet):
Code:
Sub Macro2()
Dim msgaddress As String
For Each c In Range("A:A")
If c = 0 Then msgaddress = "Portfolio Deal with invalid ID:" & vbNewLine & ActiveCell.Offset(0, 1).Value
Next c
End Sub
I have a workbook with two worksheets.
On Sheet1 I have two columns 'Date' & 'Serial Number'
I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.
I have a spreadsheet with 2 columns, 'A' is the property number and street name, and 'B' is the year the work is going to be done on the property.
I have a form with a textbox and command buttons, now what I want to do is allow the users to enter property number and street in textbox1 and click commandbutton1 to search. If it finds a match I want a small message to come up and say either the year the work will be done (column B) or 'no match found'.
I am trying to populate a UserForm with data from a spreadsheet using a search function instead of just providing a Next and Previous search function (which works). The following code is able to search for and select the correct row on which the data resides.
Private Sub cmdBlankFind_Click()
Dim FindMe As Variant, FindCell As Range, FindCell2 As Variant, Data As Variant
With Range("BLANK")
FindMe = InputBox(Prompt:="Please enter search criteria:")
Set FindCell = .Cells.Find(What:=FindMe, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
FindCell.EntireRow.Select
Data = FindCell.Value
tbxBlankAccount.Value = Data(1, 1)
End With
End Sub
Is it possible to combine search results into a single cell?
On my spreadsheet I'd like to select from 4 columns and combine the result into 1 cell. I'd like to search on equipment number in column K that meets a condithion in column H, if this is a match I'd like to show the result date in column P and the duration in column R. Columns P & R shown in 1 cell...
Sort of like this... if "K" = "CV537" and "H" = "0" then show the resulting date from "N" and duration from "R" in one cell somewhere.
Possible?
I am trying to create a search where the user types into the text box 'ItemDescription' then hits the 'ItemDescSearch' button (see below code) to pollute the list box 'lbSamDesc' with any partial matches from the specified range. Currently when I click on the button it takes about 8 seconds then no results are displayed in the List Box.
View 9 Replies View RelatedI 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'm using a userform to report on set of worksheets. Two of the functions are:
- reporting on the number of contacts with a client
- displaying the details of all contacts with selected client in one textbox.
I can't work out how to concatenate the text of all the contacts relating to one client in a text box (txtContactHist) on the userform.
Using the countIf function I'm trapping zero matches.
The worksheet containing the contacts has the following data columns:
Col A - Customer ID
Col B - Detail of contact
Col C- Date of Contact
Columns B and C contain the data I want to capture
Column A (the customer ID) is the column that is searched for a match ( via the userform text box (txtLic.Value))
To work out the total number of contacts, I use this bit of ...
I use the Macro below to search in a datafile and copy the results into another worksheet. Only problem, when the macro finds a result he should copy and start over again, starting from the row below, but somehow he start 2 rows lower, so I'm missing results. (when he should copy row 2,3,4,5,6 - the result is only 2,4,6) I tried changing
WerkRij = WerkRij + 1 into Werkrij = WerkRij
StartRij = Rij + 1 into StartRij = StartRij
but then he keep searching and copying the same cells
Any way to use a search form I've created to delete data from the original sheet. What I'm doing here is using advanced filter to copy data from the original database onto a temporary sheet to display in this list box. I've gotten almost everything to work properly, but since this is populated by advanced filter, I don't really even know where to start on my delete button.
Code:
Private Sub Search()
Dim Criteria As Range
Dim SearchRange As Range
Dim SearchResults As Range
Set Criteria = Range("Values!Criteria")
[Code] ......
So I was thinking something like this:
Code:
Private Sub cmdDelete_Click()
lstResults.Value.delete
End Sub
But I know this won't work.
I'm trying to make a macro to search in a list of indeterminate results, and when found, to count how many found results and select the ones found, and then to put the results in the cells I2 and J2
I already have this code, but is a bit out of what I want.
Code:
Private Sub CommandButton2_Click()
'dimensiona as quantidades de cada criterio
Dim c1, c2 As Long
[Code].....
This one open a MsgBox with the results, and i want the results on the cells I2(what it was search) I3(how many it was found) J2(what it was search) J3(how many it was found)
And this one also dont "paint" the cells with the results, and this one only search if you put the rigth sentence... Ex: if i search for "miguel" and the cells have "Miguel" it won't count :S
On my attached example (ProjectDBTrial.xls) I have a number of sheets. Data containing all the relevant project data, Search which functions similarly to a MS Access Query (this is where the coding is) and the other relevant sheet is Portfolio_Dash which will end up being dashboard.
My aim is to allow the Search function to be controlled from the Portfolio_Dash Sheet. For example - if the user were on the Dashboard and he chose the project "Magnus". This would then select Magnus in the Search/Query, populate all the Magnus data in the search and then the Dashboard would take the information straight from the Seach Sheet.
At the moment the code (seen below) requires me to select the project on the search sheet drop down. Also a bit of a bug is that as soon as I click off the Search sheet and back on it resets.
Any way to control the search function from the Portfolio_Dash sheet. The reason I cannot Make the Porfolio dash sheet the query is it has to have a very specific graphical template applied that will not work well with the function built in.
CODE:
Option Explicit
Private Sub Worksheet_Activate()
[c3] = "Type your search here."
[c3].Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
[Code] .........
I am trying to find a way in which a used can enter text into a cell (effectively the search cell) and then that text is compared to text in 3 or so columns, if the text matchs any part of the cells in the those columns then the spreadsheet should filter out the rows that dont have a match and only leave rows that have a match.
Its like having a spreadsheet with a list of part an user can put in 55 or bolt in the cell at the top and the list would only then show all the rows that have 55 or bolt or whatever text in the table below?
I was looking at this VBA code the other day and i was wandering what i meant. I thought if somone could expliane line by line,
Sub CommandButton1_Click()
Dim ThisAddress$, Found, FirstAddress
Dim Lost$, N&, NextSheet&
Dim CurrentArea As Range, SelectedRegion As Range
Dim Reply As VbMsgBoxResult
Dim FirstSheet As Worksheet
Dim Ws As Worksheet
Dim Wks As Worksheet
Dim Sht As Worksheet
I am creating a database using excel and I've created a userfrom for ease of data entry and searching the database. But now I am stuck at the searching part.
The sheet is Sheet1 and the userform is UserForm1. Based on the pic that i have attached, user needs to key in the keywords in any of the textboxes and comboboxes in the group box labelled "Organizational detail" and when he/she clicks on the Search button, the userform will display the whole rows where the search results reside onthe listbox. When the user click on the search results on the listbox, the textboxes and comboboxes will be updated with the data on the listbox.
I have a workbook with 50+ sheets. Within each sheet are rows of data in column A that I'd like to search for specific text.
I'd like to search each row from every sheet for specific words (e.g. "7 days" AND "Monday" AND "Tuesday" etc.) then copy the entire row containing all my search text in a new sheet on column A along with the name of the sheet it was found in in column B and the row number it came from in column C.
What I am trying to accomplish is to search through all the sheets and post results in new sheets for each search string.
