How do I add the last 10 cells
If I have a column labelled, say, temperature, that is often being added
to, how do I add up the last 10 cells. I want excel to do it for me
automatically. I want a formula that will find the last filled-in (non
blank) cell in a column and then add up the 10 entries above that.
Assuming your data starts in A10, then in A9 perhaps, try the following:-
If your data started in A15, formula would be:-
This assumes you have no other data below this range in that column (Or at least within the COUNT
range)....find cell value
i have this formula that identifies the last column with data in it.
this works a treat
what i would like to do is us the result of this and subtract 3 column
ADDRESS(35,MATCH(6.022*10^23,33:33)) = N35
i would like to have a formula that takes N35 and sub tracts 3
columns fro it giving K33
i then intend making that my range
ie k12:n33 and copy the data else where to work on
If =ADDRESS(35,MATCH(6.022*10^23,33:33)) returns $N$35
then =ADDRESS(35,MATCH(6.022*10^23,33:33)-3) will return $K$35...'sticky' cell selection
When I select a cell it cannot be deselected and if i move my mouse it
selects all cells from the original. I cannot select a singular cell anywhere
else on the worksheet or select any other commands. Thus the name sticky. How
do I unstick my selection to carry on using the worksheet.
yippeekiay, sounds like you are in Extended Mode, EXT on in the right hand
corner or the status bay, press the F8 key to get out of it
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always apprecia...Average & blank cells
I have an issue with a sheet and an average formula:
I am subtracting one date from another to achieve a result (no problems) i have used the following if statement:
=IF(E5-D5=0,"",E5-D5) to not show zero values as there is a long list of dates and not all the list would be fillied in all the time.
My problem is that i need to create an average for these figures but need to also include the zero's in the average formula that may have the same start and end date (which results in zero but not displayed due to my if statement) without including the zeros in the cell...16 th digit of a number in a cell gets chopped to zero
I am trying to enter a 16 digit number, like a credit
card number, and no matter what kind of formatting I try,
the 16th digit of the nunmber gets reset to zero!
Actually, this happens for the 16th digit and beyond as
well. i.e, the 17th, 18th, etc all get reset to zero no
matter what digit I entered. Which begs the question
whether this is a bug or is there is a upper number limit
that a cell can display? If its the latter, then thats
pretty dumb, since all I wish to do is just display this
number and not run any math operations on it! I tried
formatting it as text, or using a speci...Hide button based on a cell value
I have been trying to write a macro that would hide a button based on a cell
value (cell N20). Somehow, it doesn't work. If N20=1, the button should be
visible. If not, then it should be hidden.
The button is called Button 4388 and the sheet is called Results.
This is what I have come up with so far but as I said it doesn't work
(please don't laught, I'm a beginner!):
Dim myButton As Button
Set myButton = .Buttons("Button 4388")
If Range("N20").Value = "1" Then
..Visible = True
E...Exporting Names and Addresses to Outlook
From Outlook, I have tried to import data from Excel into
a Contacts list...but when I go to do so, an error message
in the Outlook Import/Export Wizard pops up telling me to
name the range of data in Excel I'd like to import.
When I go to Excel and search the help menu, nothing
> Hey there.
> From Outlook, I have tried to import data from Excel into
> a Contacts list...but when I go to do so, an error message
> in the Outlook Import/Export Wizard pops up telling me to
> name the range of data in Excel I'd like...OL 2002 Lists Multiple Names per Contact When Addressing Emails
Sorry if this question has been asked before but I have
not been able to resolve or find relevant solution.
When addressing an email in IMO OL 2002 its lists multiple
instances per contact when the contact has more than one
email address. Fax numbers are also some how liste but
don't know why.
IN IMO OL 2000 only the default email address was listed
when addressing emails.
Is there a setting somewhere or software that I could buy
for OL 2002 to make it behave like 2000?
The following information is an excerpt from 289467 User Profiles and
http://support.micr...Merging Info in Two Cells #2
I did get a partial response, thanks Barb, but now I need to dig into
VBA and I stumble.
I guess the formatting needs to be done in VBA and the easiest way is
by copying each cell and pasting the values (otherwise, I am not sure
if I can do partial formatting of a cell, i.e. superscripting parts of
So the code would read:
Operation:=xlNone, SkipBlanks _
With ActiveCell.Characters(Start:=4, Length:=9).Font
...Return value based on 2 items
please help am not very good at explaining things but this is my sample list
05 p5 Q5
Tom Plow 1-6
Tom Plow 6.1-12
Tom Plow 12 .1-18
Tom Plow 18.1-24
Tom Shovel 1-6
Tom ...PDF icon in calculated cells
I have a calculated column and a PDF icon has appeared. If I delete the
contents of the cells, the PDF icon remains. How can I delete the PDF icon?
Right click on the icon and select cut from the popup menu.
"Texas Bald Eagle" <Texas Bald Eagle@discussions.microsoft.com> wrote in
> I have a calculated column and a PDF icon has appeared. If I delete the
> contents of the cells, the PDF icon remains. How can I delete the PDF
...E-mail addresses executing
I maintain a directory of homeowner association members in Excel. One of the
columns is the e-mail address of each member. Formerly the file was in Excel
97. Moved it to a computer running Excel 2002. Now the new e-mail address
entries are in executable form (excuse ignornace of Internet
terms--hyperlink?), i.e. when I click on the cell, the program attempts to
send an e-mail to that address. This is not what I want. I've tried
formatting the cell differently, to no avail. How do I turn off this
feature, so that the addresses go in as ordinary text?
copy the email address to...Cannot add email address to contacts
When I try to add an email address to a contact, the email address will
not save. I enter the email address in the proper spot in the upper
right hand corner, click on either save and exit or save and the
contact then only saves with the name of the contact, but no email
I am using Outlook 2003 with XP Pro with SP 2 and the service pack for
My HDD crashed and I am starting over and cannot get my email addresses
Thanks for your help.
...Using Indirect Cell References in a Chart
I am trying to create a 'self-sizing' chart, but don't
know if I can use indirect cell references in a chart.
Here's the scenario: I have a tab with data(DataTable!
A2:A20) and a tab with Graphs (Graphs). I use a Max
formula to determine the last row of data entered, and
I've labled that formulas as 'DataTable!LastRow'.
In my 'Graphs' tab, I have a cell called 'Graphs!
XAxisLabel' with the formula ='Datatable!A3:A'&(DataTable!
LastRow)' that displays the rows of data to be used in the
chart. I want to use a formula (=Graph...Flag row if cell values = something specific
Let's say A1 = top
and B1 = Bottom
in C1, I want to say that if A1 = top and B1 = bottom then the cell
background color of C1 should be red.
How can I do this?
And I need to do this in a macro. I can't use conditional formating from the
I have a recorded macro that does all my formatting and I need to add this.
"Some Dude" <firstname.lastname@example.org> wrote in message
> Let's say A1 = top
> and B1 = Bottom
> in C1, I want to say that if A1 = top and B1 = bottom then the cell
> background color of...Can you change cell fonts using formula?
I have a formular:
=REPT("|",(VLOOKUP($E$4,SummaryTable,6,FALSE)/200))& " " &
but I need to change the font of the 2nd
Is this possible to do?
No. but you can change to text and do whatever is desired. However, you no
longer have a formula.
Microsoft MVP Excel
"Ayo" <Ayo@discussions.microsoft.com> wrote in message
>I hav...Vlookup(s) problem
I'm hoping someone can help me out.
I'm trying to automate the creation of a vendor report that lists al
vendor sales. Basically I need a formula that, based on a unique vendo
- check colomn A for the vendor number match
- then check column B to see if there is a buyer number (which mean
that the item is sold)
- then dumps then 'nth' occurance of the value / text from the column
I'm currrently using the formula:
ROW(Catalog!$A$2)+1,ROW(Cat...Inserting Outlook Addresses into a Word Doc
When you use your Outlook 2002 Contacts folder to insert
a single address into a Word document, the only way a
Contact shows up in the Contact list is if you have made
an entry in either the Contact forms Fax or E-mail
The problem is that if you only have a Fax number and no
e-mail listed for the Contact, the Contact's name is
inserted into the Word document with the words "(Business
Fax)" or "(Home Fax)" attached to the Full Name. How can
you eliminate the insertion of these words?
What happens if your Contact has neither a Fax number nor
an e...Unwanted e-mail addresses
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC
Email Client: pop
E-mail addresses from spam emails keep getting added to my address book, but when I open it to delete them, I can't find them. I know they're there because they come up when I try to address an email. Where are they kept and how can I remove them?
On 1/7/10 2:58 PM, in article 59bb046c.-1@webcrossing.JaKIaxP2ac0,
"Texile@officeformac.com" <Texile@officeformac.com> wrote:
> E-mail addresses from spam emails keep getting added to my address book, but
> when I o...Delete cells with content that don't contain the =?UTF-8?B?wqMgc3ltYm9s?=
I have a very large messy excel file that contains some data I want to isolate. The other data is unneeded and basically in the way.
I want to delete/clear all the other cells that do not contain the ? pound symbol. This way I will be left with just the pricing info I need.
All help appreciated
On Fri, 23 Mar 2012 12:46:11 GMT, Gary N <email@example.com> wrote:
>I want to delete/clear all the other cells that do not contain the ? pound symbol.
"Be careful what you wish for"
This can be done with a VBA Macro:
To enter this Macro (Sub), <alt-F11> opens t...DateDiff("yyyy",[Anniversary],Now()) returns rounded number
I am using DateDiff("yyyy",[Anniversary],Now()) in a query to return the
number of years between two dates. Since it is an Anniversary calculation,
the result needs to NOT round up. Currently, when the result is 11.73, it
rounds up to 12 when I really need it to stay at 11 until the anniversary
date is reached. Thanks in advance for any help.
First of all DateDiff returns integer values and never fractional values.
DateDiff returns the number of transitions that occur. So Dec 31 2008 to Jan 1
2009 will return 1 year even though only one day has elapsed.
The number...Vlookup problem
I am having a problem with a vlookup fromula returning an #N/A value when
using a cell with another vlookup formula as the lookup_ value. I created
this same spreadsheet a year ago and it worked fine. I even opened the old
one and I can't see any differences at all. I'd appreciate any help anyone
We'd be happy to help, but you need to meet us halfway.
What's the formula?
What data is providing the #N/A result?
"tpocccfo" <firstname.lastname@example.org> wrote in message
news:6EFE978D-A5DE-4DCC-8B4...Enter date automatically in cell
Hi i would like my system date to be inputted into an
excel cell, how do i go about doing this.
CTRL-; will add the date in the activecell
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
"MO" <email@example.com> wrote in message news:firstname.lastname@example.org...
> Hi i would like my system date to be inputted into an
> excel cell, how do i go about doing this.
Enter =Today() in the cell
>Hi i would like my system date to b...Addresses and Contacts
I think this is the most confusing part of Outlook. Iam trying to move
everything over to a new laptop. I have copied all my pst files. If i try
and create a new email now, there are no addresses to select from....But if
I go into the contacts within Outlook they are there.
What do I have to do to transfer my contacts over so I can use them???
How do the contacts work in Outlook?
I have my pst files in My documents, so that I can back them up, is it
possible to path other things to my docs as well?
Iam using Outlook 2003 SP2.
if you have Contacts in the Contacts...email addresses don't pdf correctly in Publisher 2007
When I highlight someone's name and hyperlink it to an email address,
Publisher automatically adds mailto: at the beginning of the address and the
link works fine. But when I pdf it, whether I use the Publisher print as PDF,
or use the print menu and choose Adobe pdf, or go from Adobe and create a
pdf, the link on the email address ends up as mailto:mailto:emailaddress,
i.e., 2 mailto:'s show up. So then when you click on it, it opens up my email
program and puts one of "mailto:" in front of the address instead of just
putting the address there and the email won't ...