Can I use conditional formatting to scale a display value?
I have a value in a cell, I would like to display the value directly with a
text modifier if it is above a certain threshold or display a scaled value
with a different text modifier if it is below a threshold. I use the value
in a different calculation so I don't want to change the value of the cell,
only the way it displays.
Examples:
If the value is time:
.5 should read as "4 hours" while 2 should read as "2 days"
If it was money:
.5 should read as "50 cents" while 2 should read as "2 dollars"
Thanks. Tag: where do I find capital leasing calculators? Tag: 783595
Quick Sumproduct vs Text return
Hi Team,
I think I've lost it :P
I've got two lists.
c d e
9 Morry 2000 2004
10 Howie 2005 2009
11 Johno 2010 2019
12 Smithy 2020 2029
13 Jones 2030 3000
and
c d
19 John 2005
20 Sam 2010
21 Ralph 2011
22 fred 2001
23 mike 2020
24 Nat 2008
25 Sally 2015
26 steff 2025
27 diane 2027
I want to pull the name from list 1 and place it in column e if the number
in list 2 column d falls in the range in list1 columns d&e
I have this formula working to an extent
=SUMPRODUCT(--($E$9:$E$13>=$D19),--($D$9:$D$13<=$D19)*($C$9:$C$13))
This returns a #value error.
if I do some checking and modify list 1 to add a unique number in column f
=SUMPRODUCT(--($E$9:$E$13>=$D19),--($D$9:$D$13<=$D19),$F$9:$F$13)
It returns the correct unique number.....
So I figure the problem is returning the text??
Changing the forumla to this
=SUMPRODUCT(--($E$9:$E$13>=$D19),--($D$9:$D$13<=$D19),$C$9:$C$13)
does not work as it returns all 0.
any help is much welcomed. Tag: where do I find capital leasing calculators? Tag: 783594
How to send Excel selection using Live Mail
I have been using Outlook Express until this month, and have been sending
information from a selected range in an Excel worksheet to a number of people
every month. I don't want to send the entire (large) worksheet as an
attachment, but my "Mail Recipient" function has apparently been disabled
since installing Windows Live Mail. The only "mail" option I see remaining
is to mail the entire document as an attachment, which really isn't an option.
Since I send this information out to about 50 recipients each month, and
each recipient has different data, it's been tedious enough already. But
now, I'm looking for a way to send the data using Windows Live Mail without
having to cut and paste all afternoon.
If there is a way to efficiently do this using a Macro, that would be even
better...
Thanks, Tag: where do I find capital leasing calculators? Tag: 783593
Insert word to excel
If I embed a word document, it becomes a picture of the first page of the
word doc when I'm not in edit mode. I need to view the full word doc
alongside the spreadsheet data. Any suggestions?
Thanks Tag: where do I find capital leasing calculators? Tag: 783590
Adding up dropdown menu categories.
I created a dropdown list in my worksheet and I was wondering if there was
any way to add up how many times each individual item was selected. Any help
would be appreciated.
Thanks! Tag: where do I find capital leasing calculators? Tag: 783586
I need a formula that returns weekends and holidays for each mont
I have created a mileage log with each month having its own spreadsheet in a
workbook. The first sheet starts with February 2006 and the last sheet ends
with february 2007. There are 12 sheets in total. Each month start on the
first day and ends on the last day of that month. The calendar is South
African. How can I create a formula that shows a Saturday, Sunday and Holiday
per month. I have used =TEXT(A2, "ddd") to return days but I don't know how
to create a formula that also returns holidays.
Please help. Tag: where do I find capital leasing calculators? Tag: 783576
Percentage of a Column of numbers
I have a column of numbers and would like to know how many of those numbers
make up 80% of the total of the column. Does anybody have a formula for that? Tag: where do I find capital leasing calculators? Tag: 783575
Variance Report - Need to reflect 0%
Hello! I need my variance number to show 0% even when the formula's answer
is #DIV/0! Tag: where do I find capital leasing calculators? Tag: 783568
Amortization schedule with periodic payments
I have a loan, and the borrower is paying in an irregular fashion. Some
months he pays and some he doesn't.
How can I setup the amortization schedule to reflect this and add the
appropriate interest?
Thanks in advance
ezy Tag: where do I find capital leasing calculators? Tag: 783564
Disable Advanced Filter?
Is there a way that I can disable the Advanced Filter option in a protected
worksheet either through the UI or VBA? When I protect it I have the option
to disable AutoFilter, but Advanced Filter is still available and I don't
want users to have access to it.
Thanks in advance!
Kevin Tag: where do I find capital leasing calculators? Tag: 783561
Copy Paste Wierdness
Hello --
I have a Office Excel 2003 SP3. Here is the situation ...
I have a Workbook that has many Worksheets that have some pretty
intense formulas. I open a second instance of Excel with a blank
Workbook. I highlight the whole worksheet and hit copy. I then Paste
over on the new Blank Workbook ... and low and behold ... it is just
copying the values (no formulas).
If I perform the exact same test in the same instance of Excel it
works fine. The funny thing is I am unable to reproduce this on other
machines. Does anybody have any ideas of how this could be happening?
Thanks,
--Rich Tag: where do I find capital leasing calculators? Tag: 783560
please, please help
please, please help
how do i get sheets, duplicated from a master sheet, to automatically number
a field in each new opened sheet consequetive to the previous sheet. Tag: where do I find capital leasing calculators? Tag: 783559
Share Workbook command grayed out on menu/unavailable, Why?
I want to share the workbook I created but the command is grayed out on the
menu. Why would this be grayed out, is there a step I need to do before I
can share the workbook? Tag: where do I find capital leasing calculators? Tag: 783555
Excel set up is right to left rather than left to right
Office 2003 was installed in Hebrew. Excel reads from rt to left than left
to right. i.e. columns originate in the rt and the rows are indentified by
number on the rt rather than the left.
How do I switch the format to English?? Tag: where do I find capital leasing calculators? Tag: 783551
Limitations for Calculated Field formulas in Pivot Tables
Is there a list or even a guideline on what excel formulas are allowed when
creating a Calculated Field in Pivot Tables?
For example, I want to use the TODAY() function in the pivot table, but I
keep receiving errors. Is there a way to use a function that operates like
the TODAY or NOW functions within the Calculated Fields?
Thanks,
Brandon Tag: where do I find capital leasing calculators? Tag: 783550
ABC Report
Hi,
I have a spreadsheet with the total sales for one month on items that were
sold and have been asked to do an ABC report based on this info. We want to
see how many items make up 80%, 15% and 5% of the total sales for the month.
Then categorize those items as A, B, and C items. Is there a formula that
can designate each item as an A, B, or C item?
Thanks, Tag: where do I find capital leasing calculators? Tag: 783547
Excel 2003 colum
does anyone know if it is possible to increase the number of colums in a
single worksheet from 256 to 400 for excel 2003 ? Tag: where do I find capital leasing calculators? Tag: 783533
Greater Than and less than HELP PLEASE
Tried for hours to get this to work and ready to throw laptop out of window.
What I'm trying to do :
enter mileage into A1
For first 200 miles charge £0.40 per mile for miles above 200 Charge £0.25
per mile and enter combinded charge into A2.
and if mileage is less than 200 in A1 carry balance (200-A1) over to B1 to
increase B1 £0.40 rate i.e 200 plus balance from A1
Hope that makes sense . Many Thanks for any help. Tag: where do I find capital leasing calculators? Tag: 783532
Display #'s incorrectly (format)
=IF(AND(H3-O$1<0,M3-O$1<0,H3<>M3),"Past due
"&-MAX(H3-O$1,M3-O$1),MAX(H3-O$1,M3-O$1,0))
Formula above works great (Thanks to those that helped), but the problem I'm
now having is when account is not past due the other #'s return with serveral
0's. Example:00000 or 00221
I would like 221 to appear verses 00221 or 0 versas 0000
--
Elena Tag: where do I find capital leasing calculators? Tag: 783527
0 bytes
I saved an excel 07 file that contained 300 kb of data yet, when tried to
re-open get a message saying the file is corrupt or I have the wrong file
extension.
When I look at the files properties it now says 0 bytes!?
Any ideas!?
Andy Tag: where do I find capital leasing calculators? Tag: 783523
linking info from word and excel
I have a tool in excel spreadsheet that calculates scores and percentages.
After the calculations are complete, I have to transfer(retype) the results
and some of the info from that worksheet to a letter template in Word. In the
end I have to send out both the worsheet and the letter together. Is there a
way that I can combine both documents AND/OR have some of the info from the
excel worksheet to link and automatically update to the Word letter template
so that I don't have to send two separate documents and have manually type
the info from excel to word? Any suggestion is appreciated. Tag: where do I find capital leasing calculators? Tag: 783516
Convert Date Format to Minutes
I have a spreadsheet from an outside source and the Minutes are formatted as
a date, so in the formula bar it looks like this: 1/3/1900 10:00:00 PM but
in the actual cell, it looks like this: 94:00:00. How can I get the cell to
read just 94? Thanks!! Tag: where do I find capital leasing calculators? Tag: 783511
Showing when a file was updated
I am trying to figure out how to have a cell display when a file was last
updated. Is there a way to make it show the most current date that any cell
has been updated. I have tried using =TODAY() but that doesn't change with
the most recent change. Tag: where do I find capital leasing calculators? Tag: 783508
workday formula question
I am trying to create a schedule for a machine. I know how much of a day
each job will take. When I use a formula to just add column b to c, the
result actually includes the hour of the day, but I need to exclude the
weekend. When I use workday function then the fraction of the day seems to
not be included in the calculation. Is there a way to combine these
functions to add a portion of a day only using the work week?
B C b+c workday function=WORKDAY(C3,B3)
3.25 5/1/08 12:00 AM 5/4/08 6:00 AM 5/6/08 12:00 AM Tag: where do I find capital leasing calculators? Tag: 783505
Printing pages from Pivot Tables automatically
I have created a Pivot table tracking expenses and quanities of items that
each dept in our company has purchased for a given month. I want a new sheet
to print for each dept. I know how to filter by dragging the dept number
into the page area of the Pivot Table but I have to select each dept
individually and print. Isnt there a way I can have excel automatically
choose each individual dept and print their expenses on separate sheets?
Thanks! Tag: where do I find capital leasing calculators? Tag: 783503
Excel slow to save to network drive
I am trying to troubleshoot a machine that is running XP Pro and Office
2003. It is extremely slow to save files to a network drive with Excel.
Word is somewhat slow, but Excel is really bad. For example, it takes
almost 60 seconds to save a 175K Excel file. The server is in the same
building. I took a laptop down and plugged into the same ethernet hub
and opened and saved the same file. It took only 3-4 seconds. The
network in that office is somewhat slow, but still, there must be a
problem with the machine taking 60 seconds to save. The problem doesn't
seem to occur with local files.
What could be causing this?
Thanks,
Chris Tag: where do I find capital leasing calculators? Tag: 783498
Adding Shapes to Spreadsheets
I want to add an autoshape to a spreadsheet, but when I go to Autoshapes ,
the selection choices are not highlighted. As a result , I am unable to
select them. How do I get the choices to be highlighted / available for
selection? Tag: where do I find capital leasing calculators? Tag: 783479
Macro help
I'm new to macros, could some explain how I can use this macro to allow spell
check when a sheet is protected?
Using a macro is the only work around that I know of.
Sub Spell_Check()
ActiveSheet.Unprotect Password:="justme"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub
Unprotects the sheet, does the spellcheck then reprotects the sheet.
"justme" can be changed to your password.
Gord Dibben MS Excel MVP
On Sun, 10 Feb 2008 14:32:00 -0800, Brian <Brian@discussions.microsoft.com>
wrote:
>I made a spread sheet for work the has some cells protected then a password.
>But once I enable the password, the spell check cannot be used.
>
>Any help would be appreciated Tag: where do I find capital leasing calculators? Tag: 783477
Hide a column "if"?
Hello,
I have a workbook with 250 sheets. I need to be able to hide column F unless
cell E2 has the value "Level 1". If there is another value in E2 or it is
blank, I need column F to be hidden. I need this on all 250 sheets. Is this
possible?
Thanks in advance, you are all always to helpful!
tgcali Tag: where do I find capital leasing calculators? Tag: 783458
Saving an Excel Workbook
Dear all,
Is it possible to alter a workbook so that when a user opens it he/she is
automatically prompted to save it?
Also I have a cost model in Excel that has a cover worksheet in the
workbook. The cover sheet has a macro button that simply selects the next
worksheet. Is it possible to create prompt window to remind the user to save?
Thanks in advance,
Neil Tag: where do I find capital leasing calculators? Tag: 783452
Do you know how to send a single worksheet in Vista Excel?
I want to send just one worksheet from an Excel workbook in the Vista
version. I want to send it not as an attachment but as the body of the
email. Any ideas?
Thank you, Tag: where do I find capital leasing calculators? Tag: 783448
Difference between two dates with if statements
I'm a beginner...Please help
Dates are inputted in column M or H; but sometime dates are not required in
M and H which would leave it blank (if date is in column M there will not be
a date in column H and vice versa);
Cell B1 should always prompt today's date (=today())
I need column N to calculate days between them
example: B1=05/08/08 M=05/21/08 N should = 13
But if there's nothing in M and H it returns -39576
In that case I need "0" to be inputted verses -39576
and when B1 is greater then M/H column
I need the return to be "Past due "number of days between the dates"
Hope this makes sense.
Please help
Elena Tag: where do I find capital leasing calculators? Tag: 783443
Alternative for Vlookup output of #N/A if data not found?
Hello,
Is there an alternative for the Vlookup function output of #N/A when data is
not found? The reason I ask is because I need to use an If function based on
the Vlookup output if the data is not found such as =IF(K2="#N/A","SPARE",)
but the function does not recognize #N/A.
Thanks!
Jaret Tag: where do I find capital leasing calculators? Tag: 783442
Revealing the related pivot data
The situation is this...
Some of the people viewing my reports can click on the individual cells/data
value and it opens a new sheet revealing the related pivot data (or it
directs them to my pivot table). That said, others who do the same only get
the underlying formula displayed. It's almost like there is a feature that
needs to be turned on or maybe a patch that needs to be installed.
Can anyone tell me why some people can click to display the data and others
can't?
Appreciate the help.
Ken Tag: where do I find capital leasing calculators? Tag: 783438
formulas do not work
I have added a column in my excel spreadsheet and copied the formulas from
the column next to it. The correct formulas are in the cells, but they aren't
working. I have to go into each individual cell and press F2 and enter for
them to update. How do I get the cells to automatically update? I have used
excel for years and have never had this problem. Tag: where do I find capital leasing calculators? Tag: 783426
Default In-Cell Dropdown to a specific Index Value?
I have a scenario where I use a common list in a parent category (e.g. Work
Unit and a child category (e.g. Line Item) . I would like to default the
Line Item list value to the same list value as the parent Work Unit,
maintaining the in-cell dropdown structure in both cells. Tag: where do I find capital leasing calculators? Tag: 783423
Merging Workbooks
Is there a way to merge or combine multiple workbooks into one workbook on
one worksheet without copying and pasting? Tag: where do I find capital leasing calculators? Tag: 783421
Multiple Lookup Possibilities
I have a list which has an employer id and then employee id's. I need to
list all the employee ID's associated with one empoyer id.
I have a data entry sheet whereby the employer ID is entered; the next entry
is that of the employee ID. What I would like to create a drop down list for
employee ID's that would list all the possible employee Id's for a given
employer ID.
I would like to create a table that would be my list for the drop down box.
If I use a function like Vlookup it only finds the 1st Employee ID associated
with an Employer ID and thus my problem.
How can I create a macro to scroll down the Employer ID and Employee ID list
and populate a table of all the Employee ID's associated with a particular
Employer ID?
Note that I want to avoid cutting and pasting as I know this could be
accomplished via Data sort, etc. I would like some way to automate it. Tag: where do I find capital leasing calculators? Tag: 783418
Excel formula
why does my formula change when I add data example
FORMULA BEFORE ADDING DATA =COUNTIF(D9:D6369,33)
FORMULA AFTER DATA IS ADDED TO THE WORKSHEET =COUNTIF(D242:D6369,33)
This is the data that is entered in worksheet
10 080501 S-BU-99-M 46 0277 DOLLEW 5/7/2008 9:23
10 080501 S-BU-99-M 46 0278 ALBGRA 5/6/2008 14:21 Tag: where do I find capital leasing calculators? Tag: 783416
Excel Tabs
I have a worksheet with several tabs. Each tab is a different persons name.
Is there a way to have the name on the tab automatically show up in a
specific cell on the worksheet. Thank you! Tag: where do I find capital leasing calculators? Tag: 783415
Excel & Keyboard
I am looking for a keyboard shortcut (or key combination or hot key) that
lets you switch from the "Find Box" back to the worksheet and then back to
the "Find Box". Does anyone know if this is possible? Thanks! Tag: where do I find capital leasing calculators? Tag: 783406
SET DEFAULT FOR FILL HANDLE TO COPY ONLY
I want to set the deaul for the fill handle to copy cells only. Tag: where do I find capital leasing calculators? Tag: 783402
Adding Times
How do I add times (in minutes and hours) but show the total in days, hours
and minutes? This for a working day so a total of 9h15mins should display as
1d1h15mins. Tag: where do I find capital leasing calculators? Tag: 783399
show fractions rounded up to nearest 1/16th inch
I have cells formatted as fraction, up two digits. We are a mfg. company
cutting metal and if it is not on a tape measure, we don't use it. If we
input in an odd decimal value, we can get stuff like 17/20 or other wierd
unusable fractions. Is there a way to have the same cell of input to show a
result rounded up to the absolute nearest 1/16th of an inch? Tag: where do I find capital leasing calculators? Tag: 783392
Save Current Sheet Only
I have a file that I use a pivot ( show pages) that creates 200 sheets
(customers). I then want to send each sheet out individually to each
customer. What is the easiest way to break up the file by each sheet
and save each sheet as an individual file?
Thanks Tag: where do I find capital leasing calculators? Tag: 783389
Repeat Values in Column A, Break and Sum
I have data in Column A that repeats in groups; AAA, BBB, CCC, etc. Each of
these column A values is associated with a varible in column B. I want to
break at the end of a group and sum the column B.
Here's what I have and what I need.
What I have:
AAA 111
AAA 222
AAA 111
BBB 444
BBB 222
BBB 111
BBB 111
CCC 444
DDD 222
DDD 666
Here's what I want -- the break and the summation of Column B
AAA 111
AAA 222
AAA 111
444 (Sum of AAA)
BBB 444
BBB 222
BBB 111
BBB 111
888 (Sum of BBB)
CCC 444
444 (Sum of CCC)
DDD 222
DDD 666
888 (Sum of DDD)
I appreciate anything you can do to help. Tag: where do I find capital leasing calculators? Tag: 783388