Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a
table that has both text and numbers. They are part numbers, for example
21BC124. I kept the field as text because of the text with in the numbers
and didn't figure that a numeric field would alow the text. In my part
numbers table it sorts correctly (first by number then by letter then by
number again), but in my reports and queries there are a few number that sort
in the wrong place. Like this...
I can't quite f...Splitting First and Last Names
I have a spreadsheet where the Name feild contains both first and last
names. I want to split this into two fields. How can I do this?
use 'Data - Text to<columns' for this
> I have a spreadsheet where the Name feild contains both first and
> names. I want to split this into two fields. How can I do this?
"Jamie" <email@example.com> wrote
>I have a spreadsheet where the Name feild contains both
>first and last
> names. I want to split this into two fields. How can I do
&g...Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD
accounts. Changed all display names, accounts name etc with the 2 accounts.
When users open outlook and send to renamed account they see account name
<oldaccountname> in the autocomplete list. I have cleared nk2 files in
profile , but it still shows newaccount <oldaccountname> in drop down box. Is
there a way to edit the <oldaccountnam> value of the renamed account? I
tried the same thing with a freshly loaded PC. with the same result.
You can delete the nickname files.
MVP - E...Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following:
This works as designed - a new window pops up with the "My Report"
report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010
are passed respectively.
But what is the syntax for changing the "01/01/2009" and "12/31/2010"
to variables that point to the StartDate and FinishDate parameters of
the PAR...excel, worksheet, set print area, position selection on printed pa
Office 07, Excel.
Print area is set, print preview displays selection, always on far left of
I want to choose the position on the page, eg centered or right aligned etc.
To get your printout to center on the page chose Page Layout, click the
Page Setup quick launch button (bottom right corner of the Page Setup group).
Choose the Margins tab, click Horizontal.
There is no command to right align a printout, but you can change the left
margin to force the printout to the left.
If this helps, please click the Yes button
"Eve Al...Count unique values
I've some problems to count unique items (Invoice #) in a pivot table. There
is the default solution "Add-a-new-calculated-column" as mentioned on
http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't
My problem is, that these values aren't in a Excel worksheet; it's a
external data source - a SQL-Select via DAO/ADO. Because of that, I don't
have the possibility to add a new column. Another reason is, that the pivot
table should always be dynamic: Group over this field, group over another
field - and always show the number of unique...Top values
Dear friends, need your help again please.
I have a table: tbl_Plots (PlotID is the primar key - number byte) and
tbl_Data (ID is the primary key - autonumber, PlotID related to tblePlots,
height - number byte and diameter - number integer).
I need to select the 20 bulkiest trees of each plot, i.e. having the biggest
diameter. Also, perhaps in a plot less than 20 trees will be present so I
will need all of them.
Thanking you in advance,
> I have a table: tbl_Plots (PlotID is the primar key - number byte) and
> tbl_Data (ID is...Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have
null values. I will try to describe the problem. Please let me know
you need additional information. Do any of you have any ideas of how
can fix that sumproduct to get it to work?
A B C
Row Trend Claims
5 Emergency $0
6 Emergency $0
7 Emergency $0
8 Emergency 81.68% $24,444
9 Emergency 35.00% $164,758
10 Emergency 35.00% ...Comparing first and last names in two lists
I have two data sources that each contain about 8000 names in seperate
fields for first and last name. I can put them on seperate worksheets
or append one to the other. I need to make them match and find out
where they don't. Messed with consolidate but not sure if that will
work for more than one column at a time. FYI data set one is from a
school transportation database and data set two is from the main
student database from the same school district.
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages dir...Cannot Print a selection of ToDo's
I have a user who is having an issue when attempting to print a selection of
his Todo list. The print dialog will appear but as soon as he clicks either
print or print preview the dialog dissapears and no jobs are sent to the
print que. He is able to print other documents to this printer and the issue
occurs with all other printers installed (including a PDF printer).
Computer is running Vista business 32 bit w/ Vista SP2. Office 2007 Small
Business with Office SP2.
...Changing the Selection Fields in a Combo Box
I've created a Combo Box using the Controls Wizard. In creating the
control, I specified that my unique value to be stored in the table is an ID.
When the user views the selection options in the Combo Box, I'd like to have
the ID field hidden and only see the text values, while still storing the ID
in the appropriate column.
This is probably a second question.
Also, when the user views a form that has been populated, I'd like them to
see the actual textual values on the form, as just the stored ID value. Is
that possible to do with the one Combo Box or is that...Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul!
I am looking for the cell contents of Sheet: July Column D Row 27.
I tried to use offset, but I am stumped. Can I add something to the
front of the concatenate to not only build the reference to the cell,
but also return the value instead of the =Jul!$D27 ?
Don't include the equal sign in your formula. And match the name correctly (Jul
Depending on the name of the worksheet, you may need to have a string that looks
=indirec...selecting multiple choices from a drop down list
I have a drop down list in a cell and I want to be able to selec
multiple entries from that list.
Does anyone know how I go this
Message posted from http://www.ExcelForum.com
If this is a data validation list, you could use code to compile a list
of selected items. For example, if the data validation is in column C,
the following code will store the selected items in the same row in
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub
On Error Resume N...ComboBox passing values from selected
In my form (Items_frm) I have a combo box (Itemcbx) that displays 4 columns
(Item, Makebuy, Revision, IsActive) but is bound to the first column.
However, I would like to pass the other values from the remaining columns to
other fields in my form after the selection. Is this possible?
Can someone provide a sample code?
You can use the Column() attribute.
Note that the index for combo box columns is zero-based so 0 is the first, 1
is the second and so on...
"Angel G" wrote:
> In my form (Items_frm) I have a combo box (Itemcbx) that displ...Deleting Unique Values
How do you delete unique values in a column? I need to filter 7500+ rows to
only display duplicate values.
The VBA code below will delete the entire row when the value in col. "A" will
Consider to make a copy of your entire sheet in order to test the code and
see if this is what you need.
LR = Cells(Rows.Count, 1).End(xlUp).Row
For R = LR To 1 Step -1
If Application.CountIf(Range("A:A"), Cells(R, 1)) = 1 Then
N...List Box Point-to-Select (MouseMove)
I have a list box that shows files from my hard drive. I'd really like to
have it select the item being pointed to as I move my mouse pointer over it.
I have coded this and it works fine unless the list is scrolled. I need a
way to offset my list index to account for how far down the list has been
I can't find a list box property that will allow me to determine where a
scrolled list is positioned. Something giving me the list index of the first
visible row would do the trick. I haven't been able to find anything so far.
I've tried some VB code that uses the Send...Making words in a list a value!!
I am trying to create a list that will generate a value in another column
once that word or phrase is chosen. Is this possible and how. Exp. In the
drop down list I would chose Product, then in the price column the price
Much better than I could ever explain the process. Here's exactly wha
Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=454
View this thread: http://www.excelforu...Getting right date value
I setup my DTPicker control to be used only
as a date control, yet I'm noticing that sometimes
it will give back a date AND a time all in the
same "value" variable.
Since it appears that a variable of type "Date"
can give back both a date and time, how can I
eliminate the time half of a date value???
I might not be able to exactly control the DTPicker
control to give me JUST a date, so I'm just curious
what to do if it gives me back both a date & time.
Try this :
Dim x as date
x = cdate(clng(DTPicker1.value))
&qu...find match then change cell value
In column A I have cells filled with text and in column B I have cells filled
with numbers. I need to check if the number in cell C1 equals any of the
numbers in column B. If a match is found then I need to change the text in
column A to CBO.
Column A Column B Column C
aep 5 7
Since the cell C1 = 7 equals the 7 from column B, I need to change the data
in column A from xle to cbo. Is this possible...VLookup #VALUE! error help needed to resolve
The following is the funcation I have:
=VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0)
I have all the columns formatted the same; as in the column that the
function is using to lookup is text and so is the column for this figure in
order to pull back the appropriate answer. I have keyed the data instead of
having links. I have replaced the final '0' with TRUE & FALSE then put it
back. I have formatted the columns for text and for numbers.
But I am getting the #VALUE! error in SOME of the cells NOT all of the
cells. I don't know what else to d...#VALUE error when linking to other files
I have a summary sheet that pulls info from approx 10 other excel
In one column of the summary sheet, the links perform just fine. Each
cell in this column is the sum of an entire column in another
workbook. When I open the summary page and choose "yes" to update
with new information, everything works properly.
However, in the same worksheet as this, I have another column where
each cell is the result of a SUMIF where the data comes from another
workbook. The odd part that I cannot understand is why when I open
the summary sheet and update the information do all the cells...Show a blank result in a cell when there is no value in the "Lookup" cell
I apologize if this question has been asked, but I have been unable to find
an answer searching the topics.
I'm using the following formula in cell C3:
When I type in an employee ID in A3, his/her last name shows in C3.
However, when there is no value in A3, C3 shows error "#N/A".
Is there a way to show a blank cell in C3 until a value is entered into A3?
Thanks in advance!
On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote:
> I'm using the following formula in cell C3:
> =INDEX(LastNam...Countif and values greater than zero for a named range
I have a workbook with 30+ worksheets. For each of the sheets, there is a
cell (F51) that is part of a named range (PPG). On Sheet 1, cell c9, I want
to sum the range (PPG) and then divide by ONLY the number of cells on the
other sheets (F51) that are greater than 0.
I am able to sum and then divide by count (=sum(PPG)/Count(PPG) but I cannot
seem to get it to only count the cells that are greater than zero.
When I attempt to use suggestions from other posts:
(=sum(PPG)/COUNTIF(PPG)>0) I get error.
Any help is appreciated.
If you need Excel help on how the COUNT...setting a column value in code for an updatable ViewGrid
Is there a way to set the value for a column in code when a ViewGrid row is
edited? I have a row-last-updated date that is a required audit field in
all our systems (required by data management). I don't really want the user
updating this field as it should always be system controlled. If I create
an editable ViewGrid tied to either an SqlDataSource or an ObjectDataSource,
the field seems to only be updatable by the user. I can set it the first
time by setting the DefaultValue in code but that has no effect after there
is an initial value in the column.
Do I need to co...Show a cell as drop-down even if cell not selected
Let A1 be a list using Data --> Validation --> List -->
with 1,2,3 as values.
Therefore, whenever A1 is selected it indicates with a
small icon seemingly attached to the cell that a drop-down
Can we make a cell show that it contains a drop-down even
when the cell is not selected? If so, how? The solution
could be either through code or some external setting.
Note that i am using Windows 2000, Office 2000.
Thanking you in advance.
there's no such option to automatically add an indicator. You may color
these cells with a different color