Sum a $ amount in part of a field
I have payment lines that export to Excel as one field/cell - at times, there
may be as many as 45 or 50 payments that are strung together, separated only
by the "|" (bar) key.
02-19-03 01-31-03 220818 $125.00 151577 VOL.5 NO.2 | 08-13-03
08-04-03 243736 $125.00 158021 VOL.5 NO.1 | 10-14-03 09-13-03
S-59651 -$10.50 159582 RATE ADJ
Is there a formula I can use to pull out just the $ amounts from this cell,
and then sum it up (i.e. $239.50)?
I could split the field using the "|" as a delimiter, but then I might have
40 or 50 columns and I still...Glossary for Event Properties of forms, fields, etc.
I'm trying in vain to find a glossary of sorts that explains what event
properties (e.g., On Load, On Activate, On Dirty, On Undo) do... It'd be
nice to have a list with definitions all on one page and I am having trouble
finding this via the Internet, if there is one...
Any suggestions for website links would be appreciated!
In the database help window, search for:
Order of events
Arvin Meyer, MCP, MVP
"Jill" <Jill@discussions.microsoft.com> wrote in message ...Cell Value as Named Range Reference
Little bit of a quirky question...
Trying to use a cell value as a reference in a formula, where that
cell value is the name of a named range.
So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2.
I want to get the correlation vale for A1:A3 and B1:B3
So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the
formula: =correl(D1,D2). But I get an error. Have also tried using
Indirect to no avail.
Any help would be hugely appreciated. Thank you.
http://www.wimgielis.be = Excel/VBA, soccer and music
"ste...match name fields to determine differences #2
I have two worksheets that have common columns for last name and first
name. I want to determine which rows are in both worksheets based on
the last name and first name. The concatenate function with vlookup
is an option, is there an easier way?
I will have to assume that first names are in column A and last names in
And that there are 300 names on sheet1 with headings.
The first sheet is called "sheet1"
It doesn't matter how many names are on sheet 2.
In the second sheet cell C2 enter
You may have to change it around to match your information.
=IF(SUMPRODUCT((Shee...symbols won't work in form fields
I have Access 2002. We write a lot of letters in French and as I have a
QWERTY keyboard, we have shortcuts for the french letters which are missing
from the keyboard. For example Ctrl + e is e with an accute accent, Alt + e
is e with a grave accent, and so on. It all works just fine in WORD.
But in my forms in my ACCESS database, in my fill-in fields, the shortcuts
simply don't work. And there is no "symbol" option available in the tool bar
to insert a symbol or French character. Is there any way I can add this
function in to the database ?
thanks .. Roger
...Need to reference a block of cells from another sheet.
apologies if this is probably a mundane question, but I didn't know what
'search terms' to put in google to return any results..
I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet)
to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet),
tabs 1-11 (sheets) are changed too.. the cells themselves will never move
location, just the data within them..
Is it possible?
On your sheet1 select the cell that should receive the data from Sheet12;
Hold down the Shift key and Click on Sheet11 << this shoul...Task Fields.
When I add a task the "Name" of the "Contact" is added to the task... but how
do I add the "Company" name and/or "Telephone Number" to the task list?
Thanks for any advice of help or information you can give,
You can accomplish what you want with a little custom VBA code. For an =
example, see http://www.outlookcode.com/codedetail.aspx?id=3D566
Sue Mosher, Outlook MVP
Microsoft Outlook Programming - Jumpstart for=20
Administrators, Power Users, and Developers
http://www.outlookcode.com/ju...format detail field at runtime
i need to format a detail section bound textbox at runtime on a report.
i tried using the condition formatting by entering
IIf(InStr(Me.Result, "+") > 0, "positive", IIf(InStr(Me.Result, "-") > 0,
also tried this in the deatil section
Me.txtResult.Properties(40) = IIf(InStr(Me.Result, "+") > 0, "positive",
IIf(InStr(Me.Result, "-") > 0, "negative", Me.Result))
but received an error msg.
how is this accomplished?
i fixed my undinding the...Display year only in a form field
How can I display only the current year as defailt text in a form text field?
Traa Dy Liooar
Please ignore - have figured it out.
Traa Dy Liooar
> How can I display only the current year as defailt text in a form text field?
> Traa Dy Liooar
...number of fields exceeds 256 when importing from a dbf file
I'm having a small problem with importing some old data from a FileMaker Pro
database. According to what I found out in the groups archive the best bet is
to convert it to a dbf file and then import it. However, I the number of
fields in the dbf file exceeds 256, so the import wizard truncates the import
at field number 256.
I would now want to spread the data over two sheets. However, in the process
of converting it to a dbf, quite a few of the field names get truncated and
therefore have the same name, so I can't use SQL to retrieve them. My
question is: what i...refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could
apply to both
please could you take a look at the following post
Is there some reason you can't post your question here instead of asking us
to look elsewhere?
Microsoft MVP Excel
"Blinds Nottingham" <firstname.lastname@example.org> wrote in
news:email@example.com...Locking Fields in an Editable Form
I created a form that has the Allow Edits selected as Yes. But I only want 2
fields in the form to be editable, the remaining other fields I'd like them
to be locked. Is there a way I can lock the other fields so that it's not
changed? When I try to change the field property for Locked as Yes, it locks
the field so that when I enter any new records it will not allow me to input
data in that field. Can anyone suggest how I can modifiy my existing form so
that it locks most of the fields after it's saved but allows edits to
specific fields when going back on a record?
Th...Form multiple subforms?
I have a issue where one table Employee Shift has two depentant tables
Downtime and Scrap. I have created a form subform with Employee Shift
and Downtime. I also want to include an additional subform of Scrap.
Is it possible to include 2 subforms on a single form? If so, does
anyone have an example?
You can place as many sub-forms as you want.
the question is your relation you trying to display
Employee shift ----> phone numbers
----> name of children
The above has the employee shift...Coping with missing year value in date data type field (Rephrased/Repost)
Suppose I am collecting Date of Birth data for a contacts application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to deal with
this? I can think of two, but...
1. Use a "dummy" value for an unknown year, (e.g. 1900). This would allow
the use of a date/time data type and be fairly easy to implement, but it
sure feels like I would be breaking some kind of relational rule to enter
2. Create three text fields (txtDay, txtMonth, txtYear). This would allow
the use of...What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under
the Inventory account and Credit for COGS account in the Transaction Entry
Zoom. We're trying to figure out where that amount came from but we couldn't
zoom more than what it's currently shown. In both fields, Reference and
Distribution Reference, it's shown IVA#######... that is unknown to or setup
by us in anywhere, i.e. Audit Trail, Source Document.
Does this amount affect the dollar in the accounts mentioned?
And can anyone please help to where we could drill down the IVA details?...Fill Color on master not the same as shape it was created from
Created some simple telephone pad shapes, that look like the letters and
numbers on a telephone. But when I drag the shape onto the drawing, the black
background color is blue. Changing the fill color works, but shouldn’t the
master shape fill color be the same color as the shape when the master is
You may have a shape that has a Fill style to define the fill color. It is
possible that the Fill style in the stencil is different from the Fill style
on the drawing page. You can select your shape and go to Format > Style to
Office Graphics - V...macro button refers to prev. workbook?
When I create a macro in Excel, and assign a button to it, it works
fine the first time, but when I re-open the workbook for a second
time, and try to run the macro again, the macro seems to refer to the
previous workbook...For some reason, the macro saves the name of the
Dim name As String
Dim adress As String
name = ActiveSheet.Range("B6").Value
adress = ActiveSheet.Range("B4").Value
ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...Sort by empty fields
I have a mailing list, and about 1/3 of the records do not have email
QUESTION: how do I sort the list so that only the records with empty email
address fields appear?
Thanks, Mark Flynn
use the AutoFilter at Data=>Filter
and then use Custom...
>I have a mailing list, and about 1/3 of the records do
not have email
>QUESTION: how do I sort the list so that only the records
with empty email
>address fields appear?
>Thanks, Mark Flynn
You can use an AutoFilter ...mapping to customerid field
I have created an entity called "project"; this entity is in fact an
entity above opportunitities. Which means 1 project can hold several
The project entity has a lookup field to the account entity
I have mapped new_accountid to customerid (potential customer in the
opportunity) in the mapping page and
pubished the changes When I then try to create an opportunity, I see
the Customer field has a record in it that has the name of our
but the icon of a Contact record.
When attempting to save the opportunity, or when clicking on...OAB field size limitations
Hi, we've noticed several user display names getting cut off within the
offline address book. There appears to be a limit of 64 characters.
If I look at the corresponding user entry in the "live" address book it
displays correctly. If I search for the user in my copy of the offline
address book it is cut off at the 64 character mark.
Does anyone know if this is indeed a limitation of the OAB? I can't
seem to find much about this anywhere.
OAB property filtering for Exchange 2003 Service Pack 2 OAB Version 4 -
http://blogs.msdn.com/dgoldman/archive/2006/04/29/586827.asp...Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references
Uncheck "row and column headers".
Good idea to spend a few minutes browsing through the various Tools>Options
tabs to see what else is available to toggle on/off.
Gord Dibben Excel MVP
On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com>
>How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...return type of a field
My goal is to find the return type of a field. I have created a
customized right click menu. A user can right click and then choose
"conversion" option to convert the cell value, either from hours to
decimal or from decimal to hours. It is working fine.
The problem is: the program should be able to find out the difference
between hours and decimal value. To my understanding, the program
takes the value as "double" irrespective of the value in hours or
Can I get some help here in figuring out the hour type ? I want to
display the alert message if the user tr...Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a
colleague's machine at work (Office 2003; VBA 6.5; XP).
I've read Chip Pearson's "Missing References in VBA" which is a geat
explanation about how to fix problems like this.
Oddly the library didn't seem to be flagged as "missing" and was in fact
present in the system32 directory but didn't show up in the list of
Now to the questions:
I'm not clear on whether if I write code in a project that references a
library which is not part of the &qu...Reference
Is it possible to create a reference to another object such as:
int & x = i;
but using an image list, and referencing different objects depending on a
such as :
CImageList & imageList;
if (thumbnail == 1) imageList = m_ImageListThumb;
else imageList = m_ImageListFullSize;
Hope this makes sense!
You're better off using pointers for this. I believe, depending on the
class, that what you're trying to will make a copy of the object instead of
"Ben Williamson" <firstname.lastname@example.org> wrote in message
news:43ee4603...Macro to Reference Column Next to Current Reference
I'd like to automate the following procedure using a
macro. Please help.
1) i have 2 sheets: Sheet1 & Sheet2
2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2!
F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly.
3) Every month when i do my work, i'd have to manually
rekey the formulas to reference the subsequent columns,
ie, Cell B2,D2,F2 will change from above to "=Sheet2!
G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following
month, I have to change manually again to "=Sheet2!