Is it possible to shade fields progressively darker or lighter depending on
the values. For example, I would like the following values to have the
-5 dark red
-4 lighter red
-3 slightly lighter red
-2 etc, etc
1 light green
5 dark green
One step further - would it be possible to tell excel to work out which is
the lowest and highest number in the data set and work out for itself how
light or dark to shade the values e.g, if my dataset happened to have the
values -50, -40, etc excel would work out which was a low value and which was
a high one.
Conditional formatting ...Pivot Table Source Data
I have an existing Pivot Table that usually updates daily, albeit this
relies someone in the office refreshing the data. Anyway we seem to
have missed one days data and thought we would be clever and just
include the days missing data back into the source data. In effect we
are putting the 8th September data between the 7th and the 9th. However
no matter what we do the data from the 8th ends up as the last column
in the pivot table. Are we missing something really simple here or has
my brain turned to mush?
thanks for the help in advance.
P.S I also tried creating a dynamic range but ...conditional formatting of row based on cell
I want to apply conditional formatting to a whole row
based on the contents of one cell in that row.
For example, if cell L2 contains the word 'sport', i want
the whole of row 2 to be shaded.
I'm guessing this is pretty simple, but seem to be having
a bit of a mental blockage.
Any suggestions would be appreciated. Thanks :)
formula is =$l$2="sport"
"Katherine" <firstname.lastname@example.org> wrote in message
> I want to apply conditional formatting to a whole row
> based on the contents of one...Sales Distribution Entry table
Can someone point me at the table that stores the data for the Sales
Distribution Entry window?
On Feb 27, 10:44=A0am, ALGP <gpjef...@gmail.com> wrote:
> Can someone point me at the table that stores the data for the Sales
> Distribution Entry window?
...Formatting four pages onto one 12x18 sheet
I'm trying to figure out how to format a 12 x 18 sheet with four individual
5.5 x 8.5 pages on it. Any ideas? The closest I can get is under page
setup...selecting "postcards" and that will give me three pages per sheet,
but I need four.
Any ideas or thoughts are greatly appreciated.
If your printer can be custom setup to 12 x 18 then you can divide it into four
parts. Are you sure you aren't trying to setup a 11 x 17 landscaped sheet? In
any event, setup your printer first.
File, Page Setup, Advanced on the right, type 18 width, 12 height or 17 width,
11 ...Find both matched and unmatched entries
I have two tables that I wish to find:
1) find matched data - copy that into a separate table
2) find unmatched data - copy that data into a separate table
Can I achieve the above results with one query?
I have three fields in each table that I would like to compare against:
Any assistance with this would be greatly appreciated...
Yes you can use an append query to do this quite simple BUT you would end up
with the same data in 2 tables which be a mistake.
Maybe you could post some more details of what you are trying to do (the end
result) a...How do I format a field based on the value
I have a form that is populated from a query using several tables. In the
detail section I have the following fields:
Category Code qty PO number ... .... ...
The qty is populated in the query based on whether or not a PO number is
available. If the PO number is not available then it comes from table a, if
it is available then it comes from table b.
On the form, I have my fields color-coded for easy reading. I would like to
be able to color the border and column heading based on where this value
comes from. If table A, then it is blue, if table b then it...Data Tables
In Excel 2000, must data tables (Data\Tables...) always be located on the
same worksheet tab as the source data? I want to put summary tables into a
separate worksheet (tab), but Excel won't let me do that.
Looks that way, at least in Excel97 (my ver)
But we could always mirror the data tables (or parts thereof)
elsewhere (eg: another sheet) via simple link formulas
"Steve D" <sdrenker_(you_know_what_here)_media.sj.nec.com> wrote in message
news:ufhMsZfyFHA.3804@TK2MSFTNGP10....Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table.
Excel (07) is not recognizing the field as a date. This is a field I
would like to group by in the pivottable. I am aware of all the
techniques to convert this to a date field, but I am searching for an
answer as to why XL pivot tables cannot consume the dates directly
from a sql query.
I have played with bringing the dates back in a number of differenent
formats with no success.
Any insights into this would be appreciated.
Probably coming in as text and yuo may only need to copy an unused cell and
paste sp...Table name length in microsoft query
Is it true that the maximum length of a tablename is still 8
characters. I'm useing Excel 97 and trying to get data from a Paradox
9 table via odbc-link and Microsoft Query.
I have heard a few years ago that long names are OK and supported all
over the microsoftian regime.
Is there any trick to avoid this without shortening all the names.
Greetings from Helsinki
...Formatting number in Data Table
I am using Excel 2007. I have a spreadsheet that the figures can be
negative or positve. I have formatted the cells with this custom
format $#,##0;[Red]$-#,##0. On the spreadsheet the format works fine.
I created a chart from the data. I have a data table at the bottom of
the chart. The problem is that the format from the spreadsheet is not
reflected in the data table. When I click on Format Data Table, there
is no option for number formatting. There is a number formatting
option available when i click Format Axis, but this doesn't change it,
because the actual axis is Text.
Is th...Format Painter button problem
When I wish to format several cells the same I double click the format
painter and I am able to format each cell without reclicking the button. I
have just upgraded to Excel 2003 and now I can only do that on a worksheet
that hasn't any VBA code attached. Is this a bug? I know other people on Mr
Excel are having the same problem and no one seems to have an answer. Thanks
for any response or help you may give me.
Depends upon the VBA code attached, I would imagine.
If you have worksheet event code that formatted cells, this formatting would
overwrite what you painted with the...Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot
of fiddling to make sure the formatting is correct, calculated fields
get added and chart series ranges collect all the data every time I add
a new row.
The insert (row) command seems to take care of all of this but it
inserts the empty row above the current row. As I'm always appending
data to the sheet, I would prefer this to be below the current row.
I've tried adding a dummy row under all the data containing the
formulas and formatting I need but unfortunately I get formula errors
and it screws up my chart....how to find all matches in an array
I have a task list that in one column lists the person primarily responsible
for it and in the 2nd column is the back up person. The 3rd column is the
task. On another sheet I want to put in someones name and bring up all tasks
they are responsible for and all the tasks they are backup for. What is an
easy way to do this.
Easiest way IMHO would be to use advanced filter
(No private emails please)
"Greg" <Greg@discussions.microsoft.com> wrote in message
news:FA4CB596-6451-4569-81AF-24EC18C50180...Find the number of years between two dates
I have aroster of players with thier date of births. I would like to
automatically add their ages in another column
Try this Formula, assuming that the dob is in cell A1
=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " &
DATEDIF(A1,NOW(),"md") & " days"
> I have aroster of players with thier date of births. I would like to
> automatically add their ages in another column
On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...Pvt Table Question
I am creating several pvt tables that will be updated often with new
data. I want to know if there is any function within a pvt table that
I can use to set up my pvt table so that only the top 10 data points
are automatically displayed. So for example, if i have pvt table set
up to show brand name and then accounts for each brand name and I
sorted on accounts descending and only wanted the pvt table to show the
top 10 brands that had the highest total accounts. Is this possible?
To show the top 10 values for a field - double-click the field heading,
click 'Advanced' ...Trouble with time formats
Any help appreciated!
I need to work out the average speed it would take to cover a certain
If you covered 10 miles in 28 minutes what would teh average speed be...
Column A: 10 miles
Column B: 28 minutes
Column C: Would return 21.43mph.
I then want to to add time increments of say 15 seconds in rows so that I
can see that if it took 28 minutes, 45 seconds, the average speed column
would return 20.87mph.
I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s),
but I want to format it as time, i.e: 00:28:45 (or 01:10:30)
Is this possible?
Thanks...Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width
box, yet using the mouse, I am able to adjust the width. When opening the
text box, I can delete the number that appears, but am unable to even reenter
that one after it is deleted. I must tell you that I'm a new user who's
working through numerous tutorials.
do you get an error message or what happens exactly
> Does anyone know why I am unable to enter a number into Excel's
> column width box, yet using the mouse, I am ...Imported text file w/o unique identifiers, find duplicates
Imported a txt file w/o unique identifiers and have run a query to find
possible duplicate records based on 3 seperate fields. How can I either
update those fields that may be in error in the original table or append a
new record w/o the duplicate data and delete the original record? In the
following I need to determine which records to keep, which to delete and/or
which to update.
End Date Total Sheets Time Operator Name
3/3/2007 14 0:00 00094123 B D MCNARA
3/3/2007 14 0:00 00095681 NAME UNKNOWN
3/17/2007 84 0:01 00091234 C D S
3/17/2007 84 0:01 000...Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers,
footers, column & row formatting across all the worksheets
in a workbook?
one way: formating them all at the same time:
- group the sheets (hold down the SHIFT key while selecting the sheets)
- apply the format to a cell, column, row
> How do I copy worksheet formatting, inc. custom headers,
> footers, column & row formatting across all the worksheets
> in a workbook?
You can also select the entire sheet with CTRL+A and do Edit / Copy, then group
yo...pivot table grand totals not showing for all columns...
i have a pivot table that shows the grand total for some columns, but
not for others...
there are no differences in the data layout, yet this happens?
any clues as to why? and how I can get the grand totals to show for
in the PivotTable floating toolbar, click PviotTable, then Table Options,
select 'Grand totals for columns' option.
...Can I use 'or' in conditional formatting?
I'd like to have 4 conditions, Red, Yellow, and Green. But I have two
conditions for green, without using two conditions for green is there
a way to use OR somehow to beat the limit?
PS Win2000 + XL2002
Sure ... just click on "Formula Is", and you can use "OR" for more then two
Will trigger the set format if "any" of the above equate to "True".
Please keep all corr...Table relationships
I've created a database with about 15 tables for a participant data in a
research study. Each participant will complete the 15 assessment measures.
My ultimate goal is to create a data entry system whereby a research
assistant can pull up a form and enter an ID number, plus the answers to the
15 assessment measures.
I know I can create a query to combine all the tables, but it seems like the
research assistant would have to enter the id and date 15 times (once for
each table pulled in to the query).
Is there a way to just enter the ID number one time and have it apply t...Conditional Formatting for Image box on Continuous Forms
This problem is driving me crazy. I am a car enthusiast and love
taking photos of classic cars. I have a table which describes the
make and model in a series of fields. There are several fields that
contain a reference to photos of those cars. Ie: the fields are
called "Front" "Side" "Rear" "Angle". My form has four image boxes to
display the images stored in these fields. But when I open the form
all the records show the images of the first car only.
I asked about this a few days ago and was advised to check out
http://www.lebans.co...date Format #28
Mahmoud Metwally Ali
Jaicorp IT Manager
Office Tel. : (202) 3471495/361
Mob. : (2010) 5195169