assign formatted cell value to another cell
If A1 has a floating point number for a date, e.g.
39647.25 for 2008-07-18 06:00:00
and I want to have that formatted string in a new cell - does anyone
know a formula to apply to another column, so it puts the *formatted*
value there, instead of the original number? I want the string
as the value of my new cell, and I can't find a way to do that with a
Range formula at the moment (I want to avoid looping over each row and
doing it manually in VBA, for speed reasons).
Thanks in advance!
=TEXT(A1,"yyyy-mm-dd hh:mm:ss&q...Auto-populating a reference field
In the Purchasing module within the Payable Transaction Entry
Distribution window, we have the ability to type a description of a
transaction on the Distribution Reference field. Is there a way to
automatically populate each entry with the vendor name and voucher
There is nothing like this out of the box, you might need to write few VBA
lines of code to have this done.
Mohammad R. Daoud
MVP, MCP, MCBMSP, MCTS, MCBMSS
Mob: +962 - 79 -999 65 85
Great Package For Business Solutions
"S...Crosstab Queries VS Pivot Tables.
I am not entirely sure when to use one and not the other.
I suppose Crosstabs should give you some more flexibility if you want
for example the total as the first column.
I prefer using CrossTabs for 2 reasons:
1. I can easily create a report based on one.
2. I can easily export the data to Excel.
Arvin Meyer, MCP, MVP
"Avid Fan" <email@example.com> wrote in message
> I am not entirely sure when to use one and not the o...Frx
We just installed the Add-in that allows AA reporting to be done in FrX.
I've successfully created a report which filters on specific AA values by
column. However, some transactions were never assigned an AA code.
How do I filter for those in a column? I've tried using wildcards up to
that point of the account structure or putting spaces, but neither works.
...Need to Loop Through the values in Cells and Extract Parts
I have a file with a column with a cells in it that each have multiple
values in each cell. So there may be 400 rows in the column and within
each cell there may be 40 unique values in it. For example : cell A1
would have the following:
3:""519"";i:8;s:3:""522"";i:9;s:3:""525"...Opening an excel file which has a query to external data
I have an access application which opens an excel file which has a
query to that access application to import data.
* I open the excel file when the access application is closed, i get
the message "this workbook contains etc ... Enable automatic refresh".
Until here everything works as expected and the data is refreshed
* When I open the excel file when the access application is opened i
get the message that the db is opened exclusively by another user.
This I understand ass well, but is it possible to resolve this?
* Now the thing I'm looking for is: Is it possible to ...Set Y-Axis max value to cell value without VBA?
I'm working on a home-grown pareto chart in my workbook; the data will
change and I'd like to dynamically have the (primary) Y-Axis max match the
sum of all values so that that it will synch with the secondary Y-axis which
will always have my 0-100% of the total (line). Is there an easy way to
force the primary Y-axis to have a maximum value matching a cell in my
You need VBA, but it's not terribly hard. Here's how (my site):
and here's a utility that handles the heavy lifting for yo...Index fields and formatting
I've inherited a document and some of the index fields - XE - are formatted
as bold and I want to remove the formatting.
I found a similar post going back to 2005 and the answer then was use
I have Word 2003 and 2007 and just wondering if this is still the case.
Also, is there a way to bold the text without bolding the index field.
Bolding the line with fields switched off still bolds the field.
To change the formatting applied to the XE (index entry) fields, you can
make use of Find and Replace. Display the Find and Repla...code wild card value for form variable
i use a check box in an option group to generate a specific value for a
using the following code
If Forms![fee sched form]![select region] = 2 Then
Forms![fee sched form]![region var] = "Roch"
If Forms![fee sched form]![select region] = 3 Then
Forms![fee sched form]![region var] = "CNY"
this variable is hidden on the form and value is used a query.
how do i code this, so i can put "*" in the variable and thereby get all
values in the query?
the query code generates like "*" but i do not know how to code via the
fo...Default Date Value
How do I get a record create date to automatically fill in on a field in that
record as the default value?
In your table you could set the default value to the field as =Date() or set
the default value of your field on the form to =Date()
"BigK9" <BigK9@discussions.microsoft.com> wrote in message
> How do I get a record create date to automatically fill in on a field in
> record as the default value?
I recommend making it the default value of the field in the tabl...year conversion in date fields eg 1900 to 2000
I moved a existing database from foxpro to access 2000.
The dates in foxpro had a 2 digit year and when brought in
to access it converted them to 1900 (eg. 11/12/00 to
11/12/1900 instead of 11/12/2000). Is there a way in
access to convert (replace) only the year of vaious dates
to another year. If this is not possible is there a way to
prevent this while converting?
An update query can work for this.
Something like this:
UPDATE TableName SET [DateFieldName] =
DateAdd("yyyy", 100, [DateFieldName])
WHERE Year([DateFieldName]) = 1900;
<MS...Number to Text Value
I have data coming from Access via ODBC with a value of 0 (NO) or 1 (YES)
from a tickbox in Access.
Is there a way I can covert these 0 & 1's to a text word in excel in another
0 = NO or FALSE
1 = YES OR TRUE
Thanks in Advance.
"=?Utf-8?B?S1lNTw==?=" <KYMO@discussions.microsoft.com> wrote in
> I have data coming from Access via ODBC with a value of 0 (NO) or 1
> (YES) from a tickbox in Access.
> Is there a way I can covert these 0 & 1's to a text word ...Notes field in Data Analysis
Is there any way whatsoever that I can get the text from the task 'Notes'
field to be displayed in Data Analysis views?
Well, as I say to my customers, everything in a 'puter system is either a 1
or 0, therefore everything is possible. Is it easy to do what you want, no,
It would be custom develpment, but it would be easier to write an SSRS report
that listed tasks with notes...
> Is there any way whatsoe...Lookup value above cells with non-zero value
I have a table with two rows, 33 columns long. Top row has Years (200
to 2036) in it and the row below is intended for the inputing of value
in percentage form. Some of the cells in bottom row may be left blan
or with 0 value.
Elsewhere on my spreadsheet I want to create formulas that will retur
the top row value (year) for each year where the value in bottom row i
greater than 0.
For example, if
A1 = 2004 A2 = 2005 A3 = 2006 A4 = 2007
B1 = 0 % B2 = 5 % B3 = blank B4 = 20%
What formula in cell C1 will return value "2005" and in cell C2 th
next year value for which the cell in row...how do i retrieve the largest value from several records/rows in e
i'm trying to retrieve the largest value from a field. the records are many
but i only want to include the records related to a specific person. "DMAX"
seems to be the closest function that would do the job but ...
Assume your source table in A2:B2 down
names in col A, real numbers in col B
In E2 down are the inputs for col A, ie specific names: Name1, Name2 ..
In F2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula:
F2 will return the max value from col B for the name in E2. Copy F2 down as
required. Adapt the ranges to...Default value 07-11-07
Is there any way to initially put the Available qty = the In qty?
Because I need the available qty to be deducted everytime there is a
Message posted via AccessMonster.com
On Wed, 11 Jul 2007 03:08:54 GMT, "EMILYTAN via AccessMonster.com"
>Is there any way to initially put the Available qty = the In qty?
>Because I need the available qty to be deducted everytime there is a
You can use the AfterUpdate event of the Form to set the default value...need help calculating intermediary values
I'm not well enough versed in spreadsheet use to calculate some
intermediate values I need. I would be greatful for anyone who can offer
a formula or suggestion.
I have 1 guidline column, numbered 1-30. Then 2 other columns, A & B are
beside it. The values in A & B change as they go from 1-30, they change
at different rates. I have some intermediary values at given points but
need to calculate what the values in between would be, based on the
fixed values at 1, 4, 8, 15 and 30. Below, I have shown the layout as
best I can without including an attachment. Thanks.
chris@cjalexander...Search from value to value on text fields
I have a suggestion concerning search on text fields.
Fx the zipcode field. In Denmark we often want to search for an interval fx
5000 to 5999.
This isn't possible on Text fields in CRM. You have to create a search with
a lot of OR's in AX it is possible to use the syntax 5000..5999.
Or a selection of method Between or From and To.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
li...Web Query and Line Break <BR> tags
I have a problem that I have seen come up a few times in searching
through the archives, but I have not been able to find a solution (if
there is one).
I have a project that I believe an Excel Web Query would be perfect
for, except for one small problem.
When I run a Web Query against a website to pull in a table, and in
that table one of the cells has multiple lines separated by <BR> tags,
Excel puts the multiple lines into separate cells.
The ideal situation would be for it to use the <td> tags as cell
delimiters, not the <br> tags.
Can anyone think of any cl...Time values
I am trying to create a simple equation that subtracts 2 time values.
Cell 1 has 38 minutes and 24 seconds.
Cell 2 has 43 minutes and 20 seconds.
The difference between the 2 is 5 minutes and 4 seconds
How would I go about doing this?
How should I enter the numbers in a cell? When I enter 38:24, it makes it
into a Date and Time field.
What are my options?
Easiest is to enter minutes and seconds as
with the values in A1 and A2
then just subtract
returns 00:04:56 for me and not 00:05:04, you need to format as either
hh:mm:ss or ...Can't type in form field
I was emailed a Microsoft Word form I need to fill out, but when I tab to
each field, I cannot type in the box.
The document appears to be in design mode.
Thanks for any suggestions!
Turn off Design mode. In Word 2007, click the Design Mode button on the
However, if the form contains ActiveX controls, macro security settings
(which are user/machine specific) may force Design mode every time the
document is opened. The best way to fix this is to remove any ActiveX
controls from the form document (but I realize that you may not have any
influence over the d...Largest mailbox
We have some very large Exchange mailboxes in my organization and I'm trying
to convince them to use quotas. Our largest mailbox right now is nearly 10GB
with 100,000 items. Our top 10 mailboxes total 60GB together.
What's the largest mailboxes any of you have heard of? Are other
organizations storing this much email in Exchange? I can't imagine we're the
only email pack rats in the world.
Brian Spooner, MCSE
I have a user at 2 GB and I was hounding them. This is just not efficent.
It takes the data base for event to defrag and run other maintenan...Print field from different table
Access 2007 on WinXP
I have a report designed that is printing the department number, I would
like to print teh department name instead. The depatment number is included
in teh query the report is based upon. The report requires data from 4
differnt tables and when I try to add a fift table into the report
relationship the output data duplicates unreliably.
I work primarily with the click and drag method of report build in design
view as opposed to coding everything. What I want is to print the DeptName
filed from the TblDept table where the tblDeptID in TblDept is equal to t...blank cells having no value
though this is Steve's account it is his wife Val writing with my problem.
Back in the old days of DOS there was a function which allowed you to set all
blank cells as blank - that is = not having a value of Zero.
I am working on Excel 2003 at home and 2007 at work so am at the moment
somewhat confused and cannot find that facility on either system.
What I am trying to do is:
I have two cells one E6 representing "goals For" and another F6 representing
I have the following simple formula. =IF(e6>F6,3,IF(e6=F6,1,0))
this works fine...logical (if a1=specific word, can c1 = value entered in a2?)
I am trying to make a payroll sheet so that if someone enters the word "Stat"
into field a1, and then manually enters the number of hours worked into a2,
then c1 automatically displays the same value that a2 displays.
(I have 2 different columns at the end, one for regular hours and one for
stat hours, and I need them to display all of the regular hours into one
field and all of the stat hours into the column beside it.)
> I am trying to make a payroll sheet so that if someone enters the word "...