# Excel 2011 :: Automatically Calculate Running Subtotal By Category

Mar 27, 2013

I am using Excel 2011 for Mac.

I am creating a workbook with multiple worksheets, one overview sheet and then one sheet for each month of the year.

In the monthly sheets I have a Category column for which I have created a drop-down menu of expense categories. Then I have an Expense Amount column where I enter the amount spent.

The Category column will not necessarily remain sorted by category because I will be entering the expenses as they come up throughout the month so the categories will be all mixed up, unless I manually sort them.

What I want to do is somehow automatically calculate a running subtotal by Category (that will update with each new entry) and simultaneously automatically transfer the running subtotal for each Category to a specific cell on the overview sheet.

## Outline And Group And Subtotal Excel Data Automatically

May 19, 2014

Is there any way to outline excel data automatically

In the attached excel sheet : test outline.xls

I need automatically Because my data very large ...

## Excel 2010 :: Group Table By Category And Put Category In Its Own Row?

Sep 18, 2013

Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).

Category
Genre
Title
Author
Year

[Code]....

## Calculate Values Based On Category?

Jan 30, 2014

I have 2 tables:

IDTypeValue
1A3
2A3
3A4
4B4
5B3
6C2
7A4
8A3

And

TypeTotal
A
B
C

I need to "total" column to automatically display the sum of the values for the categories A, B and C. Is there a formula I can use for this?

## Excel 2013 :: Automatically Calculate Area Between Two Charts?

Dec 22, 2013

I have Excel 2013.

Find attached a sample fileDummy for area between.

charts.xlsx

## No Mode Function In Subtotal - How To Calculate

Jul 21, 2014

I'm trying to calculate the mode for a large data set, but there is no 'mode function' in subtotal. I need to find the mode for each change in day - without having to retype the function.

## Automatically Determine The Range For The Subtotal?

May 9, 2008

I have a table that needs to be subtotaled. The lines for the sub-total already exist and cannot be changed.

How can I insert a subtotal into a cell that will automatically determine the range for the subtotal? Each subtotal will range (single columns only) will be from 1 line to 100 lines. I will work from top to bottom down the page so is there a way to make it work like the Sigma summation sign that is on one of the toolbars?

## Calculate With Subtotal Functions And Write Value In Cell

Aug 22, 2014

i have wrote the following code snipped:

Sheets(2).Activate
Sheets(2).Range("D8").Value = Application.WorksheetFunction.Subtotal(109, Sheet(4).Range("BJ3:BJ" & b)) + Application.WorksheetFunction.Subtotal(109, Sheet(3).Range("BJ4:BJ" & a))

The debugger said: That "Sheet" is not allowed in the subtotal-function. because i would like to calculate two subtotals in two sheets an write the sum in another sheet

## Xlup And Calculate - Take Value Of Total And Subtract It From Subtotal

Dec 7, 2011

I have several columns of sub-total and total as shown in the screen print. I need some code that will allow me to take the value of total and subtract it from sub-total. The number of rows changes weekly so I cannot simply state =f10-f11

I thought xlup and offsetting would be a good way to do it, but I'd still need to assign a cell number.

## Make SUMIF Formula Calculate With SUBTOTAL Function

Feb 21, 2014

I'm trying to exclude cells that are filtered in a separate sheet.

The sheet I'm working with gathers information from this separate sheet using the following SUMIF formula:

=SUMIFS(Claims!\$H:\$H,Claims!\$G:\$G,\$A9,Claims!\$B:\$B,C\$6,Claims!\$E:\$E,\$B9)

This formula extrapolates information perfectly for me if I do not filter columns in the separate sheet with all of the information.

I know that SUBTOTAL functions can be used in a way to exclude hidden cells. Can I combine the SUBTOTAL function into my SUMIF function above to produce results that exclude filtered cells?

## Excel 2011 :: Text To Columns VBA (Mac)

Feb 27, 2012

My VBA which works fine on Excel 2007/2010 does not work on Excel for Mac 2011.

how I would convert this to work on Mac 2011?

Code:
Columns("AI:AI").Select
Selection.TextToColumns Destination:=Range("AI1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _

[Code]....

## Excel 2011 :: Save As XLS File VBA On Mac OS X?

Aug 15, 2013

I am running Excel 2011 on Mac OS X. I am trying to save a file (.xls) to a server (Autumn). I have tried several configurations to try to get the file saved properly. File name is "Testfile.xls"

1. folderpath = "Autumn:Tim:TestFolder:" & filename --Activeworkbook.Saveas filename:=folderpath

2. folderpath = "Tim:TestFolder:" & filename Activeworkbook.Saveas filename:=folderpath

3 Activeworkbook.Saveas folderpath

When I try this, I get an Error 1004 indicating that it cannot find the file. Do I need to eliminate the .xls and then specify that when I do the saveas?

## Excel 2011 :: Copying Calculated Hyperlink

Sep 26, 2013

For Mac Excel 2011, though I think my question would apply to any modern version of Excel.

This is a little odd. I have a sheet where, for various reasons (see below ### if interested), I need to copy a hyperlink, replace a substring, and then return the new hyperlink (with old friendly text) to the original cell. (I actually have to do this with an arbitrarily-long column of links.) This was my plan of attack:

* Pull the URL from the hyperlink cell.
* Use FIND and RIGHT to strip out what I need and replace it
* Use HYPERLINK to build a new cell with the original friendly text and the new hyperlink.
* (Manually) copy this new link (really, a column of links) back to the original cell (really, column of cells).

Here's where it breaks down:

a) If I simply cut-and-paste, the formula used to pull the hyperlink becomes circular (as do several others), and Excel complains.
b) If I cut-and-paste-special (values only), I get just the friendly text, no link info. Likewise if I try to reference the "fixed" hyperlink.

The erroneous hyperlinks are in column K. My calculated correct hyperlinks are in column S. (There are similar errors and fixes in L and T, respectively.)

## Excel 2011 :: Loop Through All CSV Files In A Folder

Nov 23, 2011

I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...

Code:
Sub RunCodeOnAllFiles()
Dim lCount As Long
Dim MyDir as string

[Code]....

For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.

Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?

## Excel 2011 :: Creating Button On Spreadsheet?

Dec 12, 2011

In MS Excel 2011 for Macintosh can I assign a macro to an object, such that if the user clicks the object it runs the macro? That is creating a button on the spreadsheet that will execute a macro.

## Excel 2011 :: Drop Down Menu With Results?

Jan 25, 2012

So i am working on an attendance tracker here. I already have aligned on a separate tab all of the works aligned next to their manager

EX
Column A: Column B:
Worker Name Manager

What i would like to do is create a drop down menu with all of the Managers name listed to where someone can select that manager and when they do all of the workers names show up in Column A and then that Manager Listed next to them.

Is there a way to do this... I am using office 2011 btw. Once when i get this last part completed with this drop down menu i should be complete!

## Excel 2011 :: Column And Row Headings Missing (Mac)

Jun 5, 2012

I have a friend using Excel for Macs 2011 and the column and row headings (ABC & 123) are not showing on the spreadsheets on the screen. How to turn them on. I fgure they got turned off somehow?????

## Excel 2011 :: Exact Some Data From A File

Sep 15, 2012

I use mac and I have "Excel for Mac 2011".

I need to exact some data from a file. I can open the file, but can't see any data of it. I am afraid I need to do some setting so that I can see the data, but I don't know what should I do

## Excel 2011 :: Using Macros To Export / Archive

May 2, 2013

I'm using Excel 2011 on a MacBook Pro. Here's my situation:

I'm running a monthly budget report that's got three spreadsheets within the workbook. Spreadsheet #1 is summary sheet with a few pivot tables. Spreadsheet #2 is the data sheet, where all of the information is entered, and is what feeds the pivot tables on Spreadsheet #1. Spreadsheet #3 is just a list page that populates the drop-down list on the second sheet.

As this is a monthly budget report, I'd like to find some way to archive the first spreadsheet (the summary sheet) every month. Ideally, I'd like to be able to create a macro-driven button that, when clicked, does the following actions:

1) Creates a new spreadsheet, with each click, named for the current month and year.
2) Archives the exact contents of Spreadsheet #1 by making an exact copy of its current contents, and places this copy into the newly created spreadsheet.

To summarize, I'm trying to use a button that, when clicked, runs a macro that archives the current month's data into a new spreadsheet, and then clears the data from Sheets #1 and #2 so that I can start each month with a clean slate. If there's a way to only clear the data from certain cell ranges (as opposed to a complete wipe of the sheet), that would be even better.

## Excel 2011 :: Insert Calendar Dropdown For Mac

Dec 5, 2013

How to insert a Calendar Dropdown for Mac Excel 2011? Also, do I have to readjust this (for lack-of-a-better-word) 'add-in' everytime the calendar year changes? This will be used for a payroll template.

## Excel 2011 :: Looking For PV Formula That Accommodates Delay Period

Aug 2, 2014

My software is Excel for Mac 2011 version 14.4.3 (140616).

I am using the present value formula =PV(rate, nper, pmt, [fv], [type]), with the pmt expressed as a negative number, to determine the present value of a lump sum that would generate specific, equal, regular annual payments, exhausting itself in the process. I fill it in this way: rate is an annual rate, nper is the number of years the payments run, pmt is the annual amount paid each year, fv =0, type =1

I would like to be able to have a similar formula in Excel that would do the same thing except that it would take into account a time period where no payment is made, ie a "delay".

For example using the PV formula I know that if I have \$49,019.96 invested at 1%, it will generate \$10,000 per year for 5 years, leaving a net investment of 0. I would like to know the formula that would tell me how much I would need to invest at 1% to yield \$10,000 per year for 5 years, again exhausting itself completely in the process, with the first \$10,000 payment starting 3 years from now. I know it would be less because I would have the full amount earning interest for 3 years at 1% before any payments need to be made.

## Excel 2011 :: Multiple Dependent Dropdown Lists

Feb 26, 2013

I am working on a spreadsheet for my Building company. I'm building a tool to price for Fascia, Soffits and Cladding.

I'm using Macbook Pro Microsoft Office for Mac 2011

The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.

The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.

Example:
Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33
Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"

So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C

The Value column would then show a value based on the options selected.

## Excel 2011 :: Shuffle Row Position After Data Refresh?

Jul 12, 2013

I am using Excel 2011 and we have a link data source to the outside SQL Server.

For the 1st step, I linked to the Database and retrieve the data.

P1.png

2nd step. Sort the data by using the column "Description" Descending. Add another column to the table and put the 1, 2, 3, etc in that column.

P2.png

3rd step. I clicked on "Refresh" and the new column values are not consistent. I mean they are moving. So, I added another row in the database to test and it becomes very obvious. It can be clearly seen at the following image. The value of "A" should always be "10", but it has been changed to "1" after the refresh.

P3.png

how I could retain the position of another column after the data refresh?

## Excel 2011 :: How To Find ColorA And Replace It With ColorB

Aug 24, 2013

I have Excel MAC 2011 and my spreadsheet was created with multiple cell colors randomly throughout the spreadsheet.

I need to change all the royal blue colored cells to "no color" without changing the data contained in the cell. For example; Cells A5:A38 are blue, A39 is green, A40:A50 blue, A51:60 green, ... Cells B5:B15 are grey, B16:B20 are blue, B21 is yellow...

I can't find the "Advanced Options" for Find and Replace that might have allowed me to specify a color change.

## Excel 2011 :: Pasting Data In Workable Format

Sep 14, 2013

I have switched from an ageing Mac Powerbook, Excel 2004 for Mac, and an out-of-date version of Firefox to an IMac with Excel 2011 for Mac and Safari as a browser. The only reason why I mention this is because I don't know if it was easier to do this on an older platform (one of those bizzare situations where going forwards is actually a step backwards).

In short I like doing things with Sports data in Excel. Usually I would hit my favourite website for pulling sports data (which incidentally I don't know if this has changed as I've been too busy to do much of this kind of thing for about 4 months - or if it is just a different format on a newer machine/different browser);

[URL] ....

The link should be showing NFL results for this year so far (I like the site as it has a handy amount of other sports etc.).

I would usually then go edit/paste special - unicode text and then hit the paste options, use the text import wizard, go delimited, tabs - and usually everything would come in on a row and then I could do what I need to do with it (e.g. use Excel's text functions to get the data - taking spaces out for example - to where I want it to do other things).

The problem I seem to be having now is that when I do the above - things no longer seem to come in on the same row (which makes life difficult for doing things easily with the data). Once things end up on different rows it becomes a pain to do basic things.

In short is there a way round this (or do I need to find another source for my data with tables laid out in a more friendly way to "borrow" data)?

## Excel 2011 :: VBA Code - Command Button Location

Aug 16, 2014

I am building a code based on a command button in a row, which will check is a worksheet exists (message), if not create a worksheet from a template (from another worksheet specific to a on a cell value in the same row), rename the worksheet based on a cell value in the same row.

I am having some success for each task with exception to relative cell values .....

As the code will be specific to the row (one button per row) Questions:

how to i determine the location of the button that is clicked? (I assume once this is established i can use to pull values in the same sheet on certain columns....?)

## Excel 2011 :: Data Validation Dropdown List?

Jan 2, 2012

I'm using Microsoft Excel for Mac 2011. I'm creating a drop down box from List within Data Validation. It's only letting my create a list of 17 names. My longest list is 63 names. What do I need to do to create a list that will support that?

## Excel 2011 :: Macro To Clear And Lock Cell

Jan 30, 2012

I'm using Excel 2011 for MAC.

What I need is to clear and lock some cells based on a drop down list selection.

The case is like this:

In Cell [E14] is a drop down list : 0,1,2 or 3

Cells: (D19:E19), (D21:E21), (D23,E23) Merged are PART DESCRIPTION

Cells: [G19], [G21], [G23] are COST

So I need when 0 is selected in the dropdown list, all of the above cells are cleared and no input is permitted, Cells locked.

If 1 is selected, just the first row is allowed for data entry and the other 2 are cleared and locked as this values populate another worksheet for calculations.

So far I did this code:

Private Sub Worksheet_Change(ByVal Target As Range)

If [E14] = "0" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("D19:E19").Locked = True
ActiveSheet.Range("G19").Locked = True
ActiveSheet.Range("D21:E21").Locked = True
ActiveSheet.Range("G21").Locked = True
ActiveSheet.Range("D23:E23").Locked = True

[code]...

It works fine to lock the cells, but I can not fined the way to clear the values previously stored before locking the cells.

## Excel 2011 :: Sorting Blank Cells To The Top Of List

Feb 5, 2013

I am trying to sort a list of business development leads. When a lead is dead I enter the date in the column entitled "Closed". Therefore, the Closed cell for active leads is blank. Whilst I still wish to retain the information of dead leads, when sorting I would like them listed at the bottom of the list. However, whenever I sort on the Closed column, whether using newest to oldest or oldest to newest, the blank cells are always at the bottom, instead of the top.

How to get the sorted blank cells to the top?

I'm using Excel 2011 on Mac OS X 10.7.

## Excel 2011 :: Inserting Rows In Checkbook Register

Apr 3, 2013

I've been looking for a free checkbook register template that will automatically calculate the balance as you add or subtract various amounts.

I found one which seemed to be just what I wanted but once I had reached the end of the rows for the worksheet and tried to add more rows the automatic feature quit working.

I found another Checkbook Register which seemed even better than the first one as it had a notation at the end of the rows.

It said to " Insert new rows above this point" and it had the last row greyed out.

I tried adding a new row and tested it out by adding some amount but it did not continue with the automatic calculation either.

I'm using MS Excel for Mac 2011