I have a 5 column 2000 row spreadsheet and wish to manualy eliminate some
entries because they are superfluous or duplicates.
I create a new SS from just 2 columns to make it easier to work with..
I do my editing on the new SS.
Can I now "apply" those changes to the original SS?
Or am I breaking a basic rule and will now have "misaligned" records?
I hope my query is clear in spite of my poor description.
"KiwiBrian" <email@example.com> wrote in message
> Hypothetical sce...Problem with cell formatting and underlining
I'm using Excel 2007, SP1. I'm having a problem with cell formatting.
Here's the situation:
Normally, if data are entered into a cell, they will appear past the edge of
the cell as long as there's nothing in that cell. And that's what I want.
However, if I choose to mark the contents of the cell as Underlined,
everything goes wrong. The cell will clip all text that falls outside the
border of the cell.
Does anyone have any idea how to either fix this or get around it? I'd
hoped that SP1 would have solved the problem, but no.
Work...Default column formats
Hello, How can I change the default column formats for all opened and new
books. EG: Format of column's 1, and 3 are general, and column's 2,5,6 are
can't to that.
but sounds like you are using the format a lot.
set a blank wb to this format. when you need a new wb with
this format, call this one. when you save it, do file save
as and give it a different name. this way you will always
have a wb set to this format.
>Hello, How can I change the default column formats for
all opened and new
>books. EG: Fo...Any font to surround number with a circle? #3
I haven't used the macro recorder b/c the only way i can think of to d
it would be to insert a drawing sahpe around the number and then se
the fill to be transparent. VBA macro wouldn't work very well fo
that, I would rather use a mouse click/keystroke macro recorder like E
Macro instead. What is truly needed is a whole new Font set tha
surrounds the value with a circle but I have absolutely no idea how t
do that or if a regular Joe who isn't an MS programmer can create
custom font set
Flamikey&...Numbering the Records of a Query
I'm using the code module below to create a extra column which will number,
in serial order, the records produced by my query. The problem is that when
it creates the number list and the list reaches a record in the reference
field which is repeated, it repeats the number used when the record was
first encountered, like shown below. How can I edit the code or correct the
issue so that number continue in serial order '1 thru 6'.
Function Serialize(qryname As String, keyname As String, keyvalue) As Long
On Error ...Help with query
if field1 is greater than zero and less than 1 than 1 otherwise 0
Whats the right way to write that expression?
NewCalculatedField: IIF([Field1] > 0 and [Field1] < 1, 1, 0)
"Qaspec" <Qaspec@discussions.microsoft.com> wrote in message
> if field1 is greater than zero and less than 1 than 1 otherwise 0
> Whats the right way to write that expression?
...Auto Formatting Cells and Dates
I noticed that if you type in something like 3/16 excel will assume you meant
March 16th and change the cell to a date. I know you can change the format
to text before you type the date in. Can you turn it off completely? Are
there any options you can change concerning this feature (like the date
format it changes to). Can you create your own autoformats concerning
numbers other than dates?
preformat the cell as Text
start your data entry with an apostrophe: '3/16
> I noticed that if you type in something like 3/16 excel will assume you meant
> Ma...manually selected Excel cell formatting
I've installed Excel 2007 recently. I notice when I manually select
non-contiguous cells using the ctrl key that the selected cells are not
highlighted like they were in Excel 97. At first I thought I was losing the
selection altogether but I noticed that the current cell had a thin outline
instead of the heavier normal outline. It was then I saw that the selected
cells were actually very faintly shaded. I checked that the cells were really
selected by migrating through them with the Enter or Tab key. Is there any
way to make the selected calls more visible? Or is something w...Format in if formula
I want to use the "if" function and change the format
(color) of the cell if the outcome is true or false.
...V-lookup and format
I am making a dashboard for work and am using lots of v-lookups t
access a spreadsheet with lots of departmental information. I have th
v-lookups for the data all figured out, my question is this: Is there a
easy way to bring the format with the data. for instance some of th
numbers are just numbers, others are percentages, thus they come to th
new cell as .23 instead of 23%. Below is an example of the v-lookup i a
=VLOOKUP($B22,'[People Hub.xls]people hub'!$E$9:$R$5000,'[Peopl
I don't want to preset the cells format in the da...Select Queries in Macro
I have created a Macro with 20+ "Select Queries". When the Macro is
run with the "OpenQuery" action, because these are "Select Queries" it
is opening all of them in several windows. I want to either :
1. Run these queries without opening a window
2. Close all query windows after the Macro is complete.
I know that its odd to have so many "Select Queries" - I didn't create
the database, so rather than start from scratch I'd like to clean it
up a bit.
Any help would be appreciated!
What is the purpose of openning a select query and...Setting Conditional Formatting in 2007 saved as 97-2003
I am working in XL 2007, but my workbooks save as the lower version.
I have two columns of cells that I would like to apply a Conditional
Format to. I figured out the Rules Manager. But I'm not sure about
applying my conditions.
I have three possible values in column A; depending on the value, I
want to set the font color in cols A and B. Would it be better to set
three separate conditions across the cells? Or use some type of IF
formula in one condition?
Use three separate conditions
Bernard V Liengme
Microsoft Excel MVP
remove cap...Date Format #2
Frequently i am downloading some datas from our ERP,in which the date'
are downloaded as in this style.
i am not sure this in what format.But when i use the formulae
=days360(a1,b1,true) to find the difference between 2 dates,excel i
giving error msg as #VALUE!
pls help me to sort out this issue.
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
check that the cell is not in text format,
try 28/11/03 to test if there is any change....Excel 2003 and Querys
I need several questions answered and the answer might be the same for both
I have a routine that goes out and pulls in data (historical data for a
number of stocks) from the Yahoo Financial page. I wrote the routine in
Excell 2003 on windows 2000. When it is run on an Excell 2007 on Vista. The
routine works fairly well on Excel 2003 and horribly on Vista.
The routine places querys on sheet "QUERYS" and works from the resultant
data. When the activesheet is "QUERYS" the machine seems to be spending a
lot of time in a mode that precludes any other activity on th...Re: Excel 2007 column chart fill question
I would like to fill my column chart with cross hatch, vertical hatch,
horizontal hatch, etc. instead of colours. The only method I've found
currently is to fill with different picture types that have the
different type of hatches. I'm certain there must be another method.
Does anyone know how? Thank you.
Pattern fills have been deprecated, MS term for removed, in 2007.
Picture fill is the only way.
Anvah Gareson wrote:
> I would like to fill my column chart with cross hatch, vertical hatch,
> horizontal hatch, etc. instead of colours. ...How to format a date to a different format
I need help in converting a date "20050205" where the first 4 numbers are the
year, the 2nd two numbers are the month, and the last two would represent the
day of the month. I have tried several things to format these and looked in
Help but I am not asking the right question. I want to be able to have the
data read 02/05/2005 or something close such as 2/5/05. The date format it
is currently would be fine if that was the only report, but I have several
other downloads that I work with and those dates are in the format I am
requesting. Also the original format is hard to re...Running append query on recordset
I would like an append query to run when a button is clicked, but only on the
items that are diplayed in the form. So if someone has a filter on a group
of records, and then they click the button, all of the records that are
selected will be sent to the append qry.
Can anyone tell me the correct way to select many records in a multi-page
form. Each page is a product and I am trying to add information for each
rs = Me.recordset
INSERT INTO tblAcqDetail ( Quantity, PriceEach, ProductID )
SELECT rs.[txtQty] AS txtQty, rs.[txtCost] AS txtCost, rs.[txtProductID] AS
tx...Reprint AP check format on plain paper
Provide the ability to print a "copy" of a previously printed check w/
stub (with "void" in the signature section)
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 the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defaul...Macros to Update date and invoice number
I created a macro to save a workbook as a new book
called "NEW". This is easily done by recording a macro,
however, I want the following functions after i save the
new book which I do not know the code:
1) Cell A1 in Sheet1 is to be equals to Cell A1 PLUS 1
in the sheet before "SheetSUP". I will be adding new
sheets before the "SheetSUP" constantly thus I cannot use
the name of the sheet before "SheetSUP" in the code...ie,
the sheets are all invoices thus A1 have running numbers.
For every new month, I open an old book and saveas a new
I am having trouble with MS Query. I have set up a DSN
for MS Query to use MS Access. If I move the location of
the Access database and change the DSN, Excel does not
recognize the change. How can I resolve this?
I have a subform based on a query. The query includes a text field for
"details" and a memo field for "Notes" in which contains additional
information which is beyond the capacity of the details field. I want
to apply conditional formatting (a change of colour) to the details
field on the subform to indicate when there is additional information
in the notes field.
I have based the conditional formatting on an expression which tests
whether the Notes field contains a null value. The notes field in
placed on the subform, as I understand that it needs to be there in
order to ...eliminate duplicate in a column
How to eliminate duplicate in a column?
Quick fix would be to apply data>advanced filter, unique entries only and
copy to another location
(No private emails please)
"jaya" <firstname.lastname@example.org> wrote in message
> How to eliminate duplicate in a column?
> Quick fix would be to apply data>advanced filter, unique entries only and
> copy to another location
Try that with the
The result will be same as the initial column. However, it does not...Corstabquery or normal query
Maybe I have a langguage problem in expressing my difficulties, but here is
what I wanted
Normal report based on the normal query:
No. Position Name
1, Elder Mr. A
2. clerck Mr. B
3. Deacon Mr. C
1, Elder Mr. D
2. clerck Mr. E
3. Deacon Mr. F
Year 2007...same way downward
I wanted it that the year is Sideway"
No. Position Name Year 2005 Year 2006 Year 2007
1, Elder Mr. A Mr. D .......
2. clerck ...gantt
I have excel 2007 (sorry for the previous post)
I have a table like this
Start Date Duration Staff Staff 2
JOB 1 5/1/2009 29 Bob Dan
JOB 2 5/1/2009 60 Jeff Denise
JOB 3 5/15/2009 61 Ben Dan
JOB 4 5/15/2009 61 Jeff Paul
I would like to be able to have different colors for each person.
Only want each job listed once on the left.
Are you trying to create a gantt chart or do you just want to color each row
a different color.
Check out the topic Gantt Charts at Jon's site:
If you only want to color ...Pick up Formatting/Apply formatting
My Publisher 2000 had a wonderful feature that I used all the time. I could right click on a text frame and "Pick up formatting" then I could click on another text frame and "Apply formatting
I purchased Publisher 2002/XP, and I can't find this wonderful feature anywhere. I sure can't easily right click to get it, but could it hidden somewhere and I just can't find it?
A small child turns to Ed, and exclaims: "Look! Look! A post from Judy
> My Publisher 2000 had a wonderful feature that I used all the tim...