how can I show repeating values in a chart?
I would like to show modes in the form of a pie chart but am not sure how.
For example the number 73 comes up 3 times in a column on my spreadsheet, how
can I show that compared to the number 50 which come up 2 times in the sheet?
Thanks for the help
You will need to compute those values using formula or a pivot table and
then chart the results.
> I would like to show modes in the form of a pie chart but am not sure how.
> For example the number 73 comes up 3 times in a column on my spreadsheet, how
> can I show that compared to the number 50 whi...Excel 2002
Is it only me then that thinks this feature is mad ?
As it happens I found out the if you hide your detailed data using the
out-line feature instead of the auto-filter, then you "can" have your cake
and eat it ( Ken ) ! Hiding the detail using the out-line doe not make
subtotal evaluate only visible data. Cool !
All messages from thread
Message 1 in thread
From: tur13o (email@example.com)
Subject: Excel 2002 - subtotal function only calculates visible cells
in an auto filtered range. Why ?
...VBA?Macro Newbie Question//Last field in a column with a value.
I am a newbie to programming Excel. I Have a workbook which has a
average about 40 worksheets. I keep individual attendance and payment
for my Alcohol and Drug Treatment program on these worksheets. Th
individual worksheets are linked to a master roster worksheet in
My question for today is how can I link the last payment in a column i
an individual worksheet to the master roster so I can tell at a glanc
when a person made their last payment? Also, how could I link th
appropriate cells to the master without doing it manually every time
enter a new client?
...Return Value from cells which match criteria (complex)
I have a worksheet I want to compute mileage on. Right now we're doing
everything manually, but I want to see if I can automate the process in Excel
without resorting to a lot (if any) VB code.
The formula needs to examine 4 columns for the start point, then 43 or so
columns for the destination point. From there it can reference another sheet
with the mileage between two points and insert the correct value.
The logic looks something like this: "Look for data in array a and in array
b then depending on which columns hold the data, lookup the corresponding
result in ...Assigning a cell address to a variable
I'm sure this is a simple question, but I've been searching and cannot seem
to find the answer to it. I have a couple of loops that run that put data
onto a spreadsheet, I want to before the loop runs assign a variable the
address of the first cell and at the end assign a variable the address of the
last cell. I am trying to use
Set LastDay = Range(Cells(3, CalCount))
but this gives me an error. Any ideas or help I would love.
This would set the variable LastDay to the address of the last used cell in
column A and then create a named range from ...select part of a cell value string
I have a column of text (postcodes/zipcodes) in the following formats L3*1MT
or L12*3DE. What i want to do is have a second column that reads the first
column value and removes the *data, leaving just the L3 or L12. How can i do
"rich_j_h" <firstname.lastname@example.org> wrote in message
> I have a column of text (postcodes/zipcodes) in the following formats
> or L12*3DE. What ...Counting unique values #2
I am trying to count the number of unique referrals for numerous
agencies. Using the table below....
...the result should be...
How can this be done? I have over 7000 entries to process!
I've tried filtering and Pivot Tables, both without success. Any
pointers would be gratefully received!
---------------------------------------------...How to sum few items
I want to sum up those numbers in B column which contains Y in their
opposite in A column.
Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28227
View this thread: http://www.excelforum.com/showthread.php?threadid=479050
Use the SUMIF function
(remove nothere from the email address if mailing direct)
&q...gather information from another sheet with one related value
I have a workbook with two worksheets. Each sheet is getting it's data from
a different source. Sheet 3 was exported from access and sheet two is from
another workbook. There is one value (per record) that relates the 2 sheets
and I need to bring information from the related value row to the second
I have: =MATCH(B3,Sheet3!A:A,0) In cell M3 of sheet 2, this has given me
the row number of the record from sheet 3. Which is (10)
then I: =ADDRESS(M3,2,1) In cell N3 of worksheet 2, this has given me the
cell address. Which is (B$10$)
How can I get the value from the cell addre...Matching a List Containing Redundant Values
I have a set of numbers (auto mileage), located in a horizontal range named
I have a corresponding set of addresses, located in a horizontal range named
Rental_Addresses, matched to Rental_Totals.
I need the two ranges automatically sorted vertically in ascending order of
Rental_Totals. To do this, I located a post from Bob Phillips (
http://tinyurl.com/6k462 ) that almost* solves my problem, and I handled it
Under heading Auto Mileage, I drag this down a column starting at F4:
= SMALL( Rental_Totals, ROW(A1) )
Under heading Rental Addresses, I drag this down a co...pausing a macro to input cell contents
My first macro using XP, Excel 2003.
During the Recording of a macro, what syntax/keystroke is required to
'pause' a macro so as to allow input of data into a cell address, and then
re-initate continuance of the macro to its next step? I do not want to end
or stop the macro, just pause it automatically to enter data.
Pause for input........................
'some of your code goes here
usrinput = InputBox("enter a number")
Range("A1").Value = usrinput
'resume rest of code
Or if you want to select a cell or ra...Secondary Value within Bar
I am trying to set up a simple bar chart. Example: Total$
2003, 2002, 2001. Within each of those bars I need to
fill the bar up with another value. Example Total$ was
10000, but 5000 when towards another category. I need to
show the total as 10000, but fill the bar up to 5000 with
another design. Make sense?
You could make a stacked bar (or column) chart. In these you do not plot
the total, but all the constituents of the total. If you don't know them
all, use 'Other' and use a formula to subtract all the known
constituents from the total. So your chart would ha...Problem with Pick List associated cell
Can anybody help me with this issue. I am trying to select an item from a pick list and be able to automatically copy the adjacent cell value to another cell.
This is the pick list
PAPER HOLDER - LEVEL I (CHROME) $2.80
TOWEL BAR - 24" LEVEL I (CHROME) $2.80
TOWEL BAR - 18" LEVEL I (CHROME) $2.80
Once I choose the item to fill in another cell location, I want the adjacent price to automatically fill in another cell location as well. One idea, use a formula in a col adjacent to the DV pick list
Let's say your pick list as posted is in B2 down
You could have this ...FUTA / SUTA Values are incorrect
I need to print FUTA reports but the numbers are incorrect. Does
anyone know how to adjust the FUTA / SUTA values in Great Plains?
If you're wanting to change the rates, go to Microsoft Dynamics
GP>Tools>Setup>Payroll>Unemployment Tax. From here you can select the
state(s) involved and change the values.
Director of Information
Hopkinsville Milling Company
> I need to print FUTA reports but the numbers are incorrect. Does
> anyone know how to adjust the FUTA / SUTA values in Great Plains?
...text cells, crosstabs? drilling down?
I have a workbook with 100 identical format worksheets.
Each worksheet is an educational course. There are 25 row
labels down the left representing 25 different
competencies we need to teach. There are about 12 column
labels to indicate if the course helps teach the
competency and how it is measured if it is taught (such as
written exam, oral exam, lab practical, specific
competency on patient care, etc.) The cells only have
text data (many cells have no data whatsoever). Is there
any way to take advantage of something like a crosstab for
numerical data using excel? If excel c...Counting cells with a value in them
We are running an "If" equation on a column to determine if an event i
taking too long to do. The resulting equation will yield an "X" if i
is taking to long and a " " if not. I would like to have an equatio
calculate the number of "X" in the column.
chrisn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=650
View this thread: http://www.excelforum.com/showthread.php?threadid=31880
We had a brain cramp and one of th...Click in cell w/ formula and get colors in referenced cells
When you click in a cell with a formula, Excel will then put color
around the cells that are referenced.
Somehow I turned that feature off. Now I do not get colors in th
other cells. Where is that option to turn it back on???? I've looke
and looked but I just can't find that option.
Thanks for the help
albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875
View this thread: http://www.excelforum.com/showthread.php?threadid=48436
...Cell Formats (like StyleSheets)
Operating System: Mac OS X 10.5 (Leopard)
I cannot find the way to store some characteristics (font, size, ....) of cell-formatting to own formats (like stylesheets, for reusing them) <br>
There are just 6 standards and I cannot add more. <br><br>Any ideas?
Sure you can :-) Where are you trying?
The easiest way is to format a cell the way you want the style configured
then while the cell is selected go to Format> Style. Just type the name you
want the style to have then click 'OK'.
Alternatively, go to Format> S...Cell Display Issue
Operating System: Mac OS X 10.6 (Snow Leopard)
I have a spreadsheet that I created in Excel 2007 (windows) and am using in Excel 2008 Mac. I cannot get the cells the right width to display the numbers in all the cells. <br><br>When the column is too narrow, "####" is displayed as you would expect. But when I increase the column width to correctly display that cell, other cells in the same column that were displaying correctly now display "# #####". <br><br>Increasing the column does not solve t...Can you freeze a result in a cell?
Here's the dilemma:
I have a list validation in H7. You can choose from 6 different text
Below, in H8, I would like to respond "true" (or yes) if a certain 1 of
the 6 text values is EVER selected, and then not be changed by anyone
For instance, if the "flagged" value is "blue", and a user ever selects
blue from the drop-down list in H7, can I create an argument that
populates H8 with "True"? And keep it as true forever, even if you go
and change H7 to another value.
Hope this makes sense. Thanks sirs and madams!
You *CO...Locking a Cell after It's Value is Calculated.
I have One Cell whose value is constantly changing once a week.
The results of that cell, I would like to have placed in
differents cells, once a week.
Input in cell WK3, resulting calculation ends in cell M3, then
cell M3 is copied to worksheet2, A1 (=worksheet1M3) I got this!
Input in cell WL3, resulting calculation ends in cell M3, then
M3 is copied to worksheet2, A2:
The problem is that cell A1 changes to the new number
Can I lock cell A1 after the value is copied to it?
Can I set conditions (time / date) on the cell after the value i
copied to it so it won...Counting colored cells?
Does anyone know a way if I can do something like a SUMIF() based on
the background color of cells? For example, if I have data and I
color the cells in column individually, and I want to sum the contents
of all the green cells, can I do that? Furthermore, can I do a
COUNTIF() to count the number of cells with a given color?
If cells are colored due to Conditional Format then use the CF criteria to
If colored manually, you must use a VBA Function.
See Chip Pearson's site for this.
Gord Dibben MS Excel MVP
On 1...IF SUM #3
Hi need need a foulmles for the following,
Total Sales Total Hrs Man Hours Prod
Paul Smith 4 7.25 8
91% £16 0.55
Total Sales Total Hrs Man Hours Prod
Paul Smith 4 7 8
88% £8 0.55
THIS IS A TABLE WHERE IT GET IN INFORT
Ratio Productivity Bonus
0.35 - 0.4 90% 2.00
0.41 - 0.5 ...Dlookup minimum value
I'm trying to use Dlookup to get the minimum date from a table.
I was trying to do the following command:
Somehow the code points an error on"Min", saying that "Sub or Function not
Is it possible what i'm doing? Is there any other way instead of looping
through all the records?
Try DMin() instead of DLookup()
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rat...Calculations not working on pasted values from Access
I'm using Office 2000 on W2k, and when I copy and paste a
column of numbers from MS Access to MS Excel, I am unable
to perform a Sum function on the pasted values. The
function always returns zero. If I then type in the values
directly it does work. Anyone know why? Is this caused by
a virus and is there a patch?
It is because it is seen as text, try to copy an empty cell,
select the imported numbers, do edit>paste special and select add.
"Scott Sullivan" <email@example.com> wrote in message