Sum data under special condition
I have a main and detail subform. In the subform I had a Check field (Yes/No
field) from the under laying table. I am having a problem of putting a
sum(amount) on the main form for only item that has a check in the Yes/No
Could someone advice?
I have Access 2003 on XP box.
A Yes/No field has a numeric value of zero when it is "No", and -1 when it
is "Yes". So, to achieve what you want, simply multiply the amount by the
Yes/No field, and by -1 e.g.
Sum(amount * yesnofield * -1)
"SF" <email@example.com> wrote in message
news:u9...sorting data, formula contance
When I sort a group of cells by highest number to lowest one column changes
the formula to match where the cell moves to. So if cell J9 moves to cell J8
in the sorting, the formula in the j column adjusts accordingly to match the
new position by one. I want the formula to remain constant. How do i do
that? Basically, if the formula in J9 is =E9/G30 and J9 moves to J8 I want
it to stay =E8/G30, but right now it keeps changing to =E8/G29. Catch my
It's a good thing you included formula, because the words don't match
what you supply as formulas. But that ...Copy Paste Chart format in Excel 2003 using VB.NET
I have a template file in Excel which contains some sample charts. I need to
generate some charts which has the same format as that of sample charts. I'm
using VB.NET 2005.
What we encourage people to do when using VBA is to run the Excel chart
recorder while performing the action they want to do in VBA, then fix up the
inefficient VBA recorded code and incorporate it into their project. You can
do the same thing, although there may be an additional step where you
translate or at least reference the VBA code so it works as VB.Net code.
Jon Peltier, Microsoft Excel MVP...can you look up info from a validated drop down menu in excel 2007
2 Name Wage
4 existing employee $20.00
5 existing employee $25.00
6 existing employee $17.00
7 Labor Ready ID $10.00
8 Labor Ready UT $10.00
9 Labor Ready WA $10.00
10 Future Employee $-
11 Future Employee $-
12 Future Employee $-
in another work sheet i have
a&b c d i
5 Labor cost
6 Name hours rate Total Cost
in a7-9 i have a drop menu based on a name I created employee.
employee='E...removing reference to other workbook
i have a excel wookbook which contains references to other workbook. whenever i open this wookbook it asks me for updating the link to the other workbook. i want to remove these references to other workbook but am not able to figure out which cell is refering to the other workbook. i also have lot of references in the same workbook. i dont want the internal references to be removed.
can anyone help me to accomplish this task. any help is highly appretiated.
thankx in advance.
try the following add-in to find/delete links:
http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindL...Copy a graph to a new workbook
A worksheet was created that contains data with an associated graph. The data
and the graph were copied and pasted into a new workbook. The data on the
graph points to the original workbook. Is there an easy way when you copy and
paste to have the graph point to the data in the new workbook without editing
the entire dataset and pointing it to the new workbook?
Maybe you could use: Edit | Move or Copy Sheet.
- Mike Middleton
Decision Analysis Add-ins for Excel
"Hubes" <Hubes@discussions.microsoft.com> wrote in message
news:E...Multiple data sets on the same graph
Any clue why a primary and secondary data set that are linked to the same
horizontal axis will not line up properly on the graph? For example, a marker
that is on the same vertical line as the data set above it will read a few
days ahead or behind.
...How do I change the Excel 2000 sheet tab size? It's too small.
For some reason my sheet tab size is very very small. How can I change it to
This is a function of your Windows Display Properties.
The size of the sheet tabs are governed by the setting for Scrollbars in
Windows Display Appearance. Mine are at 18. Start there and work your way
up or down. The font will increase or decrease with the size of sheet tabs.
Win98 as example...
Start > Settings > Control Panel > Display > Appearance(WinXP
"Item" drop down select Scrollbar
Change setting to Taste
Depending upon your s...Excel Vslow durin SavePreviewOrPrint. Netwk bcomes v bZ. Any adv?
I am running Office 2003 on my Toshiba WinXP P4 Labtop.
Recently noticed my Excel starts to give problem... I didn't know why it
First, when open or save large file (size), it runs very slowly, sometimes
showed NOT RESPONDING, then after 15-20 seconds, file opened (or saved).
Now, I also notice the slow down whenever I preview or print. During this
action, I noticed the network becomes very busy.
Therefore, I tried disabled network and worked on the same excel files. The
speed of opening, saving, previewing and printing are fast.
Any one there with similar situation and ...Conditional Sorting in Excel
I have what im hoping is a simple question. Basically, I am organizing an
event for 60 people and need to come up with table assignments for 4 separate
events. I am trying to put these 60 people into groups of 10, but I don't
want people from the same cities sitting together and I also don't want
people to sit with the same person multiple times (or at least as well as
possible). I basically have a 2 column spreadsheet - A is attendee names and
B is cities. Is there anyway that I can generate 4 distinct (or close to)
lists - each list containing 6 groups of 10?
...Can't change source data path for Pivot Chart
I want to change the source data path of a Pivot Chart that's pulling data
from an Access table. I tried following the KB at
http://office.microsoft.com/en-us/assistance/HP051991761033.aspx but when I
click on "Get Data" it just shows the other tables in that database, where I
want to change the file it's reading from (same database name just different
How do I do this?
there must be a better way but I do this..
double click on any DATA value in the PT and create a worksheet with
the rows making up that data value.
re-define the PT's source data as comin...Excel 2003: Duplicate dashboard and data sheets
I created a dashboard with several small graphs on it. The data for the
graphs is stored on a separate data sheet (i.e., work sheet). I now want to
duplicate the dashboard/data sheets to build a dashboard for another
employee. I envision the workbook eventually having 5 dashboards driven off
5 data sheets.
The trick I'm looking for is a way to change the datasource for the graphs
on the copied datasheet to the appropriate corresponding data sheet. I know
how to do it manually but it seems like there should be some sort of "find
and replace" ty...Access combo box control by another data field
I use a combo box in a form with look-up from a table for selecting data.
But, I want to only see a selection of the data in the table, depending on a
value in another field in the form.
I the combo box table I have ID, data1, and data.
data1 is the one that shall be equal to the other field.
1, 1, text1
2, 1, text2
3, 2, texta
4, 2, textb
5, 2, textc
I have tried using the standard query that comes with the combo box, and
then adding a WHERE statement testing against the data fiel in the form, but
it does not work.
Any suggestions anyone?
Check this link on &...How do I retrieve an Excel file data after my computer crashed?
My computer crashed and I lost the work I had done in an previously existing
Excel file. Is there anyway that I can retrieve this data? Thanks
depending on your Excel version maybe AutoRecovery helps. This should
come up after restarting Excel. But I would suspect your data is lost
> My computer crashed and I lost the work I had done in an previously
> existing Excel file. Is there anyway that I can retrieve this data?
...Saving an Excel Spreadsheet outside of Excel Recent Documents
Okay "boys & girls" you may think this is a primative question, but I just
can't figure this out:
When I save an Excel spreadsheet, the only way I can get back into it is to
open up my Excel program > Open the file I previously saved. And that is not
However, what if I wanna save that Excel spreadsheet into one of my computer
document foldders? Now that's the problem! If I click: Start > Documents
then I see all my special folders with all my special stuff. Well, I wanna
be able to save some of my Excel spreadsheets in a special fol...Excel 2003 Upgrade
If I only have Microsoft Works Suite 2004, and I purchase
the Excel 2003 upgrade only, will I get the full
functionality of Excel, or just an upgraded Works
Acccording to the Microsoft site, Works Suite 2004
qualifies for the upgrade, but am not sure what that
If Works does indeed qualify you to purchase the Upgrade version
of Excel, you will get the complete Excel application.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"firstname.lastname@example.org" <anonym...Problems entering numbers into Excel
Whenever I enter a number into my excel w/sheet, I get the
same number divided by 100. . . e.g. I enter 256 and the
cel contains 2.56 . . any clues???
really would appreciate it!!!!
Tools/Options/Edit uncheck the Fixed decimal places checkbox.
In article <email@example.com>,
"scuzzie" <firstname.lastname@example.org> wrote:
> Whenever I enter a number into my excel w/sheet, I get the
> same number divided by 100. . . e.g. I enter 256 and the
> cel contains 2.56 . . any clues???
> really would appreciate it!!!!
Many tha...Excel crashes when copying worksheet
Whenever I try to copy or move certain worksheets either within the same
workbook or to a new workbook Excel crashes. I cannot reopen Excel (vs.
2000) without deleting it from the Program Manager - it remains in the
I suspect the problem may have to do with having too many characters in
certain cells, although I carefully combed through one of these
worksheets and thought that I had fixed all cells so that none had more
than 200 characters and it still crashed Excel when I tried to copy
Does anyone have any solutions for this?
----------------------------------------------...Converting txt file to Excel
I have a text file imported into Excel that is setup as
Account Number Account Registration Payee Information
001511xxxxxx John Doe US Bank
123 First Street 100 Powell Ave
Las Vegas, NV 00000 Las Vegas, NV
001512xxxxxx Jane Doe US Bank
124 First Street 100 Powell Ave
Las Vegas, NV 00000 Las Vegas, NV
And this report can have up to 400 accounts. Is there a
way to related the Account Registration and Payee
Information to the Account N...Cannot double click an excel file and open it.
When I double click an excel file, the excel application opens with a
grey screen. If I drag the file onto the grey screen it opens fine.
If I double click an excel attachement the excel screen opens grey and
I get a message box saying The system cannot find the file specified.
If I drag an excel file into the outlook inbox and then double click
on it, it launches as it would normaly.
I have un associated and re associated the file with Excel.
I have checked the selections under the window menu in Excel, but
unhide is greyed out, indicating that excel has opened, but not loaded
the fil...Is it possible to view Excel split screen by tab within same file
I would like to view split screen tabs within the same Excel workbook. Is
Hi SS. Go to Window - Arrange - and select the view you would like. It
shows all tabs. Minimize or close the ones you don't want. I find the
vertical view the easiest to use. HTH
Sincerely, Michael Colvin
> I would like to view split screen tabs within the same Excel workbook. Is
> this possible?
Window|Arrange (horizontal/vertical/whatever you want)
Check windows of activeworkbook (if you only want that one workbook)
You'll see som...Enter some data into field
I would like to allow the user to type in the first letter or two into a
field and have the closiest data display.
Example: If they are typing the name Jonathon, when they put in Jo the rest
of the closiest match appears (john), as they type the rest, it keeps showing
the cloiest match (jonathon).
Is there a property value I can set to allow this to happen.
On Thu, 20 Dec 2007 11:14:00 -0800, Dan @BCBS wrote:
> I would like to allow the user to type in the first letter or two into a
> field and have the closiest data display.
> Example: If they are typing the name Jo...divide column(x) by column(y) to give column(x/y) in excel?
I'm trying to divide one column by another column to give me another column
of awnsers as in B1/C1=D1, B2/C2=D2 etc... Is this possible?
In cell D1 enter the following formula:
Copy this formula down column D as far as you need. The cell references
will increment automatically.
> I'm trying to divide one column by another column to give me another column
> of awnsers as in B1/C1=D1, B2/C2=D2 etc... Is this possible?
...Using Form to populate several tables
I want to create a form to track orders, however, the data that a user will
type in for an order is also on 2 other tables that contain more information.
Is there anyway to update that information as well?
Example of what I'm trying to say:
Table: Orders - Field: Circuit ID
I'd like to update the Circuit ID field in both Table: Entrance and Table:
DS3 without the user having to do too much.
Is that possible?
What about setting up one-to-many relationship with cascade upde related
fields and use form/subforms for data entry and viewing?
Build a little - Test a l...How do I enable Excel's "formula prompt?"
Excel on my old laptop would "prompt" me as I entered a formula. By prompt, I
mean that a small beige box (comprised of a single line of text) would appear
outlining the formula arguments as I typed. It was akin to the formula
wizzard, but not nearly as comprehensive - a kind of reminder of the
variables necessary to complete the formula. This ability was enormously
useful and appears to be disabled on my new laptop. How can I enable it?
The Function tooltips feature is available in Excel 2002 and later
versions. To turn the feature on:
On the General...