Sum of specific cell from several worksheets
I have a workbook with 12 worksheets, one for each month of the year. I also
want to add a "year to date" worksheet. Is there a simple way to take the
sum and/or average of a specific cell, over the 12 month period, and carry it
over to the same cell on the YTD worksheet?
Example: I have a "total pay" in cell A1, on each of the 12 worksheets. I
want the sum of all of the A1 cells to compute in cell A1 on the YTD
Ensure that the YTD sheet is placed outside of the group of 12 monthly
sheets. Ensure that the 1st month (eg: Jan 09) and last month...MultiList Select Box Search Form
I posted this question awhile ago, and never got a chance to get back to
check for any responses till now. I see Doug's (Thank you) and did what he
suggested, but got a kink while doing that. I also am not sure of if I
should post again, like I am now, or just reply to the old post. So
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or statement ...Shortcut for centring whatever is in a cell?
Is there any keyboard shortcut for centring whatever is inside a
cell/selection of cells? For instance, in Word, "Ctrl E" will centre text
on a line. Is there anything similar for Excel?
Don't think there's a built-in shortcut key,
but here's a play to set-up the centring for: CTRL+j
(Try on a spare copy/new book)
Press Alt+F11 (to go to VBE)
Click Insert > Module
Copy and paste the sub CentreAcrossCells
below into the whitespace on the right
If Selection.Cells.Rows.Count > 1 Then
...How do I copy a formula/paste w/o losing the cell content?
I have a formula such as "=sum(d34+d35)", I decided to move the location, but
when I move the location the formula is now "=sum(d31+d31). I would like to
move the first formula anywhere on the spreadsheet w/o it being changed. How
do you do that? Is it an absolute value or something like that?
Yes. The formula must be written as =sum($d$34+$d$35)
"CShannon" <CShannon@discussions.microsoft.com> wrote in message
> I ...Outlook Email Help: selecting Voting button option from excel VBA
I had a question about sending email from excel. I know we can set certain
properties of outlook from excel such as "Send Receipt" ect.. But can we set
an voting button option from excel?
I am able to send the voting buttons through excel by doing:
..VotingOptions = "Accept;Reject"
But There is an option "Have Replies Sent To" in Voting Buttons section
which I want checked everytime as I want the voting responses sent to the
person who requested the vote AND to one more person. Is there a way to do
this from excel?
To select "Hav...Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time
solution for this is to zoom in or out. This is problematic as 60% seems to
be the zoom that works most of the time but at this zoom level the cell
contents do not display. The time lost and the frustration that builds is
killing my productivity and office attitude. Please give all of us a permanet
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" butt...Making a command button work!!!!
I have a sheet with a combo boxes and a command button.
Is there a way that would make the command button work only when a
selection is made in a combo box
Eg combo box
Shaped finish with trim
So if the bottom select is made (shaped finish with trim) the command
button when press will work. BUT if any of the other selections have
been made pressing of the button will have no effect?
The combo and command button are on a spreadsheet from the forms
----------------------------------------------------------------------...Make Cells Diagonal
How do I make the top "label row" of the columns diagonal (45 degree angle)
instead of horizontal. I have seen it done but don't know how to do it.
...White Space in a Cell
I'm collating a lot of spreadsheets that many other
people have filled in onto one big spreadhseet template.
My problem is that there is a free text cell and, because people
dont know about the 'alt & enter' option, they've hit the space bar
many many times make it look like a new point starts on a new line.
In Word, you have the option of showing all the keyboard strokes
so you can tell what people have done - but I'm finding I'm having
to go into each free text cell and hit delete, then the cell magically
shifts up so that there is no white space.
Any...Change the file name in Macro
I created Macro that open several workbooks (actually 17 of them) at
the same time like this;
Workbooks.Open Filename:= _
"H:\Month End Work\FY 2009\02-09 RUI SALES\01 - Feb09 CFW.xls"
Workbooks.Open Filename:= _
"H:\Month End Work\FY 2009\02-09 RUI SALES\11 - Feb09 CFW.xls"
Workbooks.Open Filename:= _
"H:\Month End Work\FY 2009\02-09 RUI SALES\21 - Feb09 CFW.xls"
It works fine till I have to use it for the following month. I have to
go back to my Macro and change manually the directory from 02-09 to
03-09 and file name from Feb09 to Mar0...connecting cells with connector lines
Is it possible in Excel (2007) to draw a connector line (with or
without an arrow ...) between two cells in a worksheet, so that the
line-tips will follow their cells even when the latter are moved ?
On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote:
> Is it possible in Excel (2007) to draw a connector line (with or
> without an arrow ...) between two cells in a worksheet, so that the
> line-tips will follow their cells even when the latter are moved ?
The line will automatically a...How do I merge in a selected range of cells out of Excel?
copy / right click / insert copied cells
...Summing up content from hyperlink cells
Anyone have any idea on how I can make Excel sum up the displayed values of
several yperlink cells?
This is an EXCELLENT question !
I will use it next semester.
Let's say that in A1 thru A3 we have:
each of the cells displays 15
=SUM(A1:A3) shows 0
This is because the 15's are actually text values. They must be converted
to numbers. Use:
=SUM(--A1:A3) which must be entered as an array formula with
CNTRL-SHIFT-ENTER rather than just ENTER
Gary''s Student - gsnu200745
"Mort" wrote:...find the last occurance of a character in a cell
I can use the Find function to find the 1st occurance of a charater in
a cell. But, how to i find the last occurence of a character. in this
case, the i am looking for " ", or <space>. The length of the string
can vary, and the number of spaces can vary also.
"Get dog food 55"
There are 14 spaces (1+1+12). i am intersted in the location of the
last, or 14th, space, the one that precedes the number 5
Look in vba help index for INSTRREV
Microsoft MVP Excel
&...in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely
tell which cells are selected from those that are not. Can I change the
background color of cells that are selected?
Assuming Excel 2007 this is a known problem and as yet no patch for it.
I don't have Excel 2007 but I have read that playing with your Contrast can help
Gord Dibben MS Excel MVP
On Tue, 10 Jun 2008 12:51:00 -0700, flameretired
>When I select cells in Excel the shading is so light (blue) that I can barely
>tell whic...Passing Names to Macro's
How can I have a macro recognize a named range? E.g. ADDON=5 is defined
and named in a cell, and I want to reference it in the macro.
Dim sRangeName As String
sRangeName = ThisWorkbook.Names("ADDON").Value
MsgBox "ADDON Value" & sRangeName
Microsoft MVP - Excel
"Randy Numbers" <firstname.lastname@example.org> wrote in message
> How can I have a macro recognize a named range? E.g. ...delete a selection
how do i delete part of a chart to leave no unwanted space..
We are going to need more information.
What chart type?
Where is the space you want to remove?
Andy Pope, Microsoft MVP - Excel
"flirtacious" <email@example.com> wrote in message
> how do i delete part of a chart to leave no unwanted space..
On Nov 22, 3:10 pm, flirtacious
> how do i delete part of a chart to leave no unwanted...How to get all records in a Query even you select a record from a conbox box
Hello there well I have a big probem I am trying to create a query that I can
select a month using a combo box in a form and returns with all the record
and not ignoring the rest of the months for example If I have records for
July it will come only july's except the other month I prefer to get all the
months including july's I now is going to make duplicate in July but I don't
care that is what I need I used is null in the criteria and it works just
fine but I notice that if I Select the month of august all the records from
july's are gone only shows august anyway here is a ex...How do I get one cell to record the time another cell was changed.
I know there's a simple solution to this, but I'm not seeing it. How can I
set up a cell to update the time (or date) whenever another cell has data
entered into it; e.g. cell A1 has new info entered into it, and cell A2
automatically updates the time of that update. Thanks in advance.
"Reigning in Seattle" wrote:
> I know there's a simple solution to this, but I'm not seeing it. How can I
> set up a cell to update the time (or date) whenever another cell has data
> ente...right click new commands are missing
I had MS works and MS office installed. I needed HD space so I uninstalled
works and now my right click "new" commands for word and excel are missing.
My "new" powerpoint is still there. How do I get my "new" mouse commands
In article <E13C3B5C-F48E-4EDC-8E28-72AF78B7CE5B@microsoft.com>, Gene5133
> I had MS works and MS office installed. I needed HD space so I uninstalled
> works and now my right click "new" commands for word and excel are missing.
> My "new" powerpoint is still there. How do I g...running a macro on several sheets
I have a spreadsheet and I am trying to run a macro on all sheets.
Here is my code for the first sheet called U.S.
For counter = 5 To 15
cell = Worksheets("U.S.")Cells(counter, 38)
If cell = 0 Then
'Better than last year, better than plan'
ElseIf cell = 1 Then
= 4 'green'
'Better than last year, below plan'
ElseIf cell = 2 The...Workbook cells won't hold formatting in Office 2007
I have Office 2007 running in Vista Home Premium. My system got FUBARed and
had to be reloaded. Now my Excel workbooks won't keep the proper formatting
in cells. I have them formatted as general so that I can type in something
like 4 - 1. As soon as I do that it converts the formatting to Custom and
puts it in as a 1-Apr. If I try to reformat the cell as general I then get
39904. If I enter it as space 4 - 1 then it enters correctly.
How can I make the cell give me the correct information without putting a
space in front of it?
One way in to format the cell as Text B...Repeating instructions in a Macro.
I have a Macro that clears any data on different 31 tabs and, due to my
lack of knowledge, I do this one tab at a time. The 31 tabs represent the
maximum possible days in a month. Here's a sample of my Macro:
' Clear the data on tab 1.
'-----------------...macro at page break
Place macro so it prints starting 6 lines above each page break
Not sure what you're trying to do. Could you please give more details?
You want to print the macro 6 lines above each page break?
You want to print a header 6 lines above each page break?
You want to print automatically when you enter a page break?
More info might help.
**please reply to the newsgroup so others may benefit**
"Randy" <Randy@discussions.microsoft.com> wrote in message
news:C60553CF-D7E5-4968-B8B3-58C5A376E248@microsoft.com......VBA Excel Macro worked for 2-years now dosen't??
Is there a limit on how much code you can place in a VBA file?=A0
All works well except the macro I call "VacUsed"=A0
It is called from a couple of procedures I post the last
procedure=A0"ThisWorkBook" use to close and save the workbook.=A0
Private Sub Workbook_BeforeClose(Cancel As Boolean)=A0
=A0 =A0 Call FilterTestOff=A0
=A0 =A0 Call VacUsed=A0
=A0 =A0 Call DeleteMenu=A0
=A0 =A0 Call AllProtect=A0
=A0 =A0 Sheets("VacationAccrued").Activate=A0
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
=A0 =A0 C...