if and and statements
Looking for help with a 2 x 4 matrix with the following conditions
A1 B1 C1
3 0 1
3 0 2
3 1 1
3 1 2
I've this problem, when I drag the fields into the "Data"
area, it will show as "Count of Q1 Results". But what I
actually want is "Sum of Q1 Results". I would have to
manually go to field setting and reconfigure from count
It happens for all the fields I drag into the "Data"
area. Is there any way around this? Thanks.
If there are blank cells, or cells with text, in the column, Excel will
default to the Count function when the field is added to the data area.
If the column contains only numbers, it should default to Sum.
Derrick wr...How to paste a cells row from Excel to a PowerPoint 2003 Table row?
How to paste a row of cells from Excel to a PowerPoint 2003 Table row?
PowerPoint 2003 pastes all the row cells values in every cell in the row in
the PowerPoint table if I select the row in the PP table before the pasting.
PP pastes the row as an overlapping column if I place cursor in the first
cell of the target row before pasting.
...Create Pivot Table Reports
I just finished installing Analysis Cube on the server, everything seemed
working fine until when I try to create the Pivot Table Report
(Tools->Analysis Cubes->Create Pivot Table Reprots). After I called up the
pre-defined 'Definition ID' and click on the Excel icon, a message popped up
saying " The WHTemplate.XLT file was not found."
Anybody have any idea what that is, and how to resolve it??
1.) There are two pieces of software. Did you install both the server
piece, and the client piece?
2.) Is Excel installed on the machine, on which you'r...Case Lookup 04-21-06
Is there a way to prevent resolved cases from appearing in the case lookup?
Create an email in Outlook. Click the 'Track in CRM' button.
Click the 'Regarding' button. Change the 'Look For' to Cases.
Currently this lookup displays all cases with no restrictions. I would like
to restrict this to only show active cases.
Thanks for the post. I will have to take a look at this to see if there
is a way to filter the view. A quick glance at the issu...I would like to export the data from a drop-down list to a table
I have a form with several drop-down lists, I need to have the information in
these lists in another document/spread sheet. Is there an easy way to copy
the data in these lists to another location?
The long way would be to re-type all of it.
...Importing a table from Access query
What is the quickest and easiest way to do the above?
Thanks in advance.
The way I usually do this is run the select query, click on the top-right
box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste
into my workbook. An alternative is to right-click on the query in the
Database Window and left-click on Export. In the Save As dialog box, select
"Trish" <Trish@discussions.microsoft.com> wrote in message
> What is the quickest and easiest way to do the above?
>...Write conflict error with ODBC link table
I have migrate my back end access tables to SQL Server. While editing data
(ODBC link) from form, I receive Wirte conflict error 'The record has been
changed by other user... Copying the change to the clipboard...'. The error
allow me either copy the info to clipboard or drop change. In this case, how
can I save my work to the table here?
� "SF" <firstname.lastname@example.org> ������ ��� ������
> I have migrate my back end access tables to SQL Server. While editing data
> (ODBC link) from form, I...NESTED IF STATEMENT
This is a multi-part message in MIME format.
I have to calculate commission on sales tax collected based on the =
1) If sales tax collected is $11.00 or less, then the commission =3D to =
the sales tax collected
2) If sales tax collected is $11.01 - $333.33, then the commission=3D =
3) If sales tax collected is more than $333.33, then commission =3D 3.3% =
of the sales tax collected, with a maximum amount =3D $99...Detecting Case of Text in a Cell
I know it's possible to change the case of text in a cell in Excel
using Lower, Upper and Proper, but is there a function that tells me
the current case of the text?
A user has a spreadsheet of catalogue items, some in proper case and
some in upper. He wants to seperate those in upper case into a separate
sheet but I can't think of an easy way of doing it.
Can anyone help?
Colin Vicary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10472
View this...IF Statement 01-17-08
I am trying to run a query that has 4 fields: location, type, year, and
cost. However I need to increase the cost for the year 2007 for 2 types.
The IF statement I entered into the cost field is:
TotalCost:IIF([Type]="C" Or "H" And [Year]=2007,[SumCost]/.9,[SumCost])
The issue I am having is that it is increasing the costs for all types. Is
there a way to fix this or do i need a new query for C and H and then combine
the 2 queries? Thanks for any help you can provide.
Actually, you don't need to do it that way. Create a Where clause for the
query tha...Help with SQL- SupplierList cost into Item cost field
Hi, re-posted! See below.
Hi, thanks for that- it worked perfectly. There is just one snag however and
I didn't think of it until I was testing. Many of the suppliers use specific
currencies so the information I need copied from the Supplier tab is actually
the Local Cost. When I view the SupplierList table, Local Cost doesn't
appear as a column. Is it just a calculation based on exchange rates for
information or does it actually exist somewhere within the database? Your
help once again would be much appreciated.
> Hi Tara - backup your...Need a formula that tags one table based 2 columns in each table
I have two tables on one worksheet, Table A and Table B. Each table
contains two column with X and Y coordinates, all values are numerical. The
coordinate system is irrelevant. I need to "tag" all XY coordinates in
Table B that have a matching XY coordinate in Table A. If Table A
coordinates are in columns A and B, and Table B coordinates are in columns C
and D, then I want to place the text "hit" in column E next to each
coordinate pair from columns C and D that match a coordinate pair in columns
A and B. The ranges for the respective tables have been named ACOORD and...database question / feedback needed (GP8
We sold one of our companies, and the new owners are moving off Great Plains.
They are transferring/rebuilding data in their new software on their own.
For historical (GP) data, they have purchased some software to perform
queries, and have requested I give them the data.
I am planning to send a backup of the SQL Company dbase.
They would not have a GP client, and would not be performing transactions -
so I believe they really just want to access the various history tables.
I prefer not to send the Dynamics dbase if I don't have to (passwords, etc).
I guess my ques..."How do I get rid of old records in an Pivot Table?
The spreadsheet gets copied each month to a new file and the data cleared
out, BUT the selecetion in the Pivot Table still holds the data from Previous
Debra Dalgleish has some techniques at:
> The spreadsheet gets copied each month to a new file and the data cleared
> out, BUT the selecetion in the Pivot Table still holds the data from Previous
The web-link was very helpful, thank you. I was about to reprogram and build
my pivot tables to get rid of the obsolete item...Adding multiple tables in one report
I am trying to customize the default report Daily Detailed Sales with Tax.
What i need to do is add the Tender Type (Credit card / cash / check) as
another column in the report.
So far i have found out:
i need to add a column
i need to import the TenderEntry table for the data
I need to find out:
How to import another table
I have tried to import the table using sql UNION function, but that wont
work for me either. any help would be greatly appriciated.
The things make sense to me regarding the adding tables and fields which you
can do and customized the .grp file. ...Money looped
Online services wizard caused error "Money has encountered
a problem and needs to close. Send report to Microsoft!
And restarts and closes. And restarts and closes. .net
passport not working correctly.
..net Unable to verify email.
Email works, please respond to email.
...Pivot table help I think !
I have a report I need to create which goes as follows.
I have a data list of around 56k records.
The rows contain this; category 1, category 2, category 3, call id and
I've created a pivot table with Cat1, cat2, cat 3 on the row area,
Count of Call ID in the data area and date in the column area (this is
grouped by Month).
I'm looking at the months of Mar, Apr and May. I want to sort it, in
descending order, by the difference of 'Count of Call ID' there is
between Mar and May.
If I cannot perform the calculation in the pivot table, is there a way
of ungroupi...Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes
reaches around 500 characters. Not huge, but larger than the Text limitation
to which is what Access 2007 of course converts this. How can I force the
data type to be Memo while I'm in the query so the resulting table displays
all the data without any truncation.
I don't believe you can.
I think you'll have to create the table first, and then append to it.
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"E...Pivot Table toolbar
Every time I edit a pivot table (Excel 2000)the toolbar
launches itself and I have to then re-anchor it manually -
this is very irritating!
Is there any way to stop this happening?
"GeoffS" <email@example.com> wrote in message
> Every time I edit a pivot table (Excel 2000)the toolbar
> launches itself and I have to then re-anchor it manually -
> this is very irritating!
> Is there any way to stop this happening?
I use Excel97 but it may be the same. You can have the toolbar on all the
time, positio...move SQL to dedicated 64 bit server
Hi, I have a 32 bit Windows 2003 server with GP 9 and SQL 2005 installed.
I would like to move the SQL databses to a dedicated Windows 2008 64 bit SQL
I would like to leave GP where it is, but move the SQL database to the new
server for performance and backup purposes.
is there a KB on how to do this or can anyone help point me in the right
You can find a link to the KB article on how to perform this operation on my
You may also want to look into the...table of contents
I am trying to build a table of contents from the headings
in my employee handbook. I have heard that this is
possible, and if I change the content of the handbook, the
page numbers, etc. will automatically update. What is the
process to accomplish this?
I would appreciate any insight given. Thanks!
...Pivot tabels -- Incorrect sorting of specific value in pivot table
A message was posted yesterday that has not shown up so this is a second
I have a pivot table that consistently places a value at the top of the
sorted llist (bottom if decending) even though that value should be in the
middle of the list. A sample of the values are ALC, SNO, CET, ESU, TEL,
STR, JUN, NKA, NTL, blank. The JUN value is always at the top. Any value
placed in the field that begins with J shows at the top. If the value is
changed to any other letter, it sorts correctly. The data has been reentered
at the source, the query checked, and the downloaded data chec...pivot table in reverse
I' ve got a spreadsheet with events as row headings, people's names as
column headings and dates in the cells. E.g.
Person 1 Person 2
Birthday 01/07/61 05/10/67
Anniversary 10/08/90 10/11/92
(Actually the list is much longer....)
Now I want to make a new list list converting the above data to [dates
(in column 1; event in column 2; person in column 3]. Sort of pivot
tables in reverse......!!
I may not be reading your post correctly but I think searching fo
"Transpose" in the He...Using MicroFramework to build my own GPS device?
But what I really need to learn at the moment is how the devices like TomTom
and Garmen function? I don't have one obviously so is there a hardware forum
for Microsoft devs? Do they run using batteries? Which protocol is used if
they can communicate with the vehicle when these things appear to be dash
mounted with a suction cup?
"Hillbilly" <firstname.lastname@example.org> wrote in
> But what I really need to learn at the moment is how the
> TomTom and Garmen function? I don't have one obviously so ...