Form creates unwanted rows in table
I've created an asset database which keeps track of computers, laptops
etc. There is one company which has many departments and every
department has many rooms. (surprise, surprise)
There are four tables: tables Department and Asset and two "middle"
tables DepartmentRoom and AssetRoom.
The table structures are as follows:
depID (foreign key to department)
assetID (foreign key to asset)
roomID (foreign key to depa...Pivot table calculated field: multiply Sum of FieldA with Max of FieldB
My question concerns Excel 2003.
A user asked me about a calculated field in a pivot table.
Some of the fields are DT_NOW, GW_WCR, GW_LOB.
The formula of the calculated field should depend on the date
Up to 30/06/2009: Rotation = GW_WCR / GW_LOB * 91
Starting 01/07/2009: Rotation = GW_WCR / GW_LOB * 28
She added a field to the underlying data called ROT_FACTOR, filled
with 91 until 30th june and filled with 28 starting 1st july.
The current formula is
The cutoff value 300000 is an arbitrary value base...Word's Formatting Palette
I've worked with Word in Windows and Mac equally. There are features in
both that are wanting in the other.
In Word for Windows, the Formatting Palette is a wonderfully accessible
window that can be made as large as screen real estate allows.
In Word for Mac, the Formatting Palette is a tiny fixed-sized window
that offers few advantages over the menu bar.
This is just one of many features I hope to see improved in Office 2008.
Frequently, I wonder if the developers actually use the applications
they work on and if there is any exchange between Windows and Mac
developers. If a function ...Conditional formatting?
I have rows of different activities listed by description. I now need to
allocate numeric codes to those descriptions. There are about 20 different
codes I need to use. Is it possible to then total those codes on the bottom
PER code (i.e. all codes of 115 total on the bottom in one cell, all codes of
135 total on the bottom in another cell) or do I have to do a separate column
for each code? Thanks.
Look at SUMIF(). If codes in column A and values in column B:
You could even have a cell set up to enter the...Why won't ADDRESS work nested inside SUMIF?
If I do this:
If I do:
I get an error. However if I do:
I get the answer I want. However, I'd prefer to not have a helper cell.
Some lateral thoughts, with emphasis on resolving the core issues you raised
in your other, earlier thread
Let's say you have col data in groups of 3 cols, with headers in row2 (eg:
Period1, Period2, etc), where each header is placed only in the l...Cells losing formatting
This is driving me nuts!
I use excel 2000.
From time to time certain cells (usually blocks of cells or an entir
row) containing numerical data lose all formatting. Excel no longe
recognizes the data as numbers and will not sort them etc. Selectin
the cell(s) and changing the formatting has no effect. Copying, the
"paste special / values" only works sometimes. Manually retyping th
data over the old usually works but I am dealing with a spreadshee
with about 1000 records.
I just had this happen with a row containing date / time stamps.
Each cell has an entry like "7/26/04 9:5...Does GP allow for different Account Formats for multiple Companies
I am setting up a new client that has 3 different companies. Two of them
have the same GL framework. The third has a diffferent account framework.
How can I change the account framework for the new company?
Thanks for your help.
If they are different databases simply go to Tools >> Setup>>Company
Leslie Vail, CPA, MCT, MVP
MCBSP-Application for Microsoft Dynamics GP
MCBSP-Installation and Configuration for Microsoft Dynamics GP
> Hi All,
> I am sett...How to create temp table ?
Can I create temp table by myself for report writer ?
Sorry, the answer is no.
You need Dexterity to create a temporary table and a
Dexterity script to read other tables and populate the
temp table's contents.
You may be able to use VBA with ADO to obtain the data you
need for your report.
Senior Development Consultant
MBS Services - Asia Pacific
Microsoft Business Solutions
Any views contained within are my personal views and
not necessarily Microsoft Business Solutions polic...Pivot Tables #17
Monthly dump info from Access into Excel and then do a
pivot table. I am able to refresh the data in the pivot
table from the raw data fine, but I can't group on the
Date field. The raw data has each day in the month and I
group it into a month. When I try to group the Date
field I get the error "Cannot group that selection." Any
help would be appreciated. Thanks
The dates have to be numeric, that way you will get the option of grouping
week etc. If they are dates and look numeric as in 12/15/03 or similar
depending on the region, select an empty
cell, copy...How to change existing table record value by VBA and How to add new record in existing table by VBA
Hi all, I got table in my database with name "tbldata" and i have two
fields in that table with the name "Ticket_No" and "Amount". In
"Ticket_No" filed column i have value "SD001" and in same row of
"Amount" column i have figure "50". With VBA how can i lookup for
value "SD001" in "Ticket_No" field and change figure "50" to "30"
which is appearing in same row of "Amount" column field. My other
question is that how can i add new record in same table with VBA. I
want...Number format #17
I have a cell containing:
="Number of combinations = "&COUNTA(C2:C1500)*COUNTA(D2:D1500)
This number easily exceeds 1 million which makes it diffcult to read
with all the digits in one long row, so I would like to format it with
I could make the calculation in another cell e.g. C25 and then write:
="Number of combinations = "&C25
and then format C25 to thousandseparators, but that does not change the
appearance in the cell where I combine it with the text.
Is it possible to change the number format in such a case?
---------...Pivot table formats
I have created a pivot table that has three fields; Date, raingauge and rainfall. I want to export the output of this table into an Access database. However the table produced only lists date as a single entry instead of listing the date for each raingauge. Therefore I have to manually fill in the missing date data to export it to Access. Is there any way of formating the date field so that it is listed all the way down the table
What you can do is copy the pivot table and paste special
> value over itself. Select the entire range with the
gaps, press F5 > Special > Bl...How do I turn off auto syncronization of formatting?
The instructions in Help for Publisher tell you to click on Edit, and then
click on Undo synchronize. Those are simple instructions, however, there is
NO option under edit to undo synchronize. How do you urn off the automatic
synchronization feature when you use Publisher's templates?
Peggy, after a synchronization happens, just click on the undo button on the
toolbar. When you do this, it will automatically turn off synchronization
for the remainder of your work in that Publication.
Microsoft Publisher MVP
This posting is provided &...Formatting Issue #2
I have a cell range that have Last Name, First Name
format. The problem is that some cells have a space
between the comma and the first name and some have no
space. This is causing a problem when I try to use the
=substitute(A1,",",", ") formula because it adds an extra
space in the ones that already have a space. Any ideas on
how I can keep the ones that already have a space from
getting an extra space? Thanks for the help.
=substitute(substitu�te(A1,", ",","),",",", ")
...setting up tax tables in RMS
I have a client who has special tax needs.
$0-$1600 is taxed at 9.25
$1601-$3200 is taxed at 9.75
$3201- and above is taxed at 7%
so for example- for a $3,300.00 item- the tax would be $311.00. How would I
set this up in RMS?
Create 3 Sales Taxes.
Sales Tax 1
Minimum Taxable Amount = None
Maximum Taxable Amount = $1,600.99
Tax Rate = %9.25
Sales Tax 2
Minimum Taxable Amount = $1,601.00
Maximum Taxable Amount = $3,200.99
Tax Rate = %9.75
Sales Tax 3
Minimum Taxable Amount = $3,201.00
Maximum Taxable Amount = None
Tax Rate = %7
Assign all of these taxes to one...Kits and Kit Components and SQL Tables
In the past few weeks I have been working on pulling data into an Excel
Workbook from various GP Dynamics tables. The purpose of this workbook is to
compile data to be used in a cash flow analysis. I have posted several
questions on here and responders have been very helpful in answering my
questions. I have a new one that hopefully someone can answer...
I am pulling in sales line item data from tables SOP30300, SOP030200 as well
as IV00101 (for item type). I am using IV00101 because some of our sales
include items set up with an item type of "service" so the costs that ar...Adding a calculated ROW to a pivot table
Does anybody know how to add a calculated ROW to a pivot
I have a pivot table that is returning totals at the
bottom, as it should, but I *also* need it to return that
total as a percentage of grand total, directly beneath the
I've always done this free-form in the cells below a pivot
table before, but the size of this pivot is dynamic so
that's not an option.
Also--I'm using the pivot in Access, not Excel directly.
Anybody have any tips? Thanks!
...Convert degree from decimal number to the standard format (degre.
How to Convert degree from decimal number to the standard format (degree °
minutes ´ seconds″.
I tried custom but could not work out for some degrees.
On Sat, 26 Mar 2005 08:23:01 -0800, "Zainelabdin"
>How to Convert degree from decimal number to the standard format (degree �
>minutes � seconds?.
>I tried custom but could not work out for some degrees.
Here's one way:
Divide your decimal degrees by 24. Then format the cell as:
Format/Cells/Number/Custom Type: h� m' s.00\"
...i want to set conditional format to cells containing odd numbers
i want to set conditional format to cells containing odd numbers
1. Select the cell/Range (say A1:A10). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK
PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New...How do I create a one variable data table? #2
...Need to remove data in table due to input mask; cannot close.
ACCESS Table open, unable to close or modify telephone field. Had tried i/p
mask of 0 due to "Unlisted" numbers appearing right-justified when doing a
report. I sure thought I changed it back to a NO MASK prior saving the table.
Now, table is open and I am unable to do anything with that field, even
deleting the content, to satisfy the mask problem. Hands seems to be tied in
'catch 22'; I would have thought (!!??) that the table would not have saved
properly if the input mask did not match the field contents. I'm stuck!
Thank you so much.
=?Utf-8?B?b2ZmZXJvY2t...Pivot Table to Ignore N/As
Hi, Does anyone know if it is possible to get a pivot table summary to
ignore N/As within a table when summing the data?
Maybe you could clean them up in the original data (if those cells are a
Or even use another column:
Then use that other column in the pivottable.
> Hi, Does anyone know if it is possible to get a pivot table summary to
> ignore N/As within a table when summing the data?
Thanks Dave, that's what we figure...Outlook 2000 fails to send HTML formatted messages
When I try to send an HTML or RFT formmatted message I
get the following response:
Microsoft Outlook has encountered a problem and needs to
close. We are sorry for the inconvenience. If you were
in the middle of something, the information you are
working on might be lost.
Please tell Microsoft about the problem.
We have created an error report ...............
The error report states:
AppName: outlook.exe AppVer: 184.108.40.20604 ModName:
ModVer: 5.1.2600.1255 Offset: 00006d79
I am using Windows XP professional, Word 2000, Outlook
2000. I am able to send plain t...Format Text
I'm trying to format order numbers in excel and I want dashes to fill in
automatically. The problem is I have numbers and text for each order. For
example: SRT46510D1234 is what I want to type in, but I want it to appear as
SRT456-10-D-1234. Is there a way to make that happen?
You can not have th eformat directly in the cell you are entering in. XL does
not have a facility for input masks or formats on text. You can output the
formatted text in a seperate cell with a formulas such as this
=LEFT(A1, 6) & "-" & MID(A1, 7, 2) & "-" & MID(...Adding format to "save as" sub-menu in Office 2007?
I save all my files as either .xlsx or .csv. Usually I need to save
the same file as both - once to preserve features such as charts and
once in .csv for import into another application.
Is there a way to add a format to the "save as" window? That is, I
would like to be able to click the office button, click "save as", and
have .csv appear in the list of formats alongside "Excel workbook",
"Excel macro-enabled workbook", etc.
Granted, I can choose Save As>Other formats and scroll down to .csv in
the save dialog, but I do this often enough that it wo...