Please help! Displaying only rows with empty cells in a numeric field using Advanced Filter. Thanks
I am learning how to use the Advanced Filter (Menus: Data, Filter, Advanced Filter). I can’t use Autofilter because in some cases I have to look for records that have to meet more than 2 conditions in the same field
Some of the fields in the list that I am trying to filter have only numeric values. One of the conditions I am trying to define would allow me to filter the list exposing those rows in a numeric field that are blank (empty cells containing no data). I have tried using the following wildcard and formula combinations�
<>* , =C11="" and <g
… in the criter...Avg. 1-month, 3-month, 6-month & 12-month Stock Returns
I don’t know for sure if Access can handle this request, but I know Queries
are pretty powerful, so I’m thinking it is possible. As I alluded to in the
title of the post, I’m trying to find a way to query for the average of
1-month stock returns, average of 3-month stock returns, average of 6-month
stock returns & average of 12-month stock returns.
This is my SQL now:
SELECT SharePrices.StockSymbol, Avg(SharePrices.StockPrice) AS AveragePrice,
tblStocksGroup.Company, tblStocksGroup.Group, tblStocksGroup.Class
FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSy...Retain cell references when column moves
Column C in a table contains data which is pulled into a chart:
I want the chart to pull data from column C even if I insert a new column
to the left of Column C. In other words, when the current Column C data
moves to the right and becomes Column D, I DON'T want the reference to change
I want it to stay
and reflect the new values that are in the new column C.
The Indirect function does this for a single cell, but I can't seem to make
it work for a range of cells as shown above. Any way to do this?
Every time a n...Adding a second line on a two axis column stacked chart
I have a stacked bar graph showing 4 data series on the x-
axis with a line showing 1 data series on the secondary
Now I want to add a second line to the secondary axis
When I do so it automatically becomes a bar instead of a
line, regardless which axis I am plotting it on.
Is there anyone out there that knows what I am doing
wrong and/or what the trick is ?
The trick is this: Don't use the "built-in custom" chart types, but make
your own. Make your chart with all series as stacked columns, then
select each one you want to change, and use Chart Type on the Ch...Return cell based on another cell help!
I have a spreadsheet with a list of codes in column A, a Master list of
codes in column C, and a Master description in D that is associated with the
values in C.
If a code exists in A, I would like to check if the same code exists in C.
If so, I would like to populate column B with the description in D.
Can anyone show me how to do this please?
Try this in B1:
Please keep all correspondence within the Group, so all may benefit!
============...Finding thing errors quickly in a huge column
OK guys. I have a column with 20,000 dates in. They should all be in
the format that is DD/MM/YYYY, but some times people have only put days
and months and occasionally random characters are in there too.
Is there a formula to search for cells in column A that are not
DD/MM/YYYY? I have tried using autofilter and stuff but that is not
Many thanks gang!
...Columns labeled as numbers, not letters!
Help! I have office 97, and the columns are labeled as 1,
2, 3, 4 rather than A, B, C, D. The rows are still labled
as numbers. This makes working with formulas and
spreadsheets too frustrating!
Go to Tools > Options > General tab and uncheck the box
for "R1C1 reference style".
>Help! I have office 97, and the columns are labeled as
>2, 3, 4 rather than A, B, C, D. The rows are still
>as numbers. This makes working with formulas and
>spreadsheets too frustrating!
>Thank...Deleting columns if value of cell is not matching
I have one workbook which contains 20-30 sheets and from these i would like
to check whether column C1="Expired" if value is matching then i would like
to delete entire column from all the sheets except two sheet (sheet1 and
Please help in this regard..
You say " i would like to check whether column C1="Expired"...". In what
sheet? Or do you mean you want to look in cell C1 In EACH sheet and, if
found, delete Column C in THAT sheet? As written now, you say you want to
delete column C of EVERY sheet ...Auto fill column with sequential numbers with decimals
Want to auto fill as follows: 3, 3.1, 3.2, 3.3, 3.4
What method works? Can only seem to get sequence on primary number, example:
3.1, 4.1, 5.1
I want the number to the right of the decimal to change.
Enter 3 in a1
Enter 3.1 in a2
Select both and drag down using the fill handle
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
> Want to auto fill as follows: 3, 3.1, 3.2, 3.3, 3.4
> What method works?...Return Read Receipt Loop Madness
I am having a problem with Outlook 2003 and return read receipts that I
was hoping some MS gurus might have an answer for. We use an offsite
PoP3 server for mail (no exchange). Some users check mail from
multiple locations or have it synced to their Treo's. To do this they
have "Leave a copy of messages on the server" checked "Until deleted
from deleted items" folder. This works fine except for when they
receive a message with a return read receipt request. When this
happens the user will continue to get a copy of the message everytime
they check their ...Moving to end of long columns
In other spreadsheets than Excel it is possible to paste
a long range of data between columns by using the
left or right column neighbour to get to the end of the
target data range.
I'll try to explain what I intend to do and I kindly ask
more experienced users to tell me a working solution:
- Imagine a column of about 60,000 rows in length
(A1 ... A60000)
- I intend to copy a cell to the empty column right of
the 60,000 rows column
- Let's presume the new cell data is already in the
- I move to cell B1
- From B1 I move to A1 with Shift-LeftArrow
- From there I intend...Dynamic form, columns of subform based on a table that changes with each application
I want to make a form/subform that can be used across clinical
trials. The form container for the subform won't change. However,
the columns of the sub-form will change based on records in a table.
I have a table of subjects with unique identifiers. I have a table of
vital signs. The vitals collected for each study will be different.
The vitals are collected at multiple time points. I want the subform
to show the unique identifiers for the subjects as the rows and the
columns to be the vital signs.
Once collected on the form, I'd like to transpose the vitals into one
vi...adding ROWS #4
is there a way to auto add rows in a work sheet when there are no more empty
ROWS to be filed and there is more work space rows needed?
i have a row with different colums with text to be filed in. what i want to
do is when i am typing in the texts and reach the end of the last row it auto
add a new ROW of the same settings as the last and if possible have my
documentation continued there.
You can use Data>Form to add new records to an existing table. It will
automatically add the formulas to the new rows.
> is there a way to auto add rows in a work sheet when there ...custom task columns
Is there a way to transfer/export columns, including user defined, on to
another computer in Outlook Tasks?
No, custom views will not export. Try copying the .pst file and then open
it on the new computer and drag and drop your tasks to the new tasks folder.
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the discussion intact. All
unsolicited mail sent to my personal account will be deleted without
After furious head scratching, JB asked:
| Is there a way to transfer/export columns, including user defined, on
| to another computer in Outlook T...PO and return
Raises a PO for 20 of the same Item
Receives 20 for that item against that PO.
Returns 11 of those items against the RCT.
when he goes back to match invoice screen he wants the auto invoice to show
only 9 items that he has received. However GP shows 20 items with regards to
that invoice, any ideas ?? is there something might be missing
No answers to that one either ?
"Zille Hassan ( Support Consultant)." wrote:
> Raises a PO for 20 of the same Item
> Receives 20 for that item against that PO.
> Returns 11 of those items against the RCT.
> when he goes back to matc...Subreport Group header printing/repeating twice
I have a main report with several subreports. Each subreport has its header
information in a Group header, grouped by a constant expression, "=1", and
the Repeat Section property is set to "yes." There is not a page header or
report header on the subreports. Why does the Group header repeat itself on
the first page of the report? It does not matter whether the report is
previewed seperately or apart of the main report. I need the header to appear
on each subsequent page, but not twice on the first page.
...Freezing two columns in addition to Freeze Panes
Is there a way to Freeze two columns so that they do not scroll with the
rest of the sheet?
I need columns A and B to remain stationary when scrolling. Additionally, I
need the normal Freeze Panes option to continue to be enabled at cell F3.
Can this be done?
Use a second window.
MS Excel MVP
"PCLIVE" <firstname.lastname@example.org> wrote in message news:ux7UcQjgFHA.email@example.com...
> Is there a way to Freeze two columns so that they do not scroll with the rest of the sheet?
> I need columns A and B to remain stationary when...Performing an Action on a Range of Rows
Forgive the newbie question. I am looking to perform an action on a
range of rows (ex. - COPY the contents of cell A1 to Cells K1 through
K21,777 or DELETE Rows 2 through 10,801).
Is there an easier way to do this than dragging the cursor down the
column to the ending location?
Thanks in advance...JohnB
Click in NameBox to left of formula bar.
Type K1:K21777 and ENTER.
To delete rows enter the range A2:A10801 in the NameBox then Edit>Delete>Entire
Gord Dibben MS Excel MVP
On Mon, 23 Jul 2007 15:55:28 -0700, JohnB <firstname.lastname@example.org> wrote:
...adding every other column
i need a formula in cell A1 to total cell C1 and everyother column after that
i.e. E1,G1 ect
i need a formula in cell B1 to total cell D1 and everyother column after
that i.e. F1,H1 ect
how do i do that?
=SUMPRODUCT(MOD(COLUMN(C1:S1),2),C1:S1) in A1
=SUMPRODUCT(MOD(COLUMN(D1:T1)-1,2),D1:T1) in B1
Adjust the ranges to suit.
"gma" <email@example.com> wrote in message
>i need a formula in cell A1 to total cell C1 and everyother column after
> i.e. E1,G1 ect
> i n...Stacked column graphs and text displayed inside the columns
I want to create a stacked column graph for a series of numbers, then have
corresponding text appear inside the columns. Can this be done in Excel?
If not, why do my text boxes disappear (get sent backward) when selecting my
You could try to use data labels. Right click a series, choose Format, and
for data labels, choose one of the default options. You can edit the text of
one of these default labels, or use a utility like one of these to add
custom labels from elsewhere in the worksheet:
Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tool...Listing months and years in two columns based on start and end date.
I freely admit to being an Excel idjut and I hope you all can help m
out. What I would like to do is enter a start date in one cell and en
date in another cell. Then I would like for the spreadsheet to lis
the months in between the start date and end date in one column and th
associated year to that month in the next column.
For example, if my start date is May 2000 and my end date is July 2001
the columns should be May 2000, June 2000, July 2000 until the end dat
Is there a way to do this?
Message posted from http://www.ExcelForum.com
I fo...Mixed up e-mail bodies and headers
I created a new folder in Outlook 2003 on Win XP and moved some old e-
mails from inbox to this folder. Then I decided to create subfolders
in this folder for every month and moved e-mails into them. After
that, all e-mails have mixed up headers and bodies, i.e a body of e-
mail from 2 weeks ago has a header of e-mail from yesterday. Seems
like headers and bodies have been combined completely randomly in this
subfolder. All other folders and inbox seem to be ok.
Is there anything I can do to synchronize the old e-mails?
Koval <firstname.lastname@example.org> wrote:
> I created a new folde...Returning null values
I have a report where I think that certain fields are not shrinking because
while I think that the associated query fields look blank, they are no in
fact null. How can I ensure that a query field (that is the result of an
expression) is in fact null, and not just blank?
Joseph Greenberg wrote:
>I have a report where I think that certain fields are not shrinking because
>while I think that the associated query fields look blank, they are no in
>fact null. How can I ensure that a query field (that is the result of an
>expression) is in fact null, and not just b...Can't edit header
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC
Once I create a header, I can't go back and edit it after it is saved, like change the font size.
if you go back into view>header & footer, you can edit the header. My configuration is the same except I have a intel processor.
As Mungo suggests, it shouldn't require anything more than going to View>
Header & Footer or switching to Page Layout View & double-clicking the H/F
you want to change. If something is preventing you from doing so you'll have
to take the time to accur...Macro to Un Hide filtered rows
I have this Macro
Const csPWORD As String = "123"
I get a bug error if the sheet is already un filtered and the next step of
protecting the sheet again doesn't happen. How do I change this to search
first to see if the worksheet is filtered and if it is then proceed with the
rest of the Macro.
One option would be to ignore the error;