Free Microsoft Excel 2013 Quick Reference

Create a cascading list box using Excel

A cascading list box is a list box with choices that change based on the
value a user selects in another list box. For example, clicking Condiments
in the Categories box causes the Products box to display a list of
condiments. This example comes from an Access 2003 solution. Can it be done
using Excel 2000?


Post your answer or comment

comments powered by Disqus
One spreadsheet tab contains multiple project reports of about 20 lines each.
Each section has a text header. I want to create a pick list in a cell at
the top of the tab so I can quickly go to a project header and see the report
for that project. Just using a filter won't work, I need to see the 20 lines
following the header. I need either a find or a go to that will pick the
project name from the list, but it needs to be a permanent function set up in
a cell. Can I do this within the functions of Excel, or do I need a macro?
I'm not macro-wise.

I have a shared workbook with several huge spreadsheets. There are certain
things that other users/viewers would readily want to know without parsing to
the end of the spreadsheet. How can I create a floating text box in Excel
and include totals of certain cells from the same worksheet?

hi,

I keep running into this same situation time and time again so this time i thought i would post the question here and see what others come up with.

I have a column of values in an excel sheet. I want to create a unique list of those values to paste into another worksheet. In the past i have used someething like this

   
SourceRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=TargetCell, Unique:=True
the problem is that the unique list that is generated includes the header from the original list.

So my question is what is the best way to generate a unique list from a column of data in an excel sheet and i want to put this unique list into another sheet starting at some cell location and have it paste down the column.

Are there any suggestions?

Hello,
Me again. I am almost done with my project however I have come across an issue when I try to cascade dynamic datafrom one list box to another.
Can anyone suggest a simple way that me as a VB novice can achieve this with the 2nd list being depandant on the selection in the first?

thank you

Dave

hello,
thanks for viewing this thread.
i have one excel sheet. i have entered data in 30 rows in one column.
now when i create a list using validation menu in excel . it created drop
down list box having 8 or 9 rows only.
now i want to create a drop down list box in excel sheet not on form . where all 30 rows are displayed at a time.

thanks
abhijeet

I want to create a drop down box in an excel cell for users to select an
entry from my list - I am completely stuck and have no idea how to do this.

When I insert a bullet or number in Word, type something and enter, a second
bullet or number appears automatically on the next line enabling me to make a
bulleted/numbered list.

However, in excel when I do the same thing and enter, it takes me to a new
cell and hence creating a bulleted/numbered list is not possible for me
within the same cell. Kindly tell me how to create a bulleted list within
one cell in excel.

I am using MS Office Professional Edition Excel 2003

Thank you.

I know my way around Excel pretty well, though not an expert.

I have created my an Excel file with information for each player in a
sports league whose playing rights is held by one of our teams. Essentially,
the file serves as a crude player database with the following column
headings saved as named arrays:

Master (this is a unique ID number)

Last_N

First_N

Team_ID (number assigned to a specific team)

Team (text name for team)

Status (designates player status by number denoting 1 for active,
2-inactive, 3-suspended...)

Jersey (uniform number)

Pos.

Ht.

Wt.

Birthdate

Age

Hometown

College

Each row has also been saved as a named array starting with P_1 through
P_329, noting that there are 329 players in the League.

Is there a method (without using Pivot Tables) to produce a roster of active
players for each team in a separate list?

If team=x and status=1 then display Jersey, Last_N, First_N, Pos., Ht., Wt.,
Birthdate, Age, Hometown, College for Team X

If team=y and status=1, then display Jersey, Last_N, First_N, Pos., Ht.,
Wt., Birthdate, Age, Hometown, College for Team Y

Hi,

I want to create a database of sorts using excel to keep track of my book
collection, but I have a question.

Essentially for each book listed on a row I want a column for each of the
following: book title, author, publisher, year, and genre.

I've used Excel before and have seen a spreadsheet with a worksheet like the
above which had a second worksheet that had a button marked "Add book
details" When you clicked it you filled the info above into a few boxes and
then it would actually add the info you typed in, into the first worksheet
and even sorted it into the correct position alphabetically.

How do I do that?

I understand formulas in Excel, but do I need to create macros to transfer
information from worksheet page to another? I'm over my head.

Can anyone recomend a site where I can learn how to do this or even a
template or site on the web where I can download a premade one that I can
customize. Miscrosofts template site doesn't really have any good examples
of inventory templates... Though I have in interest in learing more about
Excel programming, Id rather not have to reinvent the wheel.

Thanks in advance!

Has anybody got any ideas on how to create a fixture list for twelve soccer teams playing each other twice in a season on Excel 2000?

..can only be used with a single column value. It's easy to populate a cell from a one column list box using Data Validation. But is it possible to automatically populate a ROW with values from a custom mutilcolumn list box ?

Worded a different way: Is there a way to make or write code(if coding is necessary) for a multicolumn drop down list box that would poplulate an entire ROW, and not just the cell where the list box is, with an entire row of(previous) values for a selected or mouseclicked item?

Example:

List name = MYLIST:

DESCRIP. CATEGORY AMT.

KROGER GROCERY $20.31
SHELL GAS $35.00
T-MOBILE CELL PHONE $124.32

THE FORMATTING IS OFF ON THIS, BUT COL A = DESCRIP., COL B = CATEGORY, COL C = AMT.

SCENARIO:
AN EXCEL CHECK REGISTER CONTAINS THESE COLUMNS ALONG WITH DEPOSIT AND BALANCE COLS TO THE RIGHT.

COL A = DESCRIP. B = CATEGORY C = AMT.

A NEW BLANK ROW IN COL A IS ARRIVED ON READY FOR A NEW ENTRY.

THE DROP DOWN LIST BOX WOULD HAVE A GROWING LIST OF VALUES, SUCH AS

KROGER FOOD $20.31
sHELL GAS $35.00
T-MOBILE CELL PH $120.00
BURGER KING FOOD $11.81

WHEN A NEW ENTRY IN THE DESCRIPTION COL(A) IS MADE, IT IS ADDED TO THE LIST BOX. THE CATEGORY CAN BE THE SAME, AS TWO DIFFERENT RESTAURANT NAMES WOULD BOTH HAVE "FOOD" AS THEIR CATEGORY.

THAT WAY, BY CLICKING THE DOWN ARROW AND/OR THE HIGHLIGHTED ITEM, ON THE PARTICULAR ROW OF THE LIST FOR A DESCRIPTION A USER WANTS,IT WOULD FILL THAT ENTIRE ROW WITH THE RESPECTIVE CATEGORY AND LAST AMT VALUES FOR THAT PARTICULAR DESCRIPTION ITEM (I'M AMAZED THAT THE DEVELOPERS OF EVEN EXCEL 2007 CAN'T SEE BEYOND A ONE COLUMN LIST BOX). THIS CAN OFFER MUCH MORE AUTOMATION AND FUNCTIONALITY - IF IT CAN BE DONE.

Thanks, whomever, for all the help you can provide on this.

CR

Hi,

I want to show a calender as a validation list box in excel, so that all you
have to do is click on the date.

I know i could create a list of dates, but a calender would look much better.

i have an excel spreadsheet with about 2500 e-mails and would like to see if
i can create a distribution list from excel to outlook. is there a way to do
this?

I would like to create a mailing list in excel, entering each address in, not
importing it from somewhere. Can excel do that? Or do I have to import the
data, entering the addresses in a different software and doing some sort of
mail merge with excel? Thank you in advance for your answer.

I am trying to create a basic 2 dimensional line chart using the following
data:

X-axis Y-axis DATA DATA
"Rating" "Time" "Rating" "Time"
5 02:00.0 5 02:00.0
4 02:04.7 5 02:08.6
3 02:09.5 4 02:17.2
2 02:14.2 4 02:19.1
1 02:18.9 4 02:28.0
02:23.7 4 02:28.2
02:28.4 4 02:29.2
02:33.2 4 02:33.1
02:37.9 3 02:36.9
02:42.6 3 02:41.2
02:47.4 3 02:42.1
02:52.1 3 02:44.2
02:56.8 3 02:48.8
03:01.6 3 02:48.9
03:06.3 3 02:50.6
03:11.1 3 03:00.2
03:15.8 3 03:02.9
03:20.5 3 03:04.9
03:25.3 3 03:05.7
03:30.0 1 03:30.0

I would like to be able to lable each axis and each data point.

This is the very first time I have tried charting using Excel and obviously
I am missing something very basic.

Thanks,
Ksean

Hi all,

It´s time for another powerful approach with ADO & SQL which we here use to create a unique list with.

As the list gets larger the more powerful this approach becomes.

What we need:
* MS Windows 2000 or above
* MS Excel 2000 or above
* MDAC 2.5 or above (ADO Library)

Step 1
Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.

Step 2
Insert following code in a standardmodule:

	VB:
	
 
Sub Create_Unique_List() 
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stCon As String, stSQL As String 
    Dim i As Long, lnMode As Long 
     
    Dim wbBook As Workbook 
    Dim wsSheet As Worksheet 
     
    Set wbBook = ThisWorkbook 
    Set wsSheet = wbBook.Worksheets("Sheet1") 
     
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
    & "Data Source=" & wbBook.FullName & ";" _ 
    & "Extended Properties=""Excel 8.0;HDR=YES"";" 
     
     'The keyword DISTINCT generate a unique list in the SQL-statement."
     'All the column in the worksheet Sheet1 have fieldnames in the first row
     'which we all use here.
    stSQL = "SELECT DISTINCT Dep, Reg, Amount FROM [Sheet1$]" 
     
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
     
    cnt.Open stCon 
    rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic 
     
     'A check to see that records actually exist.
    If Not rst.EOF Then 
        With Application 
            .ScreenUpdating = False 
             'Collect the present calculation-mode.
            lnMode = .Calculation 
            .Calculation = xlCalculationManual 
             'Add a new worksheet
            Worksheets.Add Before:=wsSheet 
             'Copy the records to the added worksheet.
            ActiveSheet.Cells(2, 1).CopyFromRecordset rst 
             'Reset the calculation-mode.
            .Calculation = lnMode 
            .ScreenUpdating = True 
        End With 
    Else 
        MsgBox "No records could be find!", vbCritical 
    End If 
     
     'Cleaning up
    If CBool(rst.State And adStateOpen) Then rst.Close 
    Set rst = Nothing 
    If CBool(cnt.State And adStateOpen) Then cnt.Close 
    Set cnt = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Kind regards,
Dennis

I am trying to use excel for a database and would like to create a drop down
box for the contact numbers of individuals, I would like everytime I select
an alternative heading (four of them) that the data then coincides to all
individuals in the database, ie, select fax numbers from a drop down box also
containing home, work and mobile numbers and then have all the individuals
fax numbers appear in that column. Is this possible??

Thanks to anyone taking the time to answer this.
Kylie

Hi can anyone tell me if this is possible and if so how

I would like to be able to open a web page atomaticaly using excel
if I entered a place name in say A1 then the web page would automatically open
No moving on a bit would it be possible to have a drop down list and each item would open a different page.

Reason is I have a site with various pages giving weather forecasts for race tracks so when someone enters the track name in one box the site opens

If this is possible please be gentle with me as I'm a little green with this sort of thing
Many thanks

I am very good with Access but my boss is using Excel for this particular
project because of the calculation abilities. Can I create a pop up box that
prompts for parameters and shows the rows in the spreadsheet that meet these
criteria.

Hello

I have a scorecard for the Eurovision Song Contest tomorrow. This scores
24 countries (listed in A1:A24), giving each country a point score between 1
and 20 (points are in B1:B24).

I can use the rank function in column C to determine which country has come
first in the list (i.e. in C1, I put =rank(B1,$B$1:$B$24) and copy the
formula down to C24).

This then allows me to create a new list, with the countries in order from
who has scored most points to who has scored least. I do this by repeating
the countries in column D, and then in column F I use the formula
=vlookup(row(F1)-row($F$1)+1,$C$1:D$24,2,FALSE).

This works ok as long as two countries do not score the same number of
points. If this happens, then I get an "N/A" in this column. How do I get
around this? And can I make the formula further fool-proff so that if 3, 4,
5 or 6 countries score equal points, the formula doesn't fall over?

If 2 or more countries are tied, the order I would like Excel to return the
countries in could be either (i) the order in which the country appears in
the original list; or (ii) alphabetical.

Many Thanks for any help you can give me.

Is it possible to create a bell curve chart using excell?

i want to create a dropdown list in excell that will highlight a word when
first letter is typed

Good Afternoon,

Thank you all for your help in advance.

I would like to create a gantt-like chart in Excel without using any VB or macros.

Specifically, I am having problems trying to figure out how to get a task to appear with two seperate durations.

For example,

Building 1 will have a Start Date of 5/1/10 and End Date of 5/25/10
as well as a Start Date of 7/1/10 and End Date of 7/7/10

Building 2 will have a Start Date of 6/15/10 and End Date of 6/30/10

How can I display these durations graphically on the same line? Is there a graph in Excel that can do this? I have tried to show the 2-D Bar graph particularly the Stack graph, but I have not had any luck in get the data series to display the information correctly on the graph.

I have attached an excel spreadsheet to show an example of what I would like to display on my graphs

hi all,
i am SAP developer and i m working on SAP - excel integration. i am fetching data from SAP tables in to excel and in each row of the excel i want to create a dynamic list box and populate dynamic values.
i know the logic for data fetching but not logic for cretion of list box dynamically and handling events of dynamic list boxes..
please suggest me solution asap.

thanks.
jigs d


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