Color a certain range of cell if a cell contain specified word
a b c d e
f g h
if a cell contain the word "Group" + a single char, i want the row which is
the same the cell containing "Group" to be autohighted
In tis case, it will be Cell A3-H3, and Cell A9-H9 to be auto-highlight!
Put cursor on col A>format>conditional format>formula
is>=left($a4,5)="group">format as desired>copy format (use format tool) to
col e then copy format the row down/up as desired.
SalesA...Need help with locking columns
I am trying to lock columns A, F, and H on Sheet1 so that when a user opens
the workbook, these 3 columns are locked for any editing, however, I want the
user to be able to edit any thing else on the sheet. I need help with the
Try this. Select all cells by clicking the box above the 1 of row 1. Right
click anywhere on the sheet
Format Cells - Protection Tab and un-check 'Locked' - OK
Select your 3 columns by holding down CTRL and clicking on the column header
of each column
Right click in one of the selected columns and repeat the formatti...Formatting Rows / Columns of a Report
Ok i am having trouble wrapping my brain around this one.
I have a table, tblTotals, with Fields: Station, WeekEnd, Revenue.
(text, date, number respectively).
The WeekEnd date is always a Friday. Here is some example data:
ATL 1/9/2009 2000
BWI 1/9/2009 5000
LAX 1/9/2009 8000
ATL 1/16/2009 3500
I need to create a report that is similar to a CrossTab Query in
format. Not sure if that is possible.
So the Row Headers would be the Stations, and the Column Headers would
be the WeekEnd Dates.
...Data in narrow columns truncated when saving as DBF
I have a requirement to save some data in an Excel file in DBF format which
Excel can do. However I noticed that if the columns in Excel are not wide
enough, the data in the columns is truncated when I re-open the DBF file in
Excel. It's also truncated when I open the DBF file with this other 3rd
party application. Please note that the amount of text in each column is
not that much -- maybe 15-25 characters.
I have to re-type the data, expand the columns to be wider than the actual
data (so the data is completely visible and then some), then re-save the
file in DBF format.
Does...how to write to database tables in Excel 2004
I need to write to a database using Excel 2004 (Mac, OS X, 10.4). What are
"jolly" <firstname.lastname@example.org> wrote in message
>I need to write to a database using Excel 2004 (Mac, OS X, 10.4). What are
> my options?
"Norman Jones" wrote:
Are you suggesting that I can use adodb in Excel 2004? Near as ...autoformat the column of a cell area
Is there any way to format one cell are so that that the width of a
cell area (something like format column width to contents) but without
formating the cells above or bellow the area I want to format
I have tried autoformat but it really does not give me what I want !
Somehow I cannot remove the autofilter that appears in the formated
Thank you, best
Personal.xls is located in the XLStart directory, and is used to store
macros and things that you want to be available to all workbooks, whenever
you start Excel.
By default, it is hidden in Excel, but if yo...Pivot Chart Format
Is it possible to prevent Excel changing the format of Pivot Chart lines on
I don't think we want extra features here - it would only slow down the action.
It is no problem to link to other formatted sheets.
"Alex Burman" <email@example.com> wrote in message news:<eLv8D9xYDHA.firstname.lastname@example.org>...
> Is it possible to prevent Excel changing the format of Pivot Chart lines on
...Restrict email between certain users?
I am looking for a way to restrict certain users to only be able to
send/receive from a set list of internal addresses only. I run an
exchange2003 server. I know how to restrict users from being able to
send/receive external email, but having trouble restricting internal
On 1 Sep 2006 07:22:34 -0700, "dxt178" <email@example.com>
>I am looking for a way to restrict certain users to only be able to
>send/receive from a set list of internal addresses only. I run an
>exchange2003 server. I know how to re...How to populate all cells of a column with the same comment?
Thanks in advance.
Copy the cell with comment
Select the target cells
Right-click > Paste special > Check "Comments" > OK
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
"cyril" <firstname.lastname@example.org> wrote in message
Re: How to populate all cells of a column with the same comment?
> Thanks in advance.
�� 38 ���������
�ļ��ߣ�Ken Wright (ken.wright@NOSPAMntlwo...Question on Subtotal Function
I am using the Subtotal function and it works good. Is it possible to high
light the numeric figure. What it does it high lights the word TOTAL but
not the numeric amount. Also is there a way to insert a line after the last
Subtotal operation automatically? Thanks in advance for you help.
- select A1:B100 (adapt to your data layout)
- goto 'Format - Conditonal format
- enter the following formula:
- choose a format
"Daniell" <Daniell@discussions.microsoft.com> schrieb im N...Creating Report on two tables that are linked
I am a novice access user who is looking at someone else's application.
They have a Post Table and a Comments table. There is an id field in the
Post table that links to a Post_Id field in the comments table. When I open
the Post table, there is a blank field on the far left that has a plus or
minus sign. If I click on the plus sign, it appears to open values from the
Comments based on these two fields. In the Post table there is a Body field
that has information that people have typed in. In the Comments table is
also a Body field that has information. This access databa...Get Msg "Cannot shift objects off sheet" when hidng columns" Why?
I have a large worksheet where I'm using most of the columns. There are 7
objects on the sheet, all buttons used for macros. They are in the top left
hand corner of the sheet. When I try to hide columns, narrow the width of
columns, or delete columns I now get the message "Cannot shift objects off
sheet" when hidng columns". I do not understand why this is happening.
maybe this'll help:
XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
Steve Ball wrote:
> I have a large wo...Access 2000 Link to SQL Server db tables
I have a Access 2000 database that has approximately 100 tables. I would
like to link the Access tables to SQL Server database tables to give better
access to the report and forms in Access. Please let me know the best way to
link Access tables to SQL Server database tables.
On Thu, 28 Feb 2008 12:36:03 -0800, Joe K. <Joe K.@discussions.microsoft.com>
>I have a Access 2000 database that has approximately 100 tables. I would
>like to link the Access tables to SQL Server database tables to give better
>access to the report and forms in Access. Plea...Pivot Table
I've made a massive pivot table.. however i want all rows which is 0 in
one column to be hidden. Is there a setting for this, or do I have to
make a macro to go and hide all rows which has 0 in a certain column ?
Ctech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27745
View this thread: http://www.excelforum.com/showthread.php?threadid=538405
First enter this small macro:
Dim r As Range
For Each r In Selection
If r.Value = 0 Then
...Web service on two tables
Please could anyone show me how to create a web service which accesses and
updates information in two tables. I have seen examples in MSDN based on one
table but these fail when using the DataAdapter Config Wizard tries to join
In my opinion will this always fail, it is not depending on the webservice.
I searched for some messages, this one (and the thread) looked for me one
who gives an idea,
I hope this helps
Why don't you create sql state...Usage (scope) of ALTER TABLE in sproc
I have a question about the usage of ALTER TABLE in a stored procedure
(SQL Server 2005):
First concurrent query, started first, should be later encapsulated in a
BEGIN TRANSACTION [Test] ;
ALTER TABLE [myTable]
NOCHECK CONSTRAINT ALL ;
-- Function from BOL, WAITFOR DELAY wrapper.
EXEC TimeDelay_hh_mm_ss '00:00:10';
ALTER TABLE [myTable]
CHECK CONSTRAINT ALL ;
COMMIT TRANSACTION [Test] ;
Second concurrent query:
INSERT INTO [myTable]
...Autofilling next empty cell in column?
Other than copying formulas manually to the next row of empty cells, can a
function within the preceding data entry cell do the work upon completion,
<enter>, or auto summing?
All cells within the range will have entries, so no isolated empty cells are
present to mess up the integrity. These are scientific, engineering functions
with no limit to range of entries.
Having searched through several manuals and this forum, I'm not too
optimistic for a solution, but keep trying...
You can create a macro that does this.
Otherwise, if you double click on th...importing *.csv data into two columns
I have a data file that is delimited as comma separated
values: escentially one long row of comma separated
values. I need to import this long string into two
columns: read two comma separated fields and start
entering data into a new row and repeat. There are no
empty cells as column 1 always has a matching value in
column 2 for that particular row.
I would appreciate any help. Thank you.
> I have a data file that is delimited as comma separated
> values: ... I need to import this long string into two
> columns: ...
This looks like a job for Excel's ...Different Cell Sizes in the Same Column?
Is it possible to have different cell sizes in the same column
If it is possible, how do you size the cells differently?
All cells in a column have the same width (they can have different
Cells in adjacent columns can be merged to form a wider cell unit. This
can cause problems with sorting/formatting/copy&pasting, so I rarely
In article <24D650A3-564F-440D-8926-480AF023D208@microsoft.com>,
Zaebos <email@example.com> wrote:
> Is it possible to have different cell sizes in the same column?
> If it is possible, how do you size t...Copying an Excel 2002 Table into Word 2002
Are there any problems when copying an Excel 2002 table
into Word 2002 document?
Do you suggest to copy the Excel 2002 table as an object
into Word 2002? When running both applications in XP
Please advise me.
Copying an Excel object means that you can edit it
directly from Word (The menus change to Excel). One
possible problem is that a user may decide to delete the
Excel file and so you loose the connection. Always keep a
You can Insert a sheet directly in to Word. Insert,
If you do not need to edit the file again use PasteSpecial ...Display ranges in a Pivot
I have a column called IncomeRange. I need to display the value in ranges
like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to
display as ranges? Is this an option or do I need to do some kind of formulas
Thanks for all help
Just wanted to explain a little more.
My requirement is to create a pivot report in which a column called
indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc.
The source of data for the pivot is a SQL Server query. I am not sure where
or how can I do it, so I am posting this in both g...Pivot Table Error
Thanks in advance.
I create PivotTables where one of the datafields is a Calced Field.
Sometimes the resulting calced field return #DIV/0! because of this the
resulting tables in the PivotTable display #DIV/0! also instead of the
totals. Any way around this problem?
Fix your source data to remove the #DIV/0.
=if(A1 = 0, "", B1/A1)
divide by zero yeilds an indeterminate mathematical result. As such XL will
not use it in any calculations as the result of those calcualtions will also
"RussellT" wro...Referencing Data in a Pivot Table
I have created a pivot table which shows me total money spent in various accounting categories. I want to reference the sums this creates to subtract them from a total budget to give me a remaining total. Unfortunately, as the pivot table is updated, cell references change. This means that the total I used as say total spent on fuel may become total spent on salary.
Can anyone help?
You can use the GETPIVOTDATA function to extract data. Look in Excel's
help for information and examples for your version of Excel.
> I have created a pivot table which shows me total mon...Merging adjacent repeated columns with a macro
I have a relatively large spreadsheet with numerous values repeated
(within the same column) and I would like to merge adjacent, identical
I have attempted to tweak an existing macro that deletes repetitive,
adjacent cells without any sucess. Does anyone have any suggestions?
My existing macro for deleting these cells:
totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If Cells(Row, 2).Value = Cells(Row - 1, 2).Value Then
Instead of...Table Locking Problems
I use the following code to create a relationship between table A and table
Set rel = db.CreateRelation("A_B")
rel.Table = "A"
rel.ForeignTable = "B"
Set fld = rel.CreateField("AID")
fld.ForeignName = "BID"
This code works as expected, but a few lines later I call DLookup() on table
'B' and I get the following error:
"Run-time error '3008': The table 'B' is already opened exclusively by
another user, or it is already open through the...