Exclude data from field, when another field equals a certain numbe
I have 2 fields. I have Job # and GL #.
I need to exclude from the GL field, values that are 1234, but ONLY if the
Job # = 999999.
I have tried using this formula in the criteria of the GL# field in my query:
IIf([Job #]=999999,<>1234,[GL #])
and instead of 999999 still showing up in the query and excluding GL# 1234,
999999 doesn't show up at all!
What am I doing wrong??
IIf([Job #]<>999999, [GL #], IIf([GL #]=1234, Null, [GL #]))
I'm assuming by exclude that you mean Null.
Jerry Whittle, Microsoft Acce...email addresses in cells
If I enter an email address into a cell, Excel underlines it and then treats
it as a live address. This makes it impossible to edit. The only solution I
have found is to use Paste Special, but even then the pasted version becomes
live again as soon as you have finished editing it.
Does anyone know a way of switching this maddening feature off - either
temporarily or permanently?
In XL 2002 and 2003 you can turn this off through Tools>AutoCorrect
Options>AutoFormat as you type.
Uncheck the box you find there.
Gord Dibben Excel MVP
On Tue, 26 Apr 2005 15:42:01 -0700, tonyay...How to convert CString object to data type?
How can I convert CString from a EDIT control Dialog to data type for
Take a look at _stscanf/_ttoi.
Y Xu wrote:
> How can I convert CString from a EDIT control Dialog to data type for
Hi Y Xu,
Can you be more specific , what type do you want it to be converted to
Y Xu wrote:
> How can I convert CString from a EDIT control Dialog to data type for
On 8 Jun 2006 07:31:57 -0700, email@example.com wrote:
>Hi Y Xu,
>Can you be more specific , what t...Name Range with using Data Form
I first created a variable with A1:D1, four columns with named variable
Data. How to expand this Data range to A1:D2 after using the DataForm to add
one row data in the worksheet?? The same, the Data range should be A1:D3
when adding one more data row...
use the following formula in the name definition dialog
>I first created a variable with A1:D1, four columns with
>Data. How to expand this Data range to A1:D2 after using
the DataForm to add
>one row data in the worksheet?? The...Concatenate mid, left then right data
I have searched other messages and my brain must be on holiday because
I cannot get any of the answers I found to work for me.
What I have is a column of data which has to now be changed for GL
Reconciliation, looks like this-
And I now need it to look like this-
Most probably very simple but it still eludes me.....
Any help would be much appreciated.
","...Why do Data markers in Excel 2003 appear to be bold?
When crating charts in Excell 2003 the Data Markers (numerous points) appear
to be bold and blurry. This does not occur in Excel XP. Marker size is set
...: in cell references in formula (Excel 2003)
Someone put the following formula into a spreadsheet that I have to maintain:
I understand the use of : to pass a ranged to a function, but this does not
make sense to me. It only adds the values from H14, H18, and H24. Is the
use of the : here a typo that happens to work, or is there a good useful
purpose for it?
It doesn't seem like that should be working. I get a #VALUE! when I use it.
If it was intended to sum the range plus H24, then:
If only those three cells were supposed to be added, then:
=SUM(ABC!H14,A...How do I link columns so data flows from 1 column to another like.
Is there a way to link columns in Excel, so that data that's entered flows
from one column to the next? There's a function similar to this in Quark.
Anybody know anything about this?
If I understand the question correctly, and the information is being typed
into the cells, you could try this:
Select a block of cells, say A2:C10
Then, type a value. Press enter. If yuo press enter after each value
entered, when you enter a value in A:10 and press enter, you're active cell
will be B2.
"M. Frazel" wrote:
> Is there a way to link columns in Excel, so t...Freezing Cells #2
Here's the problem, I like the idea about being able to freeze the the panes of a worksheet. However I would like to freeze certain cells, for example I want to just freeze I1 through I10. The freeze panes option grabs A1 through I10 which is not what I want. Any ideas on how I could get around this?
You can't freeze only part of a row or column. Freeze works only
on entire rows and/or columns.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Illusvfyre" <firstname.lastname@example.org> wrote in message
A1 contains "Smith"
A53 does not contain "Smith"
A459 contains "Smith"
A1072 does not contain "Smith"
How do I get "Smith" to appear in:
B1 thru B52
B459 thru B1071
and nothing in the other cells?
see you post in Excel.misc
so you only want to duplicate the "Smith" entries. Try the following:
In B1 enter
in B2 enter
> A1 contains "Smith"
> A53 ...Double stacked columns
I need 2 stacked columns side by side for each month.
Here is the data for October:
Forecast Credits 200
Forecast Cash 50
Actual Credits 220
Actual Cash 55
Total Forecast 250
Total Actual 275
One column is forecast; the other is actual.
I would then need a line plotted on the secondary axis for Total Actual.
Can it be done?
So you would like a clustered-stacked column chart? Excel doesn't do
this type of chart natively, but with a little clever arrangement of the
data you can make a stacked chart look the way you want. This page links
to a handful of examples:
http://peltie...Data Charting Correctly
We are working with a table that has a Sell Price, List Price, and Profit
Margin. When we try to chart it, it looks like it is adding the "Sell Price"
and "List Price". The "Sell Price" which is in row 1, is plotted correctly.
The "List Price" looks to be the total of the two. The "Profit Margin" is
plotting with the "List Price".
Is it in the way we have selected the table to chart? Does anyone have any
Choose the line chart option in the first column, not the stacked line chart
option in the second.
...2 column subreport problem
I have a report with a single column.
I have a sub-report in the detail section of this report. The sub-report has
Only one column prints in the sub-report.
TIA for your help.
Never mind....I got it.
Gettin' old ain't for sissies!
"R Fourt" wrote:
> I have a report with a single column.
> I have a sub-report in the detail section of this report. The sub-report has
> two columns.
> Only one column prints in the sub-report.
> TIA for your help.
...duplicate entries in rows
I have a list of names from a few different sources, I need to compile
them into one list, and would like to eliminate any multiple entries by
using, I'm assuming, a filter.
To clarify what I need, here is an example:
here are the rows:
I'd like to use a filter where I could select all of the names, and
eliminate the duplicate entry of the name "mitch". Is there any easy way
to do this? Thank you.
use 'Data - Filter - Advanced Filer' and check 'Unique entries'
> I hav...Separating Names in a Column to Two Columns
I need to export a great deal of data, where the last and first names need to
exist in two separate columns, i.e.,
Doe, Jane to Doe Jane
Is there any way to accomplish without retyping?
Look at Data>Text to columns in the main menu
Microsoft MVP - Excel
"donniebrook" <email@example.com> wrote in message
>I need to export a great deal of data, where the last and first names need
> exist in two separate columns, i.e...DoPromptFileName() Freezes on Second Call
I have an MFC MDI application that freezes the second time "Open File" is
selected. The freeze happens on the second call to DoPromptFileName() in the
"Open File" handler. I have found it does not matter where in the
application DoPromptFileName() is called from the second time, it will
freeze on the second it is called.
I have done a "Trace Into" trace with the F11 key and can get as far as the
statement in the CDocManager::DoPromptFileName() function:
INT_PTR nResult = dlgFile.DoModal();
at line 640 in docmgr.cpp. After that it goes into assembly code I ...reference is not valid message for pivot tables based on Access query
I've looked around but can't find an answer to this.
I have a workbook - an xlt - that has 6 worksheets that pull data from
different queries in the same Access database.
I have several worksheets with on pivot table each. The pivot tables are
based on dynamic named ranges in the external data worksheets. I use
dynamic ranges because I have some adjacent calculated columns in the
The workbook has only 6 pivot caches - the same as the number of worksheets
that pull data. I copied pivot tables from sheet to sheet so that they'd
use the same cache, where approp...import data from access table into a existing table
I need to map data from am existing access database into a existing staging
database. The access database only has the data I need, so the mapping is not
a one to one, The SQL table many more columns so I need to do some manual
mapping. Is there a way to do this throiugh the import wizard or do I need to
write some type of query to manual insert the data?
Thanks in advance.
Message posted via SQLMonster.com
This is SQL Server forum, you will be better of to ask on Access forum
"Jay via SQL...format new rows
My table looks like this:
A B C D E
1 Organisation Adress Contact Outcome
Jacob Skaria was able to help me generate a new row when data is entered
into each category [(eg when new data is added to B2 a new line will
automatically appear at 3 to allow room for new data for the employer
category). A new line would insert for each of the column A categories in the
same ...Static reference to worksheet in formula
I have several Documents. For example one is called Budget 2007, Budget
2008. In each of these I have worksheets for each month, Jan, Feb, etc... I
also have a worksheet called Year. In the Year I have cells that reference
the month worksheets. For example I might have =Jan!$B23
My problem is that when I now copy the worksheet Year to the new file the
cell reference is ='C:\[BUDGET 2007.xls]Jan'!$B$23
I can't edit the links because I do have some formulas which I want to
reference previous years and otherwise will create lots of circular
2 questions I have.
1)...Reflect cell contents into another worksheet #3
I have forgotten how to reflect data that is present in one worksheet
cell into a specific cell of a second worksheet. IOW, repeat cell
contents in a second worksheet.
I simply do not make use of MS Excel97 often enough to remember the
~ Vince ~
You can just type = and then click the cell if you want to be duplicate.
> I have forgotten how to reflect data that is present in one worksheet
> cell into a specific cell of a second worksheet. IOW, repeat cell
> contents in a second worksheet.
> I simply do...Want to create sliders to change cell values in EXCEL for PC
As a math teacher, being able to view chart/graph changes as equation
parameters change is an extremely valuable tool. An article in the NCTM
Mathematics journal describes this capability using "Scroll Bar type Sliders"
in EXCEL 2004, which is evidently for MAC's. I have a PC with EXCEL 2003.
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
link to open the suggestion in th...Pulling data from one sheet to another
I'm having problems with retrieving certain data from one sheet to another and I don't know if it is possible.
See the below data as an example of what I'm using:
VERSID DOC LH EN VELS COUNT
1233 Duplex 100 500 503 G
1233 Duplex 101 501 520 I
1675 Simplex 100,105 500 520 G
1675 Simplex 101,106 501 503 I
1674 Simplex 100,105 500 520 G
1674 Simplex 101,106 501 503 I
Versid numbers are unique to each set of data, however the sheet I want to tie this up with use a different set ...Cell Selection after "Enter"
In the Excel applications we build for others it is useful to have the cell
selection remain on the cell after the data in it is modified. This is done
by unchecking "Move Selection after Enter" on the "Edit" tab of the
"Options" dialogue. However, when protecting the worksheet to allow
selection of unlocked cells only, the cell selection then ignores the Edit
tab setting and moves in the default direction after Enter. If worksheet
protection is modified to also allow selection of locked cells, then the cell
selection will obey the Edit setting and not ...how do i arrange column A (last name) in alphabetical order? t.
my phone book is in excel. how can i alphabetize by last name (column A)?
Click on any one last name.
Then, Look for the Sort Ascending Button on the Standard Toolbar. It looks
like a Blue A above a Red Z. Click that button once.
Click on any cell in the phone list (such as a last name).
Go to Data | Sort.
Make sure it is selecting the Last Name Column in the Sort by position.
> my phone book is in excel. how can i alphabetize by last name (column A)?
I don't trust excel to guess the range to be sorted.