Vary Color based on value
Is there a way in a bar chart to have a bar be a specific color if less than,
lets say "5" ? Instead of manually changing each bar that is less than a
Create two additonal columns of formulas, suppose your original data is in
B2:B12, then in C2 enter the formula =IF(B2<=5,B2,0)
in cell D2 enter the formula =IF(B2>5,B2,0)
Copy these formulas down.
Plot these ranges instead of your original range. Format one series one
color the other another.
If this helps, please click the Yes button.
>...Selecting X cells based on answer to formula
I am trying to put together a spreadsheet that performs a couple of tasks.
The first is basic resourcing which i am ok with, sort of how many heads and
how many hours available means X cases will be done.
The next thing I need to do is use the X cases result to identify from a
list the oldest date and work through sequentially X dates in that column,
which will not be consecutive, and tell me what the date of that case is. So
if there were 10 cases done I would like one cell saying the oldest case
worked on was - first date in column. And following doing the 10 cases I'd
The currency in Turkey will be changed next year by dividing the existant
currency by 1.000.000. How can I divide the amount of all of my transactions
by 1.000.000 ?
...Hide/Delete entire rows based in the content of one cell
Hello all. I have a spreadsheet that is over 500 rows long. As it is I have
no use for all of the rows at the same time and have to keep hiding and
showing them as need arises. Is there a macro to hide chunks of it based on
the value of one cell of the row? In other words, en each row I will have a
formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to
hide the row or not. I tried case.select but it takes a LONG time and I would
have to write a piece of code for every line. FYI, the rows that need hiding
will be in sequence, in other words, fro...Add non-inventory item in MO Reciept entry (Unit Cost)
I am using the (Add Component) button on the MO Reciept entry screen to add
some non-inventory items, I notice that the decimal places for the unit cost
is (2) although my Functional currency is (3).
Kindly advice for the reason behind this, or if there is any setup required
Go to Tools --> Setup --> Purchasing --> Purchase Order Processing
Find Decimal Places for Non-inventoried Items, click on the Expantion Button
near to the Currency Field and set the decimal places for you curreny.
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
S...SUM cells in column based upon criteria in two others.
Can not seem to put together a formula that accomplishes the following:
Column J contains contributions by individuals for 2010
Column I contains contributions by individuals made in 2009
Column N contains an indicator (+ - or 0) of the increase or decrease over
the two years
Column P contains a designation of which members are new members.
I need a formula that looks at column I cells against column J cells, totals
the contributions made be each of the criteria in column N, but does NOT
include those with any designation in column P. Example of the data:
I ...How do I pivot a range of cells, i.e. from column to row?
How do I pivot/transpose the data from a range of cells from, say, a column
to a row? I imagine it should be very simple, but I haven't found out how
yet. For instance:
1 data 1
2 data 2
3 data 3
4 data 4
column# 1 2 3 4
data1 data2 data3 data4
...Cannot open Price Sheet
We encounter an error message that says "This record is currently in use by
another user" when trying to open a Price Sheet although no one is using such
Kindly advise how to fix this problem.
I already found the solution. I cleared the tables in Dynamics:
And in TempDb:
> Hi All,
> We encounter an error message that says "This record is currently in use by
> another user" when trying t...Return Range Based on Cell Value
This topic has been covered in varying degrees, but the problem is
that I do not want to sum or count the range. I want the range itself
returned as the value.
For instance in A1 is 7. i.e. July
In B1-B12 are dates 201101, 201102, etc.
I need a formula that will count out the cells and return the range
based on that value in A1. In this case it would return B1:B7. This of
course would be nested in another formula.
On Tue, 4 Oct 2011 17:18:20 -0700 (PDT), Pablo < > wrote:
>This topic has been covered in varying degrees, but the problem is
>that I do not want to sum or count the...can i create formula giving totals based on financial & text info
Am i able to create a formula that gives me monetray totals for expenditure
on hotels, split into totals for 6 varying business sectors?
...Portfolio stock prices
Anyone else having problems today with Canadian Stock prices in Money 2005
Canadian Standard Edition. All my prices are 1000 times the actual stock
price, makes me feel like Bill Gates.
Yes, I am having the same problem today.
"Michael Brimicombe" wrote:
> Anyone else having problems today with Canadian Stock prices in Money 2005
> Canadian Standard Edition. All my prices are 1000 times the actual stock
> price, makes me feel like Bill Gates.
Just checked again, prices seem to be correct now.
"Mark - Canada" <Mark -...T/F Can't set Hyperlink base from within macro
I wanted to set the Hyperlink base from within a macro. Then I read:
Returns a DocumentProperties collection that represents all the built-in
document properties for the specified workbook. Read-only."
Among the listed built-in properties is "Hyperlink Base". Putting that
together with "read only", I deduce that although I can set the
Hyperlink Base as a user, this cannot be done via a macro.
Am I correct or is there a loophole somewhere that would allow me to do
...Factors governing Inventory Cost Calculation at dealership
"Shankar Raman" <Shankar Raman@discussions.microsoft.com> wrote in message
I am trying to figure out how to set special pricing for specific customers.
I already have retail, A & B pricing, with A being lowest, then B, and
retail being the highest price. I offer the different prices based on
Now, I am trying to set up a customer with special prices on a select number
of items. these prices are below A pricing, and i have been changing the
pricing on each transaction. this is very time consuming, and prone to
making mistakes. i would like to be able to set up a special pricing
structure for specific customers on a job by job basis, or for a set per...pivot table with dynamic base data
im trying to create a pivot table where I can change my base data (inserting
or deleting rows of data) and still have this reflected in the pivot table.
My solution was to use entire columns as my range for input to the
pivotTable, but this means including blank rows which seems to muck up the
I cant think how else i can allow for new rows to be added to the base data
anyone got any ideas how to do this?
thanks loads for your help!
You can use a dynamic range as the source. There are instructions here:
> hiy...Money 2002 Online Price Updates
I have recently updated my computer to VISTA and have had to re-instal
Money2002 Personal and Business edition. Since then the online price updates
no longer run on start up. I have to manually use the update price option and
this does retrieve updated prices for the shares as expected.
Does anyone kn ow how to fix the problem so that the update of prices
happens automatically every 6 hours as it used to do when I was using Windos
Thanks in antcipation of getting a resolution to this problem.
...Evaluate a Range
I would like to create a formula that looks at a range with a specified
criterion and when it finds the first entry in that range that meets
that criterion returns the position of that data in the range. For
example, given the following data beginning in A1 and extending to D1:
I would like to create a formula that looks for a value greater than 10
in that range and then returns the number 4 to signify that it is the
fourth entry in that range. Thanks as always for your help.
Message posted from http://www.ExcelForum.com/
Array enter (CTRL-SHIFT-ENTER or CMD-RET...Multiple time ranges
I am trying to create a formula that will look up the time values and
return a letter.
Times are in column F.
Must include seconds (as values does include seconds).
07:00:00 - 17:30:00 = "A"
17:31:00 - 00:30:00 = "B"
00:31:00 - 06:59:00 = "C"
<as values does include seconds).>
But you miss out whole minutes in your specications.
Anyway, this should work, but do check your threshold values. Maybe you need
to add a second or less than a second.
Set up a table like this (in this example: A1:B4)
With your time to l...How to Drag/Move the IE control based Application
I am developing Dialog based application. I have placed Microsoft
WebBrowser control(IE control) on main Dialog of application. On the IE
control, all messages are being passed to IE control.
Now problem is that I am unable to drag/move this IE Control/Dialog. Any
idea about moving IE control/Dialog
Please help me in this regard.
...how to define range names
how do name cells so that I can see what the formula is calculating?
Such as A1 * B1 = Final Price
where A1 is price and B1 is discount
so it reads in the final price cell as price * discount
thanks in advance,
One quick way is to use the namebox
(the droplist box just to the left of the "=" sign)
In Sheet1, say:
Select A1, then click inside the namebox,
and type the name: price, then press ENTER
Repeat to name cell B1 as: discount
Then we could put in say C1: = price*discount
The other (pe...Bug: printing of a specific page range is broken in MFC-VS2005
If you create an MFC application that includes printing support, and your
application does print-time pagination as described in this MSDN library
then specifying printing of a specific range of pages in the print dialog
does not work. The application always prints all pages of the document.
To see this problem download and build the WordPad sample MFC application.
Start a new blank document, then paste into it enough text to fill several
pages. Choose Print from the File menu. Choose a print range of page 1 to
page 1....no permission to send the message for the specified user
How do I solve this? You do not have permission to send the message on
behalf of the specified user. I am trying to forward faxes from Outlook.
You can't use forward with faxes. You have to Open the fax then Print it to
your fax printer and generate a new fax.
"Barbara" <Barbara@discussions.microsoft.com> wrote in message
> How do I solve this? You do not have permission to send the message on
> behalf of the specified user. ...ActX as base?
I have an Act X control that I'm using but would like to replicate it
a few more functions. Can an Act X control be used as a base class, and if
so, how? I tried to replacing the 'COleControl' with my 'CAXBaseCtrl' in a
Act X's ctl.h file, but blew up with a lot of "...unresolved external
so maybe it can't be done?
...Canon Rebel Price
Canon LP-E5 Battery Pack for Canon Digital Rebel XS & XSi Digital SLR Camera
Best deal: http://bestdealfinder.us/index.php?id=B0015GADB4
Canon NB-2LH Rechargeable Battery Pack for Rebel XT/XTi Digital SLR Cameras and VIXIA HV Series and ZR Series Camcorders
Best deal: http://bestdealfinder.us/index.php?id=B0002YE686
Canon XS/XSI Digital Rebel Kit with Case, 58mm UV Filter and LP-E5 Li-ion Battery
Image: http://bestdealfinder.us/im...how do i work out what price to charge
i am a nail technician, and would like help with working out what i spend
every month( on supplies e.g nails, nail polish , nail polish remover, fuel(
car) , etc etc ), then be able to have a total figure at the end where i
charge for a certain job, i.e french nails $30... by being able to work out
how much im spending a month, compared to how much im earning... i'll soon
realise whether or not im under charging myself
look forward to your response
Good questions. You could start with a simple table, say with 5
columns: Date, Category, Description, Expense, Revenue....