summarizing worksheet data
I want to create what should be a relatively simple spreadsheet with, say,
fifty worksheets in the workbook. Each worksheet would be identical in terms
of number of rows & columns, as well as the type of data those columns
contain. On the first worksheet, however, I'd like to be able to summarize
the cumulative totals of all the other sheets. So, for instance, if cell a20
on worksheets 2 through 50 contained a formula that calculated a sum of the
figures in a1:a19, I'd like to put a formula in a cell in the first
worksheet that will total the cell a20 in all the other w...Gathering and adding data from different rows
I run a report at work every day that tells me how many shares of a
company have been sold and from what year these shares were granted to
the participant. The year and the amount of shares are on different
lines. For instance, the year would be on D5 and the share amount would
be on D10. This is a daily function and the data changes every day. I am
required to manually scan these reports and add up the shares for each
year separately. I have to separate the amounts for 2003, 1997 and
I want to create a spreadsheeet that I can dump this data into and have
the spreadsheet find...macro to exit design mode
I am creating protected worksheets for users. When some users open them,
their Excel is already in Design Mode.
I need code to 'exit' design mode automatically. Can anyone advise please?
see if this helps:
With CommandBars("Exit Design Mode").Controls(1)
If .State = msoButtonDown Then .Execute
> I am creating protected worksheets for users. When some users open them,
> their Excel is already in Design Mode.
> I need code to 'exit...How do I insert a row ...
I want to insert a blank row every two rows, with the consideration
that my first row is the title.
the best way is to go from the end of the sheet back to the beginning
Lastrow = Range("A" & rows.count).end(xlup).row
for rowcount = Lastrow to 1 step -2
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=185766
[url="http://www.thecodecage.com/forumz/"]E...How to get every conytols
I'm working on Word 2007 and creating a form input user. I'm doing this for
a user that have Office 2003, so I have a compatibility mode template
document. It also include VBA code. What i need to do is to read every
existing text control. Theses controls are ActiveX.
I always have used:
For Each xControl In Controls
If TypeOf xControl Is TextBox Then
But now in Word 2007 in compatibility mode I do not know how to do it,
becuase there is not "Controls" in the ActiveDocument.
...Surpressing rows and columns with zero values in a pivot table
Let's say I want a report showing the number of times that my employees,
grouped in offices, markets and regions, makes a particular kind of error.
At the end of the month, I'd like to create a pivot table that says, "This
office had this number of errors of this type." I can create a pivot table
that does that, but it includes all the employees that have zero errors,
making the report unwieldy.
How can I format the pivot table to hide any row or column that has no value
Your issue is that it has a value. Something adding up to zero is just as
va...two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I
download transactions into Microsoft Money for either
account, all the transactions go to only one of the
accounts set up in Money. I was prompted for the first
download to our "main" account. Then when I went to
download for the other account, I wasn't prompted where to
download to... and all the transactions went into the
first account. Did that make sense?
My bank required we switch to Money from Quicken, so I am
new to this. Tried to search in users guide and online
for help. Help would be much appreciated - ...Access unwanted automatic data entry
when entering data in my Access table, if i use the tab ket to advance to the
next field, it often enters a number in that field. Why does it do that nad
how can I make it stop!
Are entering data direcctly into Access table? If so, stop doing that,
unless you only do it occasionally to repair data in database and you know
database design and manage it.
"Pat the biologist" <Pat the email@example.com> wrote in
> when entering data in my Access table, if i use the tab ket to advance to...Macro to pull every Nth row of data
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?
Source_Sheet = "Sheet1"
Target_Sheet = "Sheet2"
n = 5000 ' your last line of data on Sheet1
Target_Row = 1
For nCount = 1 To n Step 60
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
F...Example using MFC WinInet classes with POST of FILE and other INPUT data
I am looking for a good example for POSTing both FILE and other INPUT
data to a URL. I understand that the FILE must use a multi-part encoding
but I do not understand how the atcual file contents get POSTed or how
the INPUT data gets POSTed also. I am guessing the INPUT data can be
added as part of the query parameters to the URL.
...Q: how can I write this macro?
I am trying to copy-paste a part of an excel file based on the selected cell
and create another excel file and save it. Here is the scenario:
1. I clicked E18 (this is an example, it can be D18 or others) and run the
2. Macro should select E1-E18 (if D18, then D1-D18) and A1-B18 (so first two
columns and selected column up to the row the cell selected)
3. copy selected area
4. Create a file test.xls
5. paste it into first cell (I assume this will combine all three columns
together, so the data will be in A.B and C columns, not in A,B and E (or D))
6. save file
How can I do t...How to get userform Cancel button to invoke Exit Sub in calling macro?
I have a userform that is called from inside a sub in a module of a workbook. In order to keep most of the code inside the module, the userform only sets variables in the sub. Here is the code in the sub:
'create and initialize variables
Dim Bkp,XSub as Boolean
Dim BkpSfx as String
Bkp = True
XSub = False
'call options box
'Set main subroutine variables
Bkp = BackupOptionsBox.YesOption
BkpSfx = "_" & BackupOptionsBox.SfxBox.Value
...Change height of row
Excel seems to limit the height of the row to some presets. Is there a way
to set height of row to any height? Am trying to make spreadsheet line up
with a pre-printed form.
i think the limit is 1 pixel
"Pam MacT" <Pam MacT@discussions.microsoft.com> schrieb im Newsbeitrag
> Excel seems to limit the height of the row to some presets. Is there a
> to set height of row to any height? Am trying to make spreadsheet line up
> with a pre-printed form.
...Pivot table and it's macro
I am creating a table and from the table a pivot table using a macro. When I
run the macro the table gets created without a problem but when it goes to
create the pivot table I get an error and the debugger highlights this
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
:="Perf Tables!R2C8", TableName:="PivotTable1", DefaultVersion:= _
Can anyone see where the problem is happening?
My ...Pivot table row header
In a pivot table, I have 2 fields in the row section.
How can I have the value on the first fields to repeat in every row instead
of just showing once?
Try Debra Dalgleish's page at:
Fill Blank Cells
Fill Blank Cells Programmatically*
'by Dave Peterson 2004-01-06]
GMT+8, 1� 22' N 103� 45' E
"Jason" <Jason@discussions.microsoft.com> wrote in message
news:08549CBC-68A7-442E-BABF-D48B11E29479@microsoft.co...Averaging only some rows
I have an application where I want to calculate the average of several
fields, but only in those rows where the second column is blank. For
(A3)1 (A4)<blank> (A5)10 (A6)0
(B3)1 (B4)<blank> (B5)20 (B6)2
(C3)50 (C4)some text (C5)500 (C6)0
(D3) 1 (D4)<blank> (D5)15 (D6)4
should give me for a row of averages those calculated for all rows
except C - i.e., 1, 15, 2. I appreciate any suggestions on how I can
design this Excel 2007 solution. Thanks!
Do you mean
as an array formula, commit with Ctrl-Shift-Enter not just Enter
...Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front.
After every week, when new data is entered, I want the formula at the front
to use the cell with the new data instead of me having to change the existing
For example, my data looks like this:
A B C
I need a formula that will automatically detect the last number >0 in column
Thank you for your assistance!
so your main page formula might be something like:
=...Data Validation in XL2007 suddenly stops functioning
I created a workbook in XL2003 that included some named ranges on Sheet2 that
fed some data validation lists on Sheet1. Everything worked as expected. My
colleague opened up the workbook in XL2007 and used it several times, and
everything worked as expected.
Then she called me, saying that it was broken. Of the 6 named ranges and
related data validation columns on the other sheet, none of them were working
anymore. I went over to her PC, and checked everything I could think of- I
could set up working data validation test cells on Sheet2, but could not get
the ones on Sheet1 to...Excel macros page breaks but not on row 1 and column value in footer
Hi, I have seen some posts that are similar, but no one seems to have
the problem that I am having. I will post my macro in this, for anyone
that is interested.
My users get a csv file every month, and we have to clean it up. This
macro does that.
My last issues are this:
1) having the spreadsheet create page breaks whenever the value in
column B changes. Below is just that code.
col = 2
LastRw = ActiveSheet.UsedRange.Rows.Count
For X = 2 To LastRw
If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1")
ActiveWindow.SelectedSheets.HPageBreaks...Outlook 2007 Does not pull all POP mail emails
I have Outlook 2007 running on WinXP Professional. Outlook does not pull all my mail from my POP mail accounts and when my computer is off.
Even if I leave the computer on it still has issues with my emails not ending up in my Outlook. I have Outlook set to pull emails ever two minutes. I have no filters running either.
I have these large gaps where no email are downloaded at all. If I look on the Gmail website at my email I see several that have not been grabbed by Outlook. Any help would be appreciated
Submitted using http://www.outlookforums.com...Row Height variations from computer to computer on same workbook
I've been sent a workbook by a colleague and on his workbook, a specific row
has a height of 13.8 (23 pixels) and on my machine it is 16.5 (22 pixels).
Has anyone seen this before and what do we need to do to ensure that it's
consistent from machine to machine?
Thanks in advance,
Display Control Panel -> Settings -> Advanced -> General -> DPI setting to
Normal (96 DPI)
"Barb Reinhardt" wrote:
> I've been sent a workbook by a colleague and on his workbook, a specific row
>...Appending data in cells that utilize a Dropdown list.
I would like to append entries in cells that utilize a dropdown list.
Currently, when I select (in this case names) from my dropdown list I cannot
append them if I want to add more than one to a cell. If I select another
name from the dropdown, it erases the first entry. Is there some way to
append entries within cells that use this form of validation so that I can
enter more than one in a cell from the dd list?
Office XP Professional
Debra Dalgleish has an example workbook showing how to do this
http://www.contextures.com/excelfiles.html...Importing vcalendar data to multiple users
I'm running Exchange 5.5. Several times a year I have to create a large
group of users. I'd like to be able to pre-populate each user's
calendar with pertinent calendar events.
Is there a way to import this type of data in batch mode?
See if http://www.slipstick.com/calendar/holidays.htm#company helps.
> I'm running Exchange 5.5. Several times a year I have to create a
> large group of users. I'd like to be able to pre-populate each user's
> calendar with pertinent calendar events.
> Is there a way to import this type of data in batch...Pivot Table: showing data as % of subtotal
I have pivot table data in this format:
Name Type Amount
ABC 1 $100
ABC Total $1,000
DEF 1 $150
DEF Total $1,140
and so on
I want to see the data as follows:
Name Type Amount %
my excel skills is no more than having a certain cell multiplyin
i have 2 cells, with drop down box. box2 is dependant to selections o
after i chose the item in box2, i would like, let say, cell A3, A4, A
to display certain data about the item i selected in box2.
lets say, i selected "Peter" on box2, i would like A3 to display hi
"sex", A4 being "age", A5 is "height".
Message posted from http://www.ExcelForum.com
have a look at
for dependent data validation lis...