Excel 2010 :: Sum Multiple Columns To Get YTD Amount?

Jun 16, 2012

I have to construct a financial model for Senior Executives to show year to date spent amounts. I have my worksheet as follows:-

Cell A2,A3,A4.. to A100 has - Account Numbers (Ex. A/c. 4100..)
Cell B1, C1, D1.....has Jan2011,Feb2011,Mar2011.......and so on till Dec2011.
Cell B2 onwards, down and to right, all spent amounts by month

What I need is a formula to get year to date number, which will change to Executives requirement.

Cell-ABCDE
1Jan2011Feb2011Mar2011Apr2011
24100100100100100
34101200200200200
44102300300300300

Year to dateMar 2011( Months will be changed)
Account 4101( Accounts will be changed)
Amount should be 600 What Formula ?

My excel version is 2010.

View 7 Replies


ADVERTISEMENT

Excel 2010 :: Lines Count - How To Expand Amount Of Columns In One Spreadsheet

Dec 27, 2011

I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.

If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.

View 4 Replies View Related

Excel 2010 :: Filtering Multiple Columns (OR)

Jul 18, 2012

I have a worksheet with

Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)

There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.

I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.

For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.

I am using Excel 2010.

Name
Primary
Secondary
Tertiary

Person 1
x

[Code] .........

View 3 Replies View Related

Excel 2010 :: Selecting Corresponding Values From Multiple Columns

Feb 18, 2014

I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'

I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'

See the attached image to make it clearer..

Excel2010

Excelhelp.jpg

View 3 Replies View Related

Excel 2010 :: Sort Multiple Columns Simultaneously?

Jan 3, 2012

I am trying to sort each "Pct" column in descending order. Of course, I can do this manually, but I have over 100 to do, so I'd like to know how I can automate this (of course, the two columns to the left of "Pct" must move along with it).

delete
EFGHIJKLMNOPQ8BallFrqPct
BallFrqPct

[Code]....

View 2 Replies View Related

Excel 2010 :: Comparing Multiple Columns Of Data (Last Name / First Name)

Aug 8, 2012

I'm using Excel 2010 on Windows 7.

I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.

Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.

It would look something like this

Sheet1
Lastname
Firstname
Device
Johnson

[Code]...

So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:

1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.

View 6 Replies View Related

Excel 2010 :: Converting Multiple Columns Into Rows?

Aug 24, 2012

I am trying to convert

Excel 2010
A
B
C
D

[Code].....

View 9 Replies View Related

Excel 2010 :: Creating Scatter From Multiple Columns Using Ctrl

Jun 12, 2014

I am trying to create a scatterplot from some data I have. I attempt this by selecting multiple columns using Ctrl and clicking on the letters above. This results in it plotting only the first selected column on the Y axis with only 1 2 3... on the X axis.

When I however select multiple columns by clicking on a column and dragging to more columns it does plot as I would expect with the first column as X values and the rest Y values.

I know it is possible to create a graph and add data manually but this is way slower and I think that it should be able to be done differently.

I'm using excel 2010.

View 3 Replies View Related

Excel 2010 :: How To Take Data From Multiple Columns And Add Them Onto Single Column

Mar 8, 2013

I have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.

Data Looks like:

54654
31233
42343
51234

66968
43252
54657
63253

[code]....

Would like to look like:

54654

66968

79282

91596

68185

31233

[code]....

View 5 Replies View Related

Excel 2010 :: Formula To Display 1 If 2 Or More Cells Are Non-zero Amount?

Mar 26, 2014

The situation is that I have 3 interest income from 3 different companies. Column B acts as a helper column to determine if a partner receives just one interest income or receives multiple interest income. If they receive multiple interest income, I want column B to display a 1 and if not make it blank. If you notice partners 5, 9, and 16 contain two or more interest incomes (Columns C - E). All the cells are have either an amount (positive or negative) or a zero. There are no blank cells. Columns (F - H) contains the interest income amount if only that partner receives just one interest income. So for those partners they should not display any of their amounts. The reason is because I am using a word template and currently have to use multiple templates ( 3 in total) to display correctly for each partner. I got this to work with a word macro that will collapse all non blank columns in a particular table. This might be on a ongoing project for me as I do have more logic to solve.

Excel 2010
A
B
C

[Code]....

View 2 Replies View Related

Excel 2010 :: Count Consecutive Numbers Over Multiple Columns / Rows

May 27, 2014

I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:

A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2

I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:

Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)

Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0

Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0

and so on....

View 9 Replies View Related

Excel 2010 :: Convert Multiple Columns To Rows Depending On Input Value

Jul 6, 2014

I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:

A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910

If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result

A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..

It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.

View 6 Replies View Related

Excel 2010 :: Using Multiple Checkboxes To Hide And Show Specific Columns

May 12, 2014

Basically I have a big table containing several columns for the different quarters. (Each quarter has several columns which are not all next to each other)

I would like to use 4 checkboxes which the user can select the show or hide the columns for that quarter. E.g. if Q1 and Q3 are checked only those are visible)

(if quarter 1 clicked than all columns are visible if unclicked hidden).

The code I have written workes well for the first checkbox (Q1), but for the other quarters (Q2, Q3 and Q4) the columns get hidden when checked (which should be the other way round) and don't get unhidden when unchecked.

Sub CheckboxQone()
If Range("$A$1").Value = True Then
Call showQ1
Else: Range("$A$1").Value = False
Call hideQ1

[Code] ...........

View 2 Replies View Related

Excel 2010 :: Chart To Show All Of Amount Values For Each Size Value

Dec 12, 2011

I am trying to create a chart that would show all of the Amount values for each Size value that is in the example data below. The Size column has fixed values. In my example the values are: .5, 1, 2, 5, 8, 13 and 21. The Amount column data is variable. Any of the static Size values can be associated with many Amount values. I was attempting to have the static Size values on the X (horizontal) axis and the Amount values on the Y (vertical) axis. I would like to have all of the Amount values for each Size value be shown directly above the corresponding size value, in one vertical line. See example below.

I an creating this type of chart in Excel 2010.

Note, I am still new to this forum and don't know how to post pictures or Excel data into my posts. I am also using dots (periods) to provide separation in my examples.

Amount...Size
58.5.......21
1........... 0.5
2........... 1
1.5.........1
27.5.......13
12.5.......5
2............1
3.5.........2
17.5.......8
4........... 2
48..........21
0.5.........0.5
15..........5
11..........2

Chart that I am trying to create. The chart example is not to scale.

...........60.|
...............|..........................................................................*(21,58.5)
Amount.50.|
...............|..........................................................................*(21,48)
...........40.|

[Code] ........

View 2 Replies View Related

Excel 2010 :: Macros Taking Inordinate Amount Of Time To Run?

Mar 13, 2013

I have a 5K Excel 2010 workbook with VBA code that until yesterday, took about 30 sec. to run.

Today, after installation of WIndows7 Home Edition automatic updates, the running time has increased to about 4 min.

View 6 Replies View Related

Excel 2010 :: VBA Code To Search Multiple Columns And Delete Any Duplicate Cells (not Rows)

Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

View 14 Replies View Related

Excel 2010 :: Sorting On Multiple Columns - Method Range Of Object Global Failed

Feb 25, 2014

I need a macro to sort on five columns (Column A, L, P, X, and Y) out of about 33 columns. Sort is all lowest to highest.

The code I have so far is:

Sub Macro1()
Range("A2:AG").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("L2") _
, Order2:=xlAscending, Key3:=Range("P2") _
, Order3:=xlAscending, Key4:=Range("X2") _
, Order4:=xlAscending, Key5:=Range("Y2") _
, Order5:=xlAscending
End Sub

However, I get an error "runtime error 1004 - method range of object global failed".

View 1 Replies View Related

Excel 2010 :: Create Pivot Table To Gather Cumulative Amount From Data Sources

Nov 30, 2011

I'm trying to create a pivot in 2010, which gathers a cumulative amount from data sources within two different worksheets.

View 2 Replies View Related

Excel 2010 :: Macro To Save Multiple Sheets To Multiple PDF With Cell Value As Filename

May 6, 2014

I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.

I am using Excel 2010.

View 5 Replies View Related

Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

Aug 5, 2012

I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))

but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.

View 1 Replies View Related

Excel 2010 :: Multiple Entries In One Cell That Need To Be Spread Across Multiple Rows

Jun 17, 2014

I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.

BEFORE MACRO

ID
AREA
TYPE
CLASS
QTY
IDS

1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118

[Code] ........

AFTER MACRO

ID
AREA
TYPE
CLASS
QTY
IDS

1
COAL
TYPE9917312
CLASS881345
2
FILER756911

[Code] ......

What the MACRO would look like? This is for Excel 2010.

View 3 Replies View Related

Excel 2010 :: Create Macro Button To Clear Multiple Cells On Multiple Worksheets?

Jul 2, 2014

I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.

On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells

B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42

On all other 'Caravan sheets' I want to clear the contents of cells

D4 & D5
E4, E5, E22, E23, E41 & E42

It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead

View 8 Replies View Related

Excel 2010 :: Lookup Multiple Criteria Across Multiple Sheets?

May 28, 2014

I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..

What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)

View 2 Replies View Related

Excel 2010 :: Move Part Columns Of Data From 6 Columns To Form 1 Large Column In Column A?

Jan 31, 2013

I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.

I need to move part columns of data from 6 columns to form 1 large column in column A.

For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !

Also need to delete unnecessary 'page headers' throughout as in rows 2-6

View 2 Replies View Related

Excel 2010 :: Comparison On Two Columns

Jun 20, 2013

I have two columns (A and B) with the same data. Column A is missing a value that is in column B.

Column A has 11330 rows whereas Column B has 11331 rows.

I am trying to do a comparison on the two columns to determine which value is missing from Column A.

I am using Excel 2010.

View 3 Replies View Related

Excel 2010 :: Unable To Cut Columns?

Jul 25, 2013

I could swear I used to be able to cut and paste columns in Excel 2010, but for the past week I haven't been able to. When I click on a column and do a right click, "CUT" is greyed out. I can cut any section, but not a whole column.

View 3 Replies View Related

Excel 2010 :: Lookup With Different Columns

Mar 11, 2012

On my "Order" sheet, I enter an account no. that looks up the company name, address, etc on my "Accounts" sheet. Below the address info on the "Accounts" sheet is a list of the salespersons with their email address.

After entering the account no., I want to enter the salespersons name and automatically look up their email address. I can make the lookup function work if I tell the formula which column the salesperson is located in, but am lost how to write the formula to locate the salesperson based upon the column no. that contains their company info.

It's not pretty, but this works.

=IF(F1"",HLOOKUP(F1,ACCOUNTS!A1:Z100,ORDER!G9, FALSE),"")

F1 - contains the account no. entered on the order sheet.

Accounts!A1:Z100 - range containing columns of account info.

ORDER!G9 - This is the row number value I obtained by using a match function to lookup the Salespersons name entered in cell F8, then I add 1 to render the value of the row that contains the email address located directly below the salespersons name.

=(MATCH(F8,ACCOUNTS!B1:B100,FALSE)+1)

Herein lies my issue, the salesperson name will not always be in Column B

I can write another Match to obtain the column no, but then I am using another cell to hold a calculated value for my lookup statement.

ACCOUNTSABCD1CODACCT1ACCT2ACCT323 COMPANY 1COMPANY 2COMPANY 34ADDRESS ADDRESS ADDRESS 5   CITY STATE ZIPCITY STATE ZIPCITY STATE ZIP6TELEPHONETELEPHONETELEPHONE78910TOM JONESJANE DOEMARY SMITH11tjones@company1.comjdoe@company2.commsmith@company3.com12SALESPERSON 2SALESPERSON 213salesperson2@company1.comsalesperson2@company2.com14SALESPERSON 315salesperson3@company1.com
Excel 2010

In words, Find Mary Smiths email address, she works at the company with the account number in cell F1 on the order sheet.

View 6 Replies View Related

Excel 2010 :: Matching Between Columns

Apr 19, 2012

I am using Office 2010. I have a list of names in column A and a list of URLs in column B and I'm trying to find which ones have a match or partial match at least.

ex. (the name is in column A and the URL is in column B)

1 youtube www.youtube.com
2 Mr excel www.mrexcel.com
3 Wine Lover www.winelovers.com
4 Brian Jones www.ilovecats.com

In the example, I would like to highlight (or something) #s 1,2,3 as matches.

View 2 Replies View Related

Excel 2010 :: Compare 2 Columns With Dates?

Dec 11, 2013

I have 2 columns directly next to each other each containing dates.

Example 1:
F2 = 5/23/13
G2 = 5/23/14

Example 2:
F3 = 6/6/13
G3 = 4/11/14

I wanted to do a conditional formatting that would highlight the cell in
column G if the Month/Day doesn't match the Month/Day in column F

Is there a way to do this with Excel only looking at the Day/Month and ignoring the year??

View 3 Replies View Related

Excel 2010 :: Separate Two Rows To Two Columns?

Feb 26, 2014

I am trying to separate two rows to two columns. How can I do this for +200 data. For example,

1
Apple
2
Ball
3
Candy
....
I need this to be:
1 Apple
2 Ball
3 Candy
and so on.

show me how I can do this for Excel 2010.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved