Select a random value from a column of values
Hi, I have never programmed Excel before, but do use it a fair amount,
including use of built-in formulae. I would like to be able to select
a random value among a group of values - is this possible?
So for example, in a spreadsheet I have in column A a list of names
from cell 1 to cell 20. then I want to pick a name from random from
that list. How would I do?
Any input would be appreciated.
PS I do not have visual Studio/VB installed on my machine.
someone else will give you a simpler formula, but, if not, the following
=INDIRECT("A"&a...Access 2007. Combo box list template
I am intending to use combo boxes on Forms to support data entry. I am
wondering if there are templates already created that i can import, such as
Countries, States, Counties, etc..
Also is there any design benefit to using the combo box in the bound or
There's a couple of lists here, for US zip codes and Australian postal
Bound combos are ideal where you need Access to store a value.
Unbound combos are ideal for jumping to a record or applying a filter, e.g.:
Allen Browne...Default numeric value
I am creating a spreadsheet dealing with dollars. There are several formulas
that I have created using percentages. One particular formula multiplies a
percent in one cell by the $ value in another. If I have no $ value in the
cell the formula uses a '1' instead of what I would expect '0'. How can I
set up my sheet so that if a cell is blank the default value will be '0'?
Would help to see your formula, but:
VBA Project Manager
"thomson" <email@example.com...filter a list
In the "City" column, we type city with capital case in order to identify
the donors. Now I need to mass mailing to the donors only. Is there any
possibilty that I can filter all cities typed with "cpital case"?
Thanks in advance for your help!
Add a new column to the table, with the heading "Donor"
In the cell below the heading, enter the formula:
where city is in column D
Copy the formula down to the last row of data
Filter the table for TRUE in the Donor column.
> In the "City" co...Unique Lead Id like Case ID
I want to attach a unique id with lead just like we have for case, quote,
Can i know as to which procedure, function or DLL is doing so or do i need
to write my own utility.
If i need to write my own, how shall i go abot this?
Thanks in advance.
There are multiple ways to achieve this but they all depend on using code to
get the next id number. For instance after adding a field to lead to store
the id number you could add a workflow rule that calls a .net assembly to
find your last id and add one to it. You can also do this us...Pivot tables
Two questions regarding pivot tables.
Is there a way to use auto filter to limit the items included in row
fields? That is, instead of checking or unchecking items in the drop
down box use filters to say only include accounts greater than 5000 and
less than 7000. I got around this by filtering the source of the data,
copying it to another sheet and changing the source of the pivot table
to the new sheet, but that is cumbersome.
Can conditional formatting be used within pivot tables? I have an
actual field, a budget field and a calculated variance field. I would
like to have variances...Off Topic: Pulling Hair Out
Want to have some fun? Search microsoft.public.pos on google groups for
You'll get all of the posts about people pulling thier hair out.
Hint: There's more than one page.
...Table of contents #2
I have a multi page spreadsheet at sheet1. Then created a
table of contents at sheet2. By using a formula (=name!
location) I have the page name in my Table of Contents
from the spreadsheet.
Each spreadsheet page is numbered by using (page set up,
custom footer, &page). How do i get the number into the
Table of Contents?
don't think that will work. &page just numbers the page
starting at 1. so all the pages have 1.
what are you trying to do? create a goto funciton? if so
you might have better luck using cell addresses(upper left
corner) or range names.
>-----Orig...Pull down menu
I am trying find a way to make a pull down menu in excel.
I have a list of foods on one sheet in a workbook where all the nutrition
information is stored per food type where the row indicates the food type
and the columns indicate the nutrition info ie: Carbs, Protein, Fats,
Calories etc. This sheet has roughly 300 or so food items listed in rows.
How can I create a pull down menu in another cell on another worksheet as a
method to select a food item from the food sheet?
Any help is appreciated,
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (...Users with Email address not in All Users list
I have some users with Email addresses established (no mailbox) and would
like to have them appear in the All Users recipient list. How would I do
I was hoping someone could help me please. Here's what I have. A
worksheet that has several dates in a row. D1:T1, Of that range, I
have conditional formatting set to highlight a date(s) either = to or
within the next 7 days.
I need to be able to pull the date next closest or equal to today.
Can anyone help me out?
Any help would be greatly appriecated.
> I was hoping someone could help me please. Here's what I have. A
> worksheet that has several dates in a row. D1:T1, Of that range, I
> have conditional formatting set to highlight a date(s) either = to or...dyanmic picklist values
OK so I have set up a dynamic pick list but have run into a minor problem. I
would like my second list to default to a unassigned or blank value, no
matter what value is chosen on the first list?
I have tried to update my values by adding a blank value at the start of my
iStartIndex = 0;
iEndIndex = 0;
iStartIndex = 35;
iEndIndex = 39;
This however does nothing, and since I can not set a blank value in the
picklist, I am stuck.
Any ideas or suggestions???
You should add a value to you SubPicklist and call it "Unassigned" then
reference that value in the Ca...Building folder-list in Excel
You have kindly helped me before, and I dare to consult you again.
I want to make a Excel-list of all subfolders in folder - the path =
value in cell A1.
If there are no subfolders then a msgbox will appear "Folder dos'nt
Proberbly a simple VBA-trick - but I'm not that kind of
magician ...so..will you please help me again?
Best regards Snoopy
Private cnt As Long
Private level As Long
Dim i As Long
Dim sFolder As String
Dim iStart As Long
Dim iEnd As Long
Dim fOutline As Boolean
arfiles = Array()
cnt = ...word cannot be pulled up
Do I need a updated version of word. Cannot bring up any word files now.
If you are saying that you cannot double-click to open Word documents, see
Note that there is no "Word 99"; you need to look at Help | About Word to
find out which version you are actually using.
Microsoft Word MVP
"rentals" <firstname.lastname@example.org> wrote in message
> Do I need a updated version of word. Cannot br...Display percent and value on stacked column chart
I'm displaying cumulative $ totals from 3 sales groups on a 100% stacked
column chart. We've recently exceeded 100% of our total $ goal so the chart
isn't able to display the amount above 100%. If I switch to a standard
stacked column chart, I can't choose the display percentage option. I
downloaded the Rob Bovey's Chart Labeler but I can't get it to display what I
Perfect world would be displying the dollar value of each sales group in the
column segments and showing percent of total goal on the y-axis. I would
even be okay with showing the dollar valu...MS Query - Date/Time
I'm running a querry to bring data into Excel from a Oracle database. The dates in the Oracle database are in the Date/Time format and I need to average values for the each day. The information in Excel is in a pivot table format and shows multiple rows for each date. I believe this is because of the time portion of the date. I've formatted the date in Excel to MM/DD/YYYY and this does not change the results. Help!
...Pivot tables downgrade 2007->2003
I've created a few pivot tables in my report in Ex2007.
The problem is that there is a group of ppl who have Ex2003 and these pivot
tables looses their functionality (expanding) when opened in this version of
Excel. Of course files are saved as *.xls format but it doesn't change
The one way to solve this problem is to create once again these pvt tables
in Ex2003 using source table,
but i think its just time wasting process. Besides there are pvt tables
without source, so i would have to generate them again.
I'll appreciate any hint helping me...Assigning values to a variable set
I am trying to create a spreadsheet that allows me to assign a predetermined
number to a specific set of numbers.
(This is to help me with automation of grading students.)
There is a predetermined grid we must follow that has an x axis zero to 10
The y axis is zero to 50.
The rest of the grid tells the proper grade that a student would get is they
score specific numbers. For example a 50 and a 10 would be a 100%, a 45 and
a 7 would be another number like a 83%
I want to be able to enter the 2 (X and Y grid) numbers for students and
have excel calculate the proper percentage ...Data values contain Parentheses (N) with String inside
I have a database which has data values in one field where the data contains
values that have parentheses which also contain a string (Letter). One value
has a full.stop in the middle of the name. When trying to use these data
values in an IN clause I get an error which seems to suggest Access SQL is
interpreting the parentheses as a Function, which it isn't. They are just
plain vanilla data values being used in the SQL. Actual examples of these
data values are:
Canterbury(N) M.Valley(N) Randwick(I)
How can I successfully deploy these values into (a) an IN claus...desktop shortcuts to another user's task list?
Exchange 2003, Outlook 2003, Windows 2000/XP
I would like to be able to put a shortcut on a user's desktop to
another person's shared task list. Is this possible?
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the discussion intact. Due to
the (insert latest virus name here) virus, all mail sent to my personal
account will be deleted without reading.
After a lot of head-scratching, Jason Gallas asked:
| Exchange 2003, Outlook 2003, Windows 2000/XP
| I would like to be able to put a shortcut on a user's desktop to
| another person's share...Catalog values
How do you catalog every value or word from every cell in alphanumeric
order with the number of respective repetitions?
Ex. If every cell in a range contains a description of various
scientific results in sentence form, the goal would be to list all
words and values found as discrete elements (separated by spaces) and
to list the number of times each was found in adjacent cells. The
purpose for this application would be to find keywords and flag their
associated records (rows).
...Excel 03 DB query pulls data in wrong order, how to resolve?
New user to Excel DB queries. Have set up DB and query, however query does
not import DB in the column-order of the query. This worked fine the past
couple months. Dont understand why it wont work this month. Have re-created
query and the problem still exists. Cant find any information on how to
troubleshoot this issue. Thanks for your help.
Data>Import External Data>Data Range Properties
Uncheck Preserve column sort/filter/layout
Does that help?
...How block a highlighted list of Senders not one-at-time ?
New Windows 7 (prof level but for single-user home use) with Office 2007,
trying to add hundreds of Junk emails to list of Blocked senders but think
that is failing beause still geting many of the same senders coming through.
Tried deleting them, both in highlighted lists and one-at-a-time, but Adding
to Blocked Senders list can only be dome one-at-a-time; and still seems to be
Somebody know what I'm not doing, or doing wrong, please ?
Blocked Senders is only marginally useful because the spammers just change
their apparent addresses on a near-hourly basi...Excel to protected application? Unique Computer ID
I have a excel spreadsheet that ideally I would like to run as a standalone
Is it possible to do this?
I want to distribute by disk - but restrict the use of the to a specific pc.
Is it possible to locate a Unique Computer ID variable in Excel (such as MAC
address) to implement a key system?
Any help appreciated.
See Chris Rae's page at
and additional information at http://www.mvps.org/dmcritchie/excel/property.htm---HTH, David McRitchie, Microsoft MVP - Excel
[site changed Nov. 2001]My Excel Pages: htt...Freezing Table Headers and Column Headers
I am able to freeze table headers and column headers. Simple report with a
one row table header.
Table Headers : Tablix property, row group, advance mode, static, set fixed
data to true
Column: same using column groups
Issue is when both are frozen and I scroll horizontally everything is fine.
When I scroll vertically everything is fine.
If I scroll vertically then horizontally the frozen column disappears. If I
scroll horizontially then vertically the frozen column disappears. Can
anyone explain this behavior? What am I missing.