Free Microsoft Excel 2013 Quick Reference

automatically linking after using a list menu

so i have this general email taht i want to send to everyone but in the email
there are specifics that i change using 2 drop boxes. but when i do use the
drop boxes there is another colum which should automatically update after i
have chose certain specifics from the dropboxes

Post your answer or comment

comments powered by Disqus
Dear all

I've got a situation where I have a lot of underlying Excel files linked to a single Excel report. I'd now like to make it easy to navigate in the Excel report by using a combo or list box, and I'd like the content in a worksheet to change depending on what the user chooses from the combo/list box.

There's an area of cells in one of the worksheets in the Excel report which I'd like to change depending on the chosen item in a combo/list box, and there's about 30 equal (though with different content) underlying files for this cell-area. What I need is guidance to is the VBA code that can solve this.

I'm not even sure whether I should use a list or combo box, so I've got a pretty long way ahead of me... Any help will be highly appreciated on this.

Ladies & Gentlemen,

I have borrowed some code from David McRichie's site (see below). The code creates a new worksheet based upon a name (in my case a Stock ticker) entered into a column, It also ignores duplicates and places the stock symbol in cell 1A of the newly created sheet.

I can get it to run fine when I manually enter a symbol in the target column, but I need it to work via a copy / paste using a list of stock symbols. Again, it works fine if I copy just one, but it causes an error when I try and copy a list. Also, how can I get this code to copy a customized template(from within the same workbook) and not just add a sheet?

All the Best!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newSht As String, oldSht  As String
    Dim wsOld As Worksheet, wsNew As Worksheet
    If Target.Column <> 1 Or Target.Row = 6 Then Exit Sub
    oldSht = ActiveSheet.Name
    Set wsNew = ActiveSheet
    newSht = Target.Text
    On Error Resume Next
    If Err.Number = 0 Then    'sheetname already exists
       Exit Sub
    End If
    On Error Resume Next
  'Create New Sheet
    Sheets.Add After:=Sheets(Sheets.Count)  '-- place at end
    ActiveSheet.Name = newSht
    Set wsNew = ActiveSheet
    wsNew.Cells(1, 1) = "'" & newSht  'name of new sheet into cell
  '  Sheets(Sheets.Count).Activate  'try to show last tab
End Sub


I have basic understanding of macros and i would like to use a list box, and perhaps with the help of a button, to allow me to goto a particular sheet in the workbook (assuming i have the names of these sheets in the listbox)

Would you be able to advice me?

Thanks very much!

I am trying to create a dynamic chart using a list box. I want the Week Selected from the List box displayed on Cell E4 and the corresponding values for the selected week populated. My Data is on Sheet titled “Data” and my Dynamic Chart on the second sheet. So the Chart changes based on the Weekly Data.
Please find attached the document for better clarifications, i will appreciate any help/ clarifications on how this can be achieved from this forum.

Thank you

I'm starting a new job and I would like to create individual folders for each of my workers about 250. Can I create folders using a list of names in either excel or what ever? I'm using microsoft outlook.

Normally I send mail to other staff by VBA command :-
ActiveWorkbook.SendMail recipients:="aaa","bbb","ccc"

when I have to change some name, I must change direct in a module.

If I want to use a listing of name in worksheet which is easier to edit some name.
Like : Column A
How can I Coding it? Can I use "array" in this case?

Hello everyone. I'm looking for a way to use a big list to search a bigger list for a match, then cut all those matching field to another sheet, and have a unique small list left.

What i'm trying to do is to use a list of completed tickets and find a match for it in a list of charged tickets, then remove that entry from the completed tickets. At the end I will have a list of completed tickets that I haven't charge yet.

Thanks for your help.

I'm trying to update all of the Named Ranges in a workbook using a list in a spreadsheet and code. This is so I don't have to do them individually and there is over 300!!

I have tried to use the same syntax as appears in the Names dialog box but the ranges do not appear correctly and so will not work.

Any help with this would be fantastic as the cariable option isn't working!!! Thanks

I am trying to use a list of names (first, last) in a colum in worksheetA to
find corresponding entries in an imported worksheet B, Once I find the name
in worksheet B I want to extract the entire row of data into another new

For example
Worksheet A:
Column A
Bill Jones
Fred Smith

Worksheet B:
Place Overall Name Swim Bike
Run Finish
1 12 Bill Jones 24 anytown ST 13:45 54:45
23:00 1:34:45
4 78 Fred Smith 56 anothertown ST 15:00 56:12
24:34 1:48:34

The data in worksheet B in the Name column is all in the same cell (not
different columns)

Now if there is a match between worksheetA name and the name exists in
worksheet B I want to copy the entire row from worksheet B into a new row in
Worksheet C. Not all names in worksheet A will have an entry in worksheet B

Any ideas?

Hi, I am in need to know how to use a list such as in Column b and use those values to plug into cell A1,then spit out the results beside each value in column C. Im not sure if this is possible or not, but I appreciate any help.

Also, the formula in A1 is very complicated and refers to several other cells so I can not just drag it down. Thank you again!

I want to use a list in Excel 2000 as parameters in MS query.

I know it is possible to use one cell in Excel as parameter in MS Query, but I have 30 items I need to use as parameters.

Does anyone have some ideas for this ?

Thanks a lot

I have a list of sales and sales people (see attached excel file). In my head I know that certain reps only sell a certain product. I just want to count the number of sales made for each rep by date and by product.

For Example:
Scott - Product1
Cory - Product1
Sam - Product2
Ryan - Product1
Jerret - Product2

I don't want to use an equation like:
because the real problem is much more complex.

I wish I could say:
but of course this doesn't work.

Is there a way I can use a list of names in a single countifs function or some other function?


How do I get the colors to change automatically when I use a drop down list.
Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of
that cell to change its color to "green." And if later I change the "word" to
another from the drop down list, it will change it's color to a specified

The drop down list that I use was created from cells that have the colors
already in the "word", but I do not know how to make the list show the colors
so it puts the word & color automatically in the drop down list to the cell
with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,
as I don't know fully the capabilities of formulas or vba.

How do I get the colors to change automatically when I use a drop down list.
Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of
that cell to change its color to "green." And if later I change the "word" to
another from the drop down list, it will change it's color to a specified

The drop down list that I use was created from cells that have the colors
already in the "word", but I do not know how to make the list show the colors
so it puts the word & color automatically in the drop down list to the cell
with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,
as I don't know fully the capabilities of formulas or vba.


I am looking to create a listbox in a form that can only select the patients that have not been admitted.

I can select the rows that have the blank cells in the current region, not sure how to tie this in with my list box in the form though. I can't see any property in the list box to link to a range, maybe I have to use a combo box instead?

I have one worksheet in my workbook with several different pictures of
furniture, among other data. I have set up a template on a second worksheet
to pull in the data in a formatted page using a list dropdown and several
different formulas on the page. I would like to know if I can use a fomula
on the second worksheet to automatically pull in a picture of the item from
the first worksheet. Is that possible? Is it easier to do it a different
way, i.e. maybe put all pictures in a directory on my hard drive and have
them pull in and display from there? Please help!

Ok, so this is pretty much a general question about using a list of names on a worksheet and then referencing them to carry out a macro.
If I have a sheet which I want to copy data from named Brown and I select this a range of data on this sheet in the workbook and then I go to the same named sheet in a different workbook and copy the selected range of data across to the same range in the other workbook. How can I either reference a list of names OR use a variable so that the macro runs through all the sheets in one book and copy the data to a range in the other?
The latter would be the easiest to maintain BUT I am open to an explaination of how to do this OR even a link or book I can reference to show me a code to do this. I have Excel VBA for Dummies and have not found this type of reference in it. Below is what I have with named books, I want to make this either reference a list or run through all the sheets in the first book and copy across to the second book.

End Sub

Hi All,

I'm looking at making a script that will automate the task of searching an entire Sheet in Excel for certain sequences of letters and replacing them with a desired set:

For instance (I will edit the script as required to enter my entire list):
Each instance of "ama " to be replaced with "AMA "

And so forth:
"so-dimm" to "SO-DIMM"
"nforce" to "nForce"
"dual core" to "Dual-Core"
" )" to ")"

The words that are used in the search must be case-insensitive, so that it can pick up "cpu" and also "Cpu", etc. The corrections must be case-sensitive.

The spaces are also important. For instance, if it searches for "ama ", it should only pick up "ama " if there is a space after it. So the word "amazon" would not be replaced with anything.

Any help would be greatly appreciated . If I haven't made myself clear enough here, let me know and I'll try to make my explanation a bit more succinct.

EDIT: Also, if possible, I'd like to be able to have a list for searching where it IS case-sensitive as well. So, I'd be able to search for "Amd" without finding "amd" and only "Amd" would be subject to replacement with the corresponding correction.

I would like to keep the drop-down menu created when you make a list in
Excel 2003 visable, even when you dont have a cell active in the area
the list has been created. It seems the default behavior is to hide the
drop-down menu unless a cell in the list area is active.

I have another document here that somone else created (dont know who)
where the drop-down menu for filtering using a list is ALWAYS visable.
Its driving me nuts trying to find out how it was done.

If anyone knows i would be very grateful.


We have a bi-weekly timesheet in excel that we need to enter our daily work activities from a list of 33 types of activities. Each activity has a 4 column set of codes stored on the codes sheet that needs to be entered into the timesheet for our budgeting department to know where the funding comes. Manual entry and even copy and paste from one worksheet to another is resulting in many errors. I want to create a look up list that will allow the user to pick the work type and have the 4 columns fill in automatically from the codes sheet.

I tried using Data Validation but I can't seem to fill more than one column at a time and I want al 4 to fill with one selection .... Any ideas how to do this?


Hi there,

I have a spreadsheet that tracks the audits conducted on projects. The same spreadsheet is also used to create graphs based on the audit input. So when an auditor completes and audit on a project, they input key details in a worksheet, which then gets translated to data to generate the graphs. It picks up primarily the operation centre and creates graphs for average audit scores and whether the results are red, amber or green.

At the moment i am having to manually check to see if new operation centres have been added, so i can expand the selected data to edit the graphs. This takes time each month!

Is there anyway to set the document up so when a new operations centre is keyed in, it is automatically added to a list in RED font, so i can easily identify the newly added operation centres? An example document has been attached. I need for the new operation centre to be added to the list in column AN in the BU Data and Graphs tab in the attached document. This will come from input in the Audit Results tab (column C).

Any help will be GREATLY appreciated

I've been working on an Invoice Creator through a system of userforms so that we can create invoices, store them into a database and then view customer accounts to see what invoices have been settled.

So when you click on View an Account, you select a customer from the dropdown list.
This puts the relevant CustomerID into the cell, "Search_CustID" (E12) on the setup page.

Then when you click the "View account" button it brings up the ShowAccount userform.

I've got a multipage element in there and it first loads up on the Customer details tab and has all their details. There's also a page "Total Invoices (Num)" where Num shows the total number of invoices. This works by searching the InvoiceDB sheet for all invoices from the CustomerID and pasting them into the temporary spreadsheet "InvoiceSearch" and then doing a number of rows count to get the results. The same is then repeated for the tab "Outstanding Invoices" which are basically, invoices that have not been settled yet. So it uses "InvoiceSearch" and searches for any that are listed under PAID as "NO" and copies them to this sheet and displays the number of results.

If you click onto "Total Invoices (Num)" there is a list box which links back to the InvoiceSearch sheet. It puts all the reference numbers in the list box and when you click on the reference number, it populates the other fields so you can get the details on the invoice.

If you click onto the "Outstanding Invoices (Num)", there is a similar set up, but it shows the total amount of money outstanding over all the invoices. If there are none outstanding, there will be none to choose.

This works perfectly... when there is a mixture of outstanding and paid invoices (try clicking on the account for Stifford Clays and you will see it working fine).
If all invoices are paid, for some reason, in the total invoices multipage, there are no results to choose from, from the listbox, even though when you click on the InvoiceSearch spreadsheet, there are results. You can see this if you try the acocunt for Ramsden Hall or Millhouse School.

It has to be something to do with my coding but I'm pulling my hair out over this... so I'd appreciate if someone could help me, thank you sooooo much.

For the past week or so I've been compiling a list of part numbers for my boss for a number of modules that need to be built for a project we have. However, now that we have the part numbers, we need to tally them up so we know the total number of each part to order. There are several hundred parts listed, many of which are standard parts that repeat themselves throughout the list. I could use a COUNTIF function with the text string of each individual P/N (since each P/N is unique to that part), but doing this for several hundred parts, even if many are repeated, would probably take hours. Is there a simple way for Excel to simply take a column of data and automatically count the instances of each entry? The person who posts a good solution wins +100 internets, and my hearfelt thanks.

Hi There

I am trying to find the name of this feature

Picture 17.jpg

It's a sort of drop down box that selects data from a list elsewhere in a workbook

I have used this before but cannot for the life of me remember how I did it.

Have searched for hours to find out how in the excel help files but no joy

Anybody help



No luck finding an answer? You could always try Google.