Criteria to prompt for field and omit certain records
I have a query that prompts the user for the install number. I also need it
to omit any records that have "obsolete" in any of the revision fields.
Following is my SQL code.
If I include the Not Like "*obs*" criteria, the install number is not
included in the report.
If I exclude the Not Like "*obs*" criteria, the install number is included
in the report, however all records with a revision of Obsolete also show up.
SELECT [Engineering Drawing Numbers].MasterProject, [Engineering Drawing
Numbers].[INSTALL NUMBER], [Engineering Drawing Numbers].[INST...move rows from column to column
I have Column b with Name in one row and number in 2nd row want to move all
numbers to Column a and leave name in Column b without having to move each
one seperate. 1500 records.
Use a help column, in the first adjacent cell type
where A2 is the first cell with name
copy down 1500 rows by either dragging the formula or
double click the lower right corner of the help cell (as long as it is in an
Then apply data>filter>autofilter and filter on FALSE in the help columns,
select all visible cells and copy and paste somewhere else, then select TRUE
from ...Associating one column with another in Excel
I have a column of data that has 1970 cells with mixed letter an
number codes. The values in these cells come from another column with
list of 1056 possible values.
Is there a way to link my original column with a numerical value from
to 1056 such that for example any time value A appears, "1" will appea
in a cell next to it (B with 2, C with 3 etc)
Message posted from http://www.ExcelForum.com
not sure if i understand you. you could look at the ROW() function. o
if you wanted to set up a list, check out the VLOOKUP function. thi
Message posted fr...Sum a column
I don't remember how to sum a column.. What is the function ?
for exemple :
on Page1 i have G17 = 10, G50 =20, G200=50
i want do to something like SUM(Page1!$G:$G) to sum all G column and display
it on Page 2.
I don't want to use SUM because it doesn't work....
i know that exist another function with check if column cell has something
written, and add the cell content to a counter.
thanks in advance,
What "doesn't work"?
works fine to sum column G on sheet Page1.
If instead you're looking for a count of cells with values i...Need to add cells from column B to create new total in column D
This must be elementary, but I am new to VBA. I have a SQL query that returns data to my sheet that is grouped by status, and ordered by date. Here is an example of what is returned:
9/27/2003 17:00 1 Open-Propose
9/27/2003 17:00 1 Open-Review
9/27/2003 17:00 31 Open
10/4/2003 17:00 2 Open-Review
10/4/2003 17:00 38 Open
10/4/2003 17:00 4 Open-Propose
I need to create a chart so that for each given date, a total of all "Open*" is presented, because for charting purposes it doesn't matter what the status is, the overall count is what is important.
I want this returned:
9/27/200...how to combine several columns into a single column
I have data in 50 columns and 30 rows.
I would like to know how to either copy or move the data in the next columns
to the next row of the previous column without using "copy and paste" because
there are too many columns and I think there is a better way to do this.
What I mean is that I want to have only one column combining all data from
50 different columns into one column.
So it looks like this after moving or copying the data from columns into one
B1 ( data in the column B is now in the next row in the column A at the 31st
....Concatenate for Numbers?
In column A of my spreadsheet is Start Date (01/01/04),
Column B is End date (01/10/04). Each rows contains
different start and end dates. I am attempting to tie
these two together without retyping 300 rows of data. I
need a single column for date range (01/01/04 - 01/10/04).
I attempted to use Concatenate, however, it only works
with text. Is there a formula that I can use to join
these two together.
Your assistance is apprecitated.
=TEXT(A1,"mm/dd/yy")&" - "&TEXT(B1,"mm/dd/yy")
Hope this helps!
In article <1b...When sorting, how would I omit duplicates?
I am working with several data spreadsheets. I have to weekly update new
information into one spreadsheet.
Question: When sorting new data with the old data, how would I omit the
You can't AFAIK.
You could filter the data for unique records (Data>Filet>Advanced Filter)
and then sort that data.
(remove nothere from the email address if mailing direct)
"Blaine Fray" <Blaine Fray@discussions.microsoft.com> wrote in message
> I am working with several data spreadsheets. I have t...Column Width
I would like to hide/unhide columns using the column width (equal to "0"). I
would like to do this as a result of the contents of a cell. For instance,
if user answers "Yes" to a question, then hide column D. Can this be done?
You give no detail but this can be done with a worksheet_change event in the
Microsoft MVP Excel
"Nick" <Nick@discussions.microsoft.com> wrote in message
>I would like to hide/unhide co...Counting all "F"s in column A where there is also an "A" in column
I am trying to find the count of all the cells that have an F in one column
and also have an A in another column and I'm getting an error. I hope Excel
can do this with a simple formula. I don't do macros.
Here's an example:
1 A F
2 B F
3 A P
4 A N/A
5 B P
6 A F
Basically I am trying to determine how many "A" level requirement have
Passed, how many have Failed, how many are N/A and the same for the B level
requirements. I've already tallied the total Fails and Passes with ...zero value removes columns. How do I keep columns with zero value
..I have a workbook that run in Business Warehouse. I enter a date range, and
several columns fill in. However, if one of the columns has a vlue of zero,
the column will not display. Ex a workbook has col_a, col_b, col_c, col_d. If
col_c has no data, the worksheet will only show, a, b, and d.
I need the zero value in column D because I have chart data linked to the
worksheet. If col_c does not display, my chart is wrong: the data is returned
as column a,b,d, and my chart is looking for a,b,c,d. So d's data goes into
I tried telling excel to show zero values. I fooled around ...print selected columns in query OMIT LAST QUESTION
I sometimes need to print just some of the columns in a query. These columns
are not always adjacent to each other.
Is there a way to do this?
...Omitting the gaps
For the next few days I will be compiling a very large database (four
sheets) on Microsoft Office Excel 2003 (Windows 2000 Office). Three of
the sheets are pretty much complete but I am having some issues with the
(1) I have headings on both the rows and columns; some rows (Companies)
do not have any values as there was no action in these accounts last
year. All columns (Deals) have some sort of dollar figure as you
scroll down. What I want to do is: to be able to see any criteria
either row or column and see where there are active cells relating to
that field and have the correspon...Sort number as text
I have an excel 2000 file in column I, I has cost center
numbers, the range of numbers are as follows. 000, 050,
555, 850. I have made the cell text cells so they will
keep the preceding 0's. However when I sort them, they
come out in the following order. 555, 850, 000, 050.
To display numbers with preceeding 0's you don't need to change cell format
to text. Format the cell as Custom "000" instead.
When you need to convert numbers to strings somehow, use formula
=TEXT(CellReference,"000") in some adjacent column. NB! You can't use
converted value...Sorting 7 columns
How do I sort more then 4 columns? I've set up a worksheet that sort
columns in a base 1000 number system. I want to use the sheet t
organize Ideas that I get for creative writing, so to compile the Idea
in chronological order. I need to sort more then 4 columns to make it
usable worksheet. How Do I sort more then 4 columns, or get around th
+-----------------------------...problems with outlook and the from row
I cannot explain the situtation that clearly, but I have recently created my
outlook account to work with my pop 3 email account. When I initially tried
to create the account and did a send and receive test in the setup account
window, the receive worked perfectly but the send would not work as it kept
saying my email account was not accurate. I reset all my settings and tried
the test several times but no luck. A few days ago, outlook started to work
perfectly and I was finally able to send messages but now something very
strange is happening: When I create a new message, ou...Richedit:Display the content in two or more columns.(not buy)
In the CRicheditCtrl, how can I display the rtf file in two or more
columns, such as MSWord? Which version? If can't, what should I do to meet
I doubt the RTF-control can do this directly - and indeedm a quick test
saving a Word file as RTF, loading it into WordPad seems to confirm this.
You might have some luck by using two RTF-controls side-by-side, trying to
synchronize them, but given the hassles of - for example - variable height
lines, this will problably be much work and a brittle result. In your
situation, I might have...Help Freezing Multiple Rows
In earlier versions, you clicked below the rows you wanted frozen in place or
to the right of the rows you wanted frozen. Now I see only how to freeze row
I have a main title in row 1, row 2 is blank and row 3 has column headings,
so I want row 3 to stay frozen in place. I am having trouble accomplishing
this and need help, please. Thank you.
click on cell A4 then on the menu bar Window/Freeze Panes
> In earlier versions, you clicked below the rows you wanted frozen in place or
> to the right of the rows you wanted frozen....Separating text into new columns
Hi, I am having a problem splitting my delimited text file into ne
columns. What I have to do is take several survey results (all in
long string of text) and give each survey its own column so I ca
analyize it. So what I need to do is, at the end of the first survey
have some sort of a column break or something to that effect so th
next set of data places itself in a new column. Here is a smal
sample of what the data looks like:
9.)|Infrastructure|ACCEPTED|Type your comments here.|
10.)|Infrastructure|ACCEPTED|Type your comments here.|
Date: Wed, 03 Dec 2003 12:16:32 -0500 (EST)
In th...Column names
Is there any code to convert column(number) to column(letters), and V.V.
e.g. 27 for AA, AB for 28
"daniel chen" <firstname.lastname@example.org> wrote in message
> Is there any code to convert column(number) to column(letters), and V.V.
> e.g. 27 for AA, AB for 28
Great! Thanks. How about the other way?
i.e. 27 for AA
"Peo Sjoblom" <email@example.com> wrote in message
news:%23MpiXScTFHA.2560@TK2MSFTNG...I need to match all the Storage Numbers with the correct Item Number or Screen Number
Ineed a solution for this problem...
I have an Excel Workbook that contains 3 Worksheets. Worksheet 1
contains "Storage Numbers" with the suffix FS. Worksheet 2 contains
"Item Numbers" with the suffix CD. Worksheet 3 contains "Screen
Numbers" with the suffix SS. The Storage Number without the FS will
match an Item Number or Screen Number without the CD or SS
I need to match the Storage Numbers with the correct Item Number or
Screen Number. The Stroage Number on Worksheet 1 A1 do not correspond
to an Item Number on Worksheet 2 A1, the match could be in any row ...SELECTING A PARTICULAR ROW ID IN A RECORDSET
I like the domain aggregate function DFirst, DLast, DLookup etc. But how do
I select, say, the 17th record of a table or query when I have no idea what
the record contains? For example, if I know for certain that a query returns
5 records, how do I select the second or the third ot fourth?
Considering that, unless you assign some meaninful order (such as a date/time
stamp), Access doesn't store records in any meaningful order within the
table, you really can't be assurred that you can select the 17th record and
then select the 17th record a day from now and i...Find objects referencing deleted columns
Is there a script or tool that can return all views and stored procedures
that reference columns that have been deleted or renamed?
> Is there a script or tool that can return all views and stored procedures
> that reference columns that have been deleted or renamed?
The dependency information in SQL Server is not very reliable, and if
you have already dropped the target object/columns, it is even worse.
You can do:
SELECT 'EXEC sp_refreshview ' + quotename(s.name) + ...Deleting Rows in MS Excel
How can I delete every other row on an Excel worksheet without highlighting
and deleting each row in turn?
"MacK" <MacK@discussions.microsoft.com> wrote in message
> How can I delete every other row on an Excel worksheet without
> and deleting each row in turn?
You need a macro.
If this is a one off, Click on the first row header, then Hold Ctrl and
click on all of the other row headers and then delete them...entering value in column A
My users requested this functionality:
They enter values in, say, column A. Column B should be filled
automatically accordingly by executing some VBA-code (querying a
Problem is that column A also might be filled by VBA-code - meaning
that above functionality only should trigger if the user fills in
column A. (I wonder if if is possible to differentiate between these
I'm not quite sure if I should use a Worksheet_Change() or
Worksheet_SelectionChange() event. Or is there another more
appropriate way to handle this requirement?
We're using Office 2007.