I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers() Dim managers1 As Range Dim names1 As Range Dim n1 As Variant Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
I have a monthly report showing actuals and budget for current month (or quarter) and year to date. Range names have been created for (1) each month, (2) each quarter and (3) year to date). I'm using the SUMPRODUCT function to extract information by account. For example:
This formula works except I have to replace the "ACT_Jan" each month accordingly. Can a data validation list be created with the range names so that updating this formula monthly can be simplified? For example, changing "ACT_Jan" to "ACT_Feb" with a drop down list.
I've written a procedure that loads a list of country selections in a dropdown (see attached/vb editor/ThisWorkbook).
The problem is the valid values for this column need to be just the two digit country codes, not the country code +dash+country name. The reason the dropdown selections include both the two digit country code and the country name is because most users will not know the two digit codes. Presenting the selections in this fashion will make t much easier for the user to make the proper selection.
So here is what I need: When the user makes a selection from the dropdown list, the resulting value needs to be the two digit country code only. Is there a way to incorporate this formula .... =MID(B2|1|2) ... in the vba procedure which runs after the user makes a selection? Or, is there another way to accomplish this?
I attach an example worksheet with the code I have thus far.
In my workbook I have other sheets, one of which changes and updates a specific one each time a new client's data is entered on said other sheet. Because I want to save the specific client's data and not lose it when another client's stuff is entered on this other sheet, I copy the sheet where the data is summarised (I called this sheet "Sheet to Copy From") to a newly inserted sheet and use Paste Special, Values Only to change all functions /f ormulae / Links ect to values.
I then change the name of the sheet to the name of the client.
I then use this sheet name / cell value to polulate a range on another sheet (Next Empty Cell) as a Hyperlink to the newly created sheet above. This sheet I called "Table of Contents".
how to code the hyperlink. Using Macro Recorder uses the specific case's names, but the Tab name to be used as Hyperlink value will always be the name of a new client,
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
Have a list of aprox 50,000 names. in that list names may repeat themselves multiple times. Is there a way in excel to extrapolate from that list of 50k to display only a single instance of each name in a separate Colum?
I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:
Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.
What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.
I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was
So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.
My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.
Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?
I have a worksheet where a number of the cells have been assigned names. I want to copy these cell names & create a list on another part of the worksheet. Does anyone have some code I could use to achieve this?
I'm trying to sort out a list of names from a website that publishes names in the following format:
DOE John VAN GOGH Vincent DA VINCI Leonardo NADAL PARERA Rafael JIMENEZ RODRIGUEZ Miguel Angel
What I'd like to do is get the names in the following format
John Doe Vincent Van Gogh Leonardo Da Vinci Rafael Nadal Parera Miguel Angel Jimenez Rodriguez
Basically all the last names - which are all capitalized - would be moved to the end of the text string. Of course any leading spaces should be removed and I guess using the Proper() function, all capitalized words could be capitalized in a standard way.
I found the following function, here: [URL] ...
but what it does is just take the capitalized words and separate them into a separate cell, which is not all of what I want.
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?
The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.
I need to create a function that selects 4 names randomly from a list of 15 names and displays the 4 randomly selected names in the one cell. Also, you cannot repeat the same name in that cell, (i.e. bob cannot be selected twice in his group of four)
I'm trying to achieve is to write a macro that can search a column of dates then open new worksheets according to the months that are present in the column of dates. So, for example, if the column has dates ranging from January to June, I need the macro to open 6 new worksheets and label them January, February, March, April, May and June.
I have 8 different workbooks which is 100% similar. Only values registred on each sheet is different. the format and rows/columns are the same.
The sheets is something that changes, when I want to improve layout etc. I have one Template book where I do the changes and try to export this to the other books (call it upgrades). For macro moduls it works nice, just replace.
The problem is the sheets. When I have changed a sheet, replace it, it would not adopt values from the other sheets.
Ex. In sheet 2 I have named the cell 'B60' for U_KU in the new sheet 1 I expect at using =U_KU in cell I7 would adopt the values from B60 in sheet 2. But nope.
I can see that there is an link to the template workbook in edit ->linkage. Would this cause problem?
Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.
When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?
The distributor names I'm using are in column AF.
Sub FormatList() 'The code below creates and names a new tab for each members info Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Sheet1") Set rng = Range("Database")
'extracts a list of member or distributor names ws1.Columns("R:R"). AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("T1"), Unique:=True r = Cells(Rows.Count, "T").End(xlUp).Row
'set up Criteria Area Range("U1").Value = Range("R1").Value
For Each c In Range("T2:T" & r) 'adds the member name to the criteria area above ws1.Range("U2").Value = c.Value ...............
I'm trying to create a spreadsheet that has a data validation in it. I have a drop down list that has to options to select; Yes and no. Every time I create a new row to add more information I have to go through the process of creating a new data validation for for each cell in this coloumn.
Is there anyway of creating a way that every time I insert a new row, a data validation list box will appear in the coloumn that I want it in?
I need to compare two lists of client names. One list has 59k names to be compared to second list of 1000 names. The list of 59k are listed in column D and the other list is in Column E. I tried conditional formatting but unfortunately some of the names are slightly different I.e. fair point communications vs fair point communications inc. I was trying to put in v lookup with a trim function but it didn't seem to be working.
I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)
I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.
I've tried a few things to make it happen, but nothing has worked.
I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.
I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.
Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?
I have 100s of name ranges in my spreadsheet. I am using a series of drop down boxes for the user to select the info that they want to see. Based on the dropdown box, the macro "dynamically generates" a range name from which to pull the info.
For instance, if they Select Manager = Terry, Analyst 1, and Month = Jan, then the range name would be JanTA1
The if statement would dynamically create this name by concatenating the info
If manager = Terry mnger = "TA" if analyst = 1 anlyst = 1 if month = Jan mnth = "Jan"
rangename = mnth & mnger & anlyst
So, I am dynamically creating the range name based on user selections...then to display it, I want to set the display cell = to the value
I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the
Const lngLastPossRow As Long = 65536 Dim strDataRng As String strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _ strDataRng
While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).
I need a macro that will look for a list of sheet names - like Albany, Houston, Denver - and return the value of a particular cell - like B25 from each sheet and display it in a column of cells on a summary sheet.