format based on data from another cell
I want the names in column B of my spread sheet to highlight either red or
yellow based on the value of the cell in column L, but with conditional
formatting you can only format based on the value of the cell you are
formatting, as far as i can tell.
conditional formatting can be used to do this,
select the column B cells that you want the formatting to be applied to,
ensure that the first selected cell is the first line visible at the top of
choose format / conditional formatting
where row 2 is the first row in the selected column an...Data Format: how can I re-format
The spreadsheet that was exported for me to use has the information in
It is customer ID name and billing info but it is like a long series of
I want to be able to have them listed under captions like company name,
contact, address, city, etc.. so I can use the data.
There is an empty row between each entry. Each entry is 3 or 4 rows.
There are about 500 records so i dont want to manually set them up.
Try tinkering around with these steps
Assuming your list is in col A,
data in A2 down
(If data starts in A1 down, insert a new row for the co...Count invalid data entries
I am using the following macro to identify invalid data entries in
cells with data validation applied.
' Application.CommandBars("Formula Auditing").Visible = True
MsgBox ("Check for Red Circled Invalid Data Entries on" & Chr(10) &
" Configuration and Parts_TakeOff Sheets")
Is there a way to count the number of invalid data entries (red
circles) and write the number to a particular ce...Display comments as data #2
thanks a lot , this worked exactly as required, the comment go
converted to data
Now just one more thing . is the vice versa possible i.e if i have dat
in column A can it be shown as comment in column B.
the reason i need this is bcos i am shifting some data with thei
comments to MS access(Hence the need to convert omments to data)
processing them and again converting them to excel (the need to conver
data back to comments
sonika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=...auto generate emails from data stored in excel spreadsheet
Is there a way in excel to automatically generate an
email which is stored in a worksheet either using excel
macros or in conjuction with outlook.
thanks in advance.
Maybe this one
Regards Ron de Bruin
"MarkK" <firstname.lastname@example.org> wrote in message news:email@example.com...
> Is there a way in excel to automatically generate an
> email which is stored in a worksheet either us...Merging data from two worksheets
I have two worksheets with lists on them. On worksheet A there are items we
use, and charge code (which correspond to individual items); but there is no
RMS code (a specific code we use for our system). On worksheet B there is a
larger list of items, many of which are also on worksheet A. Worksheet B has
a column with the same charge codes as are used in worksheet A, but worksheet
B has the corresponding RMS codes.
Item RMS Charge Code
Stick (blank) 12345
Ball (blank) 54321
Bases (blank) 21543
Item RMS Charge...Exchange data backup
I'm worried emails backup of the small office.
Does SBS2008 Exchange server has a direct backup capability of whole mail
How to backup every night Emails from server to external harddisk?
Does Exchange server needs to be stopped before backup?
SBS2008 Takes care of it.
look at the comments section
Russell Grover - SBITS.Biz [SBS-MVP]
MCP, MCPS, MCNPS, SBSC
Microsoft Certified Small Business Specialist
24hr SBS Remote Support - www.SBITS.Biz
Question or Seco...Data Save error
GP Ver 6.0. Recently I am facing an error with normal users unable to save
records, where as the system administrator is able to save the same
information. Please advice on this error, what has to be done.
I have disabled the windows user ID of the Database owner. Thinking of this
made Dynasa as the DBO.
Database is having enough free space available.
How big is the company database? Is it over 10GB?
"Matthews" <Matthews@discussions.microsoft.com> wrote in message
> GP Ver 6.0. Recently I...Extracting XML data to be used in XSLT HTML output
I have done small level Xml stuff and am just now starting to understand
some of the plumbing involved. I've done a lot of .NET programming for SQL
Server, but little for Xml data stores.
I am currently just interested in using the .xsl file that I have being
applied to a source .xml file through ASP classic. Here's what I have:
<?xml version="1.0" ?>
<xsl:output meth...Pivot Table not pulling all fields or data
Without getting too verbose, I have a 900 row by 26 column database I am
applying a Pivot Table to. The code seems to run fine, but it is not
returning some of the fields, and of course the related data. I have tried
reformatting the entire columns I am pulling from but no joy......however,
when I reformat an errant cell from General to TEXT, that row comes
in........and/or, if I change the value in one of the missing cells, it will
bring that row in. The code runs fine, and was mostly derived by recording
a macro in XL2k, but the data cannot be trusted becau...How do I automatiicaly redefinie Source data range for a chart?
I have A Chart Defined by Category (X) values in Collumn B and Y values in
Collumn C. Right now Both are defined by rows 4 to 114.
I would like to be able to have the chart automatically redefine itself so
if I put a value in C115 the chart would automaticaly set Y value range to be
C4:C115 eithout having to manually define chart. Is this possible? If so
how? I've tried OFFSET bu so far no luck.
If you turn your data cells into a List, or Table in xl2007, the chart will
The named range approach will work if you set up the names correctly.
See here for more info...overflow data
When i input data into a cell, can i fix it so it doesn't
overflow the adjacent but empty cell? Thanks.
You could enter a space character in the adjacent cell, so it appears
empty, but will stop the overflow.
> When i input data into a cell, can i fix it so it doesn't
> overflow the adjacent but empty cell? Thanks.
Excel FAQ, Tips & Book List
thanks, Genius! :-)
>You could enter a space character in the adjacent cell,
so it appears
>empty, but will ...external data not in a table
I have a worksheet originally created in excel2003 that gets external
data from a db2 database. After migrating to excel2007 the external
data in the worksheet is not in a table (clicking on a result cell
does not make Table Tools appear). When creating a new database
query I find that the results always get put in table. I don't want
the results in a table. How do I make the results of a new query NOT
be a table? And I don't want to break the link to the database since
I update the results periodically.
...How to Use Data Migration Pack
i downloaded the DMF from Microsoft.com,
i read the documents but i couldn't understand that,
My exact Requirement is, i have to import 18000 Records to Product
what i have to do now, please give me guidelines to import Records,
On Sep 8, 7:04 am, mukathangadu...@gmail.com wrote:
> Hi All,
> i downloaded the DMF from Microsoft.com,
> i read the documents but i couldn't understand that,
> My exact Requirement is, i have to import 18000 Records to Product
> what i have to do now, please give me guidelines to import Reco...Data vs. forms & Unload/reload questions
A .mdb file seems to encompass both the database and all the 'code' (forms,
modules, and logic) used to CRUD the data in the tables.
1) Is there any way to separate the database from the code into separate
files, eg. the db is mydata.mdb and the 'code is in a separate file, eg.
2) Say you have v1.0 of an app and the tables and attributes are populated
with data. You then go and build v2.0 of the app, adding tables and columns
to what already existed in v1.0, and perhaps moving some of the v1.0
attributes into different tables. What is the recommended way to
unload/reloa...cell range not changing when refreshing linked data- sumproduct fo
The cell range is not updating correctly to reflect the last row in the data
sheet that is being refreshed. 5878 is the correct number of rows and 5824
is not. I can find and replace in my formulas to correct the problem, but it
does it each time the data is refreshed. Any suggestions as to why?
I gave you the INDIRECT option y'day, but received no feedback from you in
that thread. Looks like you're more interested in knowing why? One simple
hunc...Ignore a Blank Data Series in the Legend
I'm creating Pie Charts based on data from 10 data series fields. Some of
the data fields are blank, and so I've managed to create data labels that
only show on the Pie Chart if there is a value (otherwise the Chart was too
hard to read due to lots of blank labels).
However, if I show the Legend, the blank data series field still appear, so
I have say, 4 labels with the correct data series names but 6 which are
blank. Can I make the Legend ignore blank fields, and just show the 4 that
You can manually remove individual legend entries. Click on the lege...Extract unique data
I am using excel 2003
I have a file like below
Rate month GBP
1.46 FEB 2000
1.47 MAR 3000
1.47 APR 3500
1.48 MAY 1000
I want to create summary
Rate FEB MAR APR MAY
1.47 3000 3500
I can use sumif function to return the total, however, how can I insert only
the unique rate in the first column automatically.
Thanks a lot !
I suppose information is in sheet 1 columns A,B...Data validation doesn't work if contents is added by pasting
I've data validation set so that only the number 1 can be entered, but
even this cannot be entered if an adjacent cell is a certain value.
this works for direct entry, but not if the value is pasted in or added
using the "drag handle".
Is there any way to stop this happening as I just know somewones going
to try and fill all the cells by draging the values down.
You could disable "Allow cell drag and drop" in Tools | Options or...
copy the following code, right click the sheet tab and choose
'view code'. Paste the code into the modu...Dropdown lists thru Validation not friendly to Manual Text Entries
Using Excel 2000.
I created a (long) dropdown list using the Validation feature. When
users go to enter data, some prefer to never touch their mouse. And
scrolling through this long list is cumbersome for everyone.
Is there a way to either autocomplete an entry based on the items in
Or to have the dropdown box "intelligently" scroll when a user types a
few characters (For instance, drop to the "S's" when he or she keys in
(sorry if this was addressed in other posts, I really have tried
Message posted from http:...Moving a line chart data point revises data table value in Excel '
I recall a capability I used with Excel years ago that enabled me to create a
line chart from a data table, then move the plot points on the chart to
"smooth" the line, and the source data chart value would change with the
corresponding new plotted point.
I'd like to use that capability with Excel 2007 but can't find to activate it.
Any power users know how?
Ed, Microsoft decided that users didn't need that feature. More than
likely, they were tired of support calls asking why the values changed when
one dragged a data point...... No really, they took i...Day Validation code
I am looking for some help for my data entry form. In the form there
is a combo box, which is used to enter date ("cboLongDate"). The date
format is "Wednesday, June 13, 2007" . I need to confirm the day is
Wednesday or not before the user click on add data or close form. The
important thing is day (ie "Wednesday") not the date as Wednesday
comes every week. If the selected day is not "wednesday" a message box
should pop up saying the day selected is incorrect while closing or
adding data so that user can get a chance to correct it.
Thanks in adv...Protect Data, but allow sorting
In Excel 2003 is it possible to protect the data in a sheet, but to allow
the data to be sorted?
In the Protect Sheet command, it indicates that this facility exists, but
when I try to do a sort it won't do it until the sheet is unprotected.
Make sure that there's an empty column between the range to be sorted and any
If you have a locked cell butting up to the range to be sorted, excel yells!
Pete Walburn wrote:
> In Excel 2003 is it possible to protect the data in a sheet, but to allow
> the data to be so...comparing data #2
I was wondering is there a way to compare data from 2 columns in a side
by side comparison. I have a large list of numbers and what I want to
do is copy another list of numbers into the spreadsheet. But I want the
numbers in cell A1 and B1 to be equal if they are is not a match then
leave cell b1 empty. I want that to continue all the way through the
Do you want to end up with 2 separate columns, or are you wanting to
merge the 2 lists into 1 list, without duplications?
I want 2 side by side columns. With the numbers that are equal right
next to each othe...External Data from Secured Access Application...
I have a secured access application with a query that I
want to use as the source for an excel chart. When I
attempt to use anything out of this Access application
(even a raw table), the MS Query window won't expand to
show the field(colums).
Can anyone help me get this working???