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)....Help, Excel Files Being Wiped Out: Data Lost or Corrupted
When I try to open certain Excel files, I get this message:
Unable to read file. [OK]
Unable to read file. [OK]
Errors were detected ... MS was able to open the file by making the
repairs below. Save the file to make the repairs permanent.
Damage to the file so extensive that repairs were not possible. Excel
attempted to recover your formulaes and values, but data may have been
lost or corrupted.
The resulting file is devoid of any formatting, and some formulaes are
gone. It's really no use since it takes so much time to reconstruct
these files. I've been saving 4 different v...'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...Why subscript out of range?
why do I get a run-time error number 9 'subscript out of range' message on
running this code?
Dim vaTest as variant
Dim i as integer
vaTest = Range("a1:a10").value
For i = 1 to Ubound(vaTest)
If vaTest(i) = "True" then Msgbox "True"
...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
...How do i get my data to be on the x axis of a chart?
Chart menu > Source Data > Series tab. Select a series, then use the X Values (or
Categories, depending on chart type) edit box to select your range of X values.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
...Copying Selective Data Between Worksheets
I am setting up a simple accounts workbook
I have one sheet with 11 column headings and as each transaction is
allocated to a department
I then have a worksheet for each department -
I am trying to get it so that when an entry is made in the main sheet it
also appears in the department sheet also
I have used the following formulas but now the main sheet has passed Row 31
the data is not being picked up on the dpartment sheets
In Cell B6 on the dept sheet
In Cell C6 and then copied across the Columns
=IF(ROWS(...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" <email@example.com> 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...I'm trying to delete a huge amount of data and says something
Hello. I have a table with a huge amout of data. The data comes from an ODBC
table and is copy to a local table.
When I have to delete the data of the local table it returns me a message
sauing that I have no enough disk free space to cancel the operation. For me
is ok, but I have 22gb of free memory.
Is it normal this messages?
How are you trying to delete the records? By selecting in the table and then
press delete? If so try to create a delete query that should work.
> Hello. I have a table with a huge amo...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...Need control to not display its data under certain circumstances
I have a form that saves "99" for a particular field if that field is
left blank (which for us means that we tried, but weren't able to
collect that information).
However, when I load that form for a pre-existing record, I would like
these fields to not show the 99, but rather to appear empty. Is there
a way to do that through the control properties? Meaning, is there a
way to not display the saved value for a particular control if the
data saved for it was a 99?
Thanks for your help.
Try conditional formatting.
Create a condition that says, if the value of the control = 99, ...Serial data
I need to input serial BCD data to microsoft access via a com port. I thought
of running a scipt to control the flow of data but am unsure where to put the
data once I have loaded it and how to read that data into microsoft access.
Has any one done this before or have any ideas?
Pete G <PeteG@discussions.microsoft.com> wrote:
>I need to input serial BCD data to microsoft access via a com port. I thought
>of running a scipt to control the flow of data but am unsure where to put the
>data once I have loaded it and how to read that data into microsoft access.
I have the Table
CodSemester Lookup (String)
CodAnimal Lookup (String)
AnimalUnit must have the values indicated below:
CodAnimal Animal Unit
I would like to be able that when I select a value in CodAnimal the
respective value in AnimalUnit field will be filled. How can I do this?
Many t...problems installing outlook client: The Microsoft CRM Data File, M
Problem trying to install Microsoft CRM 3 Outlook desktop client on a PC
running windows XP service Pack 2 and Outlook 2003 service Pack 2 connected
to exchange. Originally downloaded ISO installation file from Microsoft
downloads, and burnt CD. Installation failed, it seems the installation CD
was corrupt. We then found our original installation CD, which when we
installed it, it came up with two messages: -
Microsoft Outlook was not initialized. Please run Outlook and configure your
---- Outlook is installed and running, there's no problem with this so I do
not unde...How do I import CRM 1.2 live data into CRM 1.2 test server
I was told to use the CRM redeployment tools for this but I got stuck.
I ran the "user data export wizard" on my current CRM server but it failed to
create the XML documents.
I got the error:
The wizard could not find the Microsoft CRM originizational unit (OU) that
was specified in the database. Make sure the OU exists in Active Directory.
I know the OU exsists and I can see it from the CRM machine. I am logging in
as domain admin.
What do you think I have missed?
...Selection of date range
I have a small table that covers an entire year of sales
I need to make a way to select he date range, and sales Agent type and
display the pounds purchased for that week.
So I'd like a selection box "enter Date range" (weekly) & type of
Customer Affiliate Domestic, Affiliate international, Domestic
Customers, International Customers.
Should be simple, eh?
Just add a little form with 2 textboxes: txtStart and txtEnd
Then use the following query criteria:
For the dates:
Between Forms!FormName!txtStart And Forms!FormName!txtEnd...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...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 ...Protecting sheet disables import of extetrnal data in Excel 2003
I need to protect the sheet and not disable ability to import of external
data in excel 2003?
Even when I open a sheet that worked while being protected in previous Excel
versions, once I protect it in Excel 2003 the auto update feature for
returning external data is disabled.
...how to setup only one chart and change data from a drop down
How can I make use of only one chart on a sheet that can be changed
dynamicaly by using a drop down to display a new chart.
Example data is as follows:
Time period is months from July to june
Actual monthly value.
jul aug sep oct nov dec jan feb mar apr may jun
Benchmark 20 20 20 20 20 20 20 20 20 20 20 20
Threshold 10 10 10 10 10 10 10 10 10 10 10 10
actual1 18 23 14 15
Benchmark 100 100 100 100 100 100 100 100 100 100 100 100
Threshold 78 78 78 78 78 78 78 78 78 78 78 78
actual2 67 87 88 78
Benchmark 0.012 0.012 0.012 0.012 0.012 0.012 0.012 0.012...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...how to set up a simple variable in Excel, but cell location independent
Can you please show me how to set up a simple variable in Excel, but this
variable must be cell location independent. Because this worksheet will be
sorted and cell location re-arranged and moved. That mean for example,
In one worksheet, I set variable numb = 5
other cell at different location value display will have function such as
numb*2 = 5*2 = 10
Then, other time, I set this same variable numb = 12
then, the call value will display
numb*2 = 12*2 = 24
You might try a named formula.
From the Menu Bar,
<Insert> <Name> <Define>
In the "Names In Workboo...Limit change to specific ranges in VBA routine
Bob Phillips had a wonderful VBA routine which I have
adopted and modified, and it works fine. However, I want
to limit the range of the routine to only work if the
change is made in the following ranges (D8:D27; D29:D44;
D46:D68) rather than to occur anywhere in column D (which
is column = 4 in the routine).
I am using Excel 2000
Private Sub Worksheet_SelectionChange(ByVal Target As
On Error Resume Next
If .Column = 4 Then
If .Value = "a" Then
.Value = "q"
.Font.Name = "M...Problem displaying Cell Shading color on Sony VAIO
When I open Excel documents that have Cell Shading the
colors are not visible on the LCD screen. When I use the
Print Preview I see the colors.
I'd like to be able to see the colors while I'm editing
on my Laptop. The background colors are visible on my
I have tried several different screen modes with negative
results. I've also download and installed all the latest
service packs from Microsoft.
MS Article found at
OFF: Changes to Fill Color and Fill Pattern Are Not Displa...Converting custom cell properties to text
I have a column of cells with telephone numbers in it and I wish to convert
- or copy and paste these cells - into text format.
For example, I have a telephone number as 083270000 and the cell properties
(Format > Cells > Category) is listed as "Custom" and the "Type" is listed as
When I conduct a copy and paste, I lose the preceding zero before the eight.
But I need this to remain and I need all the cells in "Text" format!
Any ideas on how to do this?
Format the column as text-go into Format>cells>cu...