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...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
...Keeping user specified Opportunities Confidential
We have a client who by default wants an open security where all
opportunities can be Read (not edited) by others. However, there are
instances where an opportunity must be denoted as "Confidential" where no one
except specified people may have access to this record(s).
Any ideas on how I can accomplish this?
The use of Teams is out because this by default All users can Read All
There is a capability using the SDK to make records "Private" to the owner;
such records can then be ...Outlook keep on asking to enter username/password
In one of our PCs, outlook keep on asking to enter username/password.
but after I entered the right username/password, this window popup
so I created new profile in the outlook and even to another PC. but all
the time, this user still have the same problem.
So I guess that's the server-end problem. while I reset the password in
the server, but it did not work.
I have to clue about this problme now. is there any other way to
troubleshoot this problem?
thanks in advance
What format are you using for the username?
You might try the full email address as the username....keep pivotchart formatting
Is it possible to keep all the formatting in a pivotchart after i refresh
Although i check the preserve formatting option, every time i refresh tha
data, i loose all the changes i made in the chart, and it allways goes back
to the default excel formatting.
You may set the property "HasAutoFormat" to False.
The code would be like the following:
ActiveSheet.PivotTables("PivotTable1").HasAutoFormat = False.
"Pmxgs" <email@example.com> wrote in message
...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
...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...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...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...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...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" <firstname.lastname@example.org> wrote in message news:email@example.com...
> 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...How to calculate (generate) a cell reference
I need a way to generate a cell address (row,column) from the value in
another cell and be able to use the contents of that in a calculation.
Here's my situation:
I have a table of mileage readings for my car taken at odd intervals.
Note that the "[Row]" column is the actual row number in the
spreadsheet and not part of the data. It is included because I want to
reference it later.
[Row] Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
13 3/13/05 48,041 1,141 50 1.64 0.14 23 695 8,335
14 7/10/05 50,922 2,881 119 3.91 0.33 24 737 8,843
15 10/29/0...hyperlink to a cell: adjusting for changes?
I recently discovered that Excel can hyperlink to another cell in the
same workbook, which looks to be very useful. In this re, ...
Using (row,col) designation, the link's 'reference' (using the word
loosly) appears to be absolute: If I set it to 'A31', and then
insert/delete row 29, the link will still be to 'A31' - as if I'd spec'd
$A$31, in most other contexts.
I know I can get around this by referencing a 'named' target cell. This
is OK, ... but, a litte more effort.
So, just to be sure I'm not missing someth...Alert If Cell Value Is a Certain Value
How can U set up an alert to format cells with a pink fill colour if say A1
ever has the value eg 1000 in it?
This could be used to Highlight the Name and address of someone (with a pink
fill) that has ever owed £1000 even if they now do not.
In other words the name and address of that person stays in pink fill if
ever they have owed £1000.
Thanks for any help.
turn cells get Excel record that a cell has ever been a certain value
Look up 'Highlight cells that meet specific conditions' in the Answer
wizard in...Return range of cell values based on current date
I have a spreadsheet containing daily sales data for the month. I want
today's sales data to display on a different worksheet to save the hassle of
someone scanning through an entire month of data.
Is this possible and what function do I use to achieve this?
WHY NOT ADD A WORKBOOK?
Providing a workbook will not only get you your answer quicker but will
better illustrate your problem, usually when we can see your data (-it
can be dummy data but must be of the same type-) and your structure it
is far easier for us to give you a tailored, workable answer to ...How do I keep my excel files in Excel 2003, not auto 2007
I loaded a trial version of excel 2007. I want to continue using 2003
instead of buying the full version, but my old spreadsheets keep
automatically changing to 2007.
Delete the trial version. Control Panel > Unistall Programs > Uninstall
XL2007 Trial Version.
"DorothyL" <DorothyL@discussions.microsoft.com> wrote in message
>I loaded a trial version of excel 2007. I want to continue using 2003
> instead of buying the full version, but my old spreadsheets keep
> automatic...Server upgrade (hardware, O/S, but keep 5.5)
I'm planning an upgrade to our old tired Exchange server. We have 60
users, using Outlook 2000 and Outlook 2003.
Currently we have Exchange 5.5 on an old PII NT 4 server. And OWA on a
seperate IIS server.
I'm planning to upgrade to a single CPU Dell server,
with SCSI RAID.
At the same time I thought I'd implement Active Directory,
as we have an existing Windows 2000 file/print server as a member server.
I don't really want to upgrade from Exchange 5.5 at this time (cost, and
we're not sure we will stay with Exchange long term) so I assume I can't
run Exchange 5.5 on ...Absolute Worksheet reference number
When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow ...Macro to coppy cells to certain rows depending on value in cell
I want his macro to after it have inserted the colmns and added the formula to
1. copy range A1 to E1 to every row where the word "Header" is in colmn F.
2. Then copy paste the whole sheet as values.
3. Then the range now standing left of "header" must be copied to the empy
cells beneath each heading.
a b c d e
1)12/12/2005 F001 SAO3 1 CCE Header
...define a cell with the value of anothe cell
I'm very new to excel and i think i just don't understand a basic function
but i couldn't find it under the help menu.
how can you define a cell using a letter and then a value of another cell?
here is an example
my guess was =D(W15) which i would like to equal 10, but i get a name error
Todd Duncombe <Todd Duncombe@discussions.microsoft.com> wrote:
> I'm very new to excel and i think i just don't understand a basic
> function but i couldn't find it under the help me...