Format Table & Add Sum To End Of 2 Columns

Mar 1, 2008

I have a code to apply formatting to my selected cells (a border and background color) as well as sum in rows in columns C and D. I have data in A1:D1508. The first row is titles. Column A has item codes, B is item titles, C is 2007 sales and D is 2006 sales. All the items are grouped by categories. For example A1:D7 is category 1 with row 8 containing a total in column C and D, A9:D14 is category 2 with totals in row 8. Not all the categories have the same number of rows. This code is giving me a Run-time error '1004': Application-defined or object-defined error. I have searched the forums and can't find what is wrong with my code.

Sub CustomFormating()
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With...............................

View 9 Replies


ADVERTISEMENT

Format Pivot Table Columns?

Oct 24, 2011

How can you visually format a column with multiple values in a pivot table? Say you have month in the column section, and three values in the value section: budget, actuals, variance; I want to visually seperate each month's set of data.

View 1 Replies View Related

Convert The Data Shown In Table 1 To Table 2 Without Rearranging The Columns And Rows

Sep 11, 2009

Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.

Table 1

Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348

Table 2

City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B

View 2 Replies View Related

Match Columns Of One Table To Another Two Columns Of Another Table

Jun 27, 2007

I need to Match to columns of one table to another two columns of another table, both tables are on the same worksheet.

One table has 500 rows more that should not be, I need to find out which of these 500 rows should be deleted, so the two tables should be same. So the batch number and corresponding amount should tie to another tables batch number and amount.

View 9 Replies View Related

Table In A PDF And Put It Into Format

Aug 11, 2008

What is the easiest way to take a table in a PDF and put it into Excel format? I need to automate a report from a PDF table and manually entering the numbers into Excel is doable size-wise but is dangerous because the likelihood of an error is pretty high.

All I have is Adobe Reader 8 and Excel 2007.

View 9 Replies View Related

Format For Table

Sep 1, 2009

I have attached a chart I have set up, but i was wondering if i am able to link the 'total bought' chart on the right hand side with column B 'owner name', so that it automatically adds up how many rows each name is in. I have set the 'total bought' chart up manually.

View 5 Replies View Related

Saving Table In Csv Format?

Nov 27, 2012

I created a table in a Workbook, but need it to be in CSV format. Every time I change it to CSV I lose the table. how to create and save a table in CSV format?

View 1 Replies View Related

Table Format Without Header?

Apr 17, 2014

I need to format my data without changing my header or adding a header without Column1, Column2...

I need to do this through VBA for two tables in columns A through C and E through K.

formatting every rows DARK and LIGHT manually because the data order will be changed?

View 3 Replies View Related

Format A Pivot Table

Oct 7, 2009

Every time I make a pivot table I change the formatting ie column width but when I press refresh it goes!

View 9 Replies View Related

Converting Xml To Table Format

Oct 9, 2006

I want to covert XML file to format of a table (excel).

View 2 Replies View Related

Can't Format Correctly A Pivot Table

Feb 6, 2014

formatting the sub-row header of the table also changes one of the columns.

View 6 Replies View Related

Creating Pivot Table Format?

Jul 21, 2014

I have a table (attached example with only a SMALL part of all data) I would like to transform this into pivottable format without copying and pasting. In row 2, all of the numbers pertain to years. Column B has the number of years in force for each property account (Column A). I

View 2 Replies View Related

External Table Is Not In The Expected Format?

Feb 2, 2010

I am receiving an error from the Microsoft Query wizard when I try and create a query of an xlsx or xlsm file. The error is "External table is not in the expected format."

Why this error is coming up and how I can correct the file so that I can write a query to pull info from a Named Range in the file.

I have written some code to duplicate the Microsoft query using ADODB but get the same error message when the Open connection line runs.

View 3 Replies View Related

How To Chart A Table To A Specific Format

Feb 2, 2012

I'm looking to generate a chart out of a table of data. I want the Y-axis to show a percentage return on an investment.

On the x-axis I want to have a range of scenarios from a market average return of -40% to +40%. Obviously, I wish for 0% to sit in the middle of the x-axis.

I'd want to have 3 lines observable in the chart. I'd be looking to build the above out of a table almost exactly like this:

Market ReturnDeposit90% Model100% Model403.51412303.51211203.5910103.57803.543-103.520-203.5-1-3-303.5-4-5-403.5-8-9

View 3 Replies View Related

Format Data Table In Chart

May 29, 2006

how to change the orientation of values in data table values (x axis) by 90 degree in the attached sheet

View 3 Replies View Related

Calendar Format Table In Drop Down

May 10, 2007

Can we get the Calender format when i use drop down and select and date from the calander table.

View 2 Replies View Related

Automatically Format Pivot Table

Jan 23, 2008

I have a sheet that comes from a pivot table that I have to format each month. It's a pain I was hoping a macro might be able to do. I was told this is the place to come for this. I have attached the the sheet unformatted and formatted. Note, there are 2 more columns on the finished sheet I add in to show totals. Also note col A has to be re-ordered each time (Safety, Environmental, Security, Vehicles, Operations).

Not sure about the complexity of this. Let me know. And let me know if I need to supply more info.

View 3 Replies View Related

Pivot Table - Format Date In A Cell

Jun 7, 2013

I'm trying to copy the value of one column to another, column A has date and time in this format: 6/1/2013 6:35:00 AM and I'm trying to get ONLY the dates to another column so that when I do my pivot, I can sort it out by date. I tried =A1 and I can format the column to how I want it but the value of the cell still remains in date&time. Is there any formula I can use? or should I change anything in my pivot table?

View 4 Replies View Related

Change Pivot Table Format And Count?

Feb 20, 2014

I would like to have a pivot table that will count the number of responses I get for a certain question

For example on the attached book I would like it to look something like:

a
blue 1
red 1
yellow 1
green 1
aa
green 2
yellow 1

and so on, I am sure this is possible just not sure how to make it happen.

View 5 Replies View Related

Converting Data Table To Matrix Format

Jan 28, 2009

I am trying to convert the data I have to matrix format. I have attached a sample of the data in the file I have attached. The data is in the worksheet "Original" and the matrix format that I am trying to convert is in the "Transformed" worksheet. Can someone please help me with the VBA code for going from "Original" to "Transformed"?

View 11 Replies View Related

Excel 2013 :: Using Macro To Format Table?

May 14, 2014

I am using excel 2013. I have the table look like the above, in the diagram 1, i want to have the macro such that it can be formatted to the 2nd diagram. Can this be done using macro ? [URL]

View 2 Replies View Related

Pivot Table Detail Report Format

Jul 27, 2004

When you create a pivot table and double-click on the output of the pivot repot you access the detailed info that make up this particular number.

Is it possible to preserve the format of the raw data file that has been used to create the pivot table output?

For instance, the raw data has $. When I create the pivot table I can format the table to reflect the $. However, when I double click on the pivot table number where I have the $ I access the detail info but the format (in this case $) is lost.

I called Microsoft and told me it is a flaw in Excel. But, I'd like to check with you before I give up.

If I can't do that. I have an alternative: double click on all the numbers of the pivot table then format the sheets to include the $ and then create a hyperlink from the pivot table to the detailed report tab.

View 9 Replies View Related

Format Pivot Table Drill-Down Results

Sep 8, 2006

Is there any way to change the format for pivot table sub data? Or maybe use a marco for it. The data table that appears when you double click any field on the pivot table range.

View 6 Replies View Related

Format Data For Pivot Table Report

Dec 27, 2007

I am building a tracking process for marketing and relationship management purposes. My company has a database (Advisor's Assistant - which the server is on site) that is for lack of a better term very limited. I have tried to identify if we have the capability to use SQL in excel to pull the information we want but that avenue looks bleak, since my co-worker that is pretty tech-savvy has had very little luck working with the database provider to get to information we want.

Anyway, I have determined that I can get the information I am interested in by way of several reports that the current database will do and printing them to a .pdf file. Then by way of a program called Able to Extracted I can get them into an excel format. The problem I am trying to solve is using excel to pull the information I want out of these twice converted reports into a format that means something in excel.

Only the reports reach excel they have many empty cells and some of the information is offset and does not follow the same pattern as you scroll down through the report. I have attached part of one of the reports. I would like to automate the process of searching the data and creating a new format that I can use a pivot table to create reports off.

View 2 Replies View Related

Conditional Format Table Row If Entries Missing

Feb 26, 2008

Please find the attached sheet.

I just want to know whether is it possible to condional format multiple cells based on multiple cell values.

Eg: In the attached sheet,when a driver is standby and the vehicle no column should be empty,if accidently a data entry is done in vehicle no column it should highlight the whole area (ie,from A3 to E3).

View 8 Replies View Related

Replicate Table Format Multiple Times

Feb 29, 2008

I have a single sheet broken into multiple sections of 10 rows (see attached). The first 5 rows of the section are formatted and I need to Insert 5 rows underneath and format them the same. I can't work out how to do this for the different sections (ie: add 5 rows to section 1, then section 2 then 3). These functions will be on separate buttons to use multiple times. I would have used multiple sheets but the project calls for a single sheet representation.

View 4 Replies View Related

Addresses In Different Format To Columns

Apr 10, 2014

I have tons of addresses that I need to separate into different columns. It should be easily done by using Text to Column but the format is different so it didn't work.

1198 W 1520 N
CLINTON, UT 84015-5301
1198 W 1520 N
CLINTON
UT
84015-5301

798 HOMESTEAD AVE
HOLYOKE, MA 01040
798 HOMESTEAD AVE
HOLYOKE
MA
01040

16765 KENUIL CT
BRIGHTON, CO 80603-8481
16765 KENUIL CT
BRIGHTON
CO
80603-8481

The addresses are copied from a website so it is formatted as 2 lines. Tried to copy and paste (values only) to another sheet and the format was like this:

1198 W 1520 NCLINTON, UT 84015-5301

No space between "N" and "CLINTON".

View 2 Replies View Related

Format Data Into Columns

Jun 15, 2007

with "Selecting equally spaced cells and putting the info into columns...". I work for a company that needs to get the info from this site here:

[url]

I'm trying to get it all organized into one nice and neat excel spreadsheet. I researched things myself and I found that you can just insert the source code into excel and excel will automatically put the tables into cells. My only problem is that, if you noticed, on the site each and every person has their own "First Name", or "Last name", "Email", or whatever. I just want one "First Name" in the column A, the "Last name" part in column B, and so on and so forth. Is this possible to do in an easy method because I'm seriously not going to copy and paste the info myself, all 36,000+ times.

View 9 Replies View Related

How To Conditional Format Data In One Table Based On Values From Another

Aug 12, 2014

how to conditionally format the data in one table based on the criteria specified in another table on a different worksheet.

The Table I'm trying to format has a series of Products and Volumes that'll be available on different dates. Table 1.jpg

The Criteria table has each product and a corresponding Key Data. Table 2.jpg

All I want to be able to do is have the cell corresponding to the criteria to be highlighted but can't for the life of me figure out how to do it. If it makes a difference I'm working offAttachment 338501 a Mac. Excel Version 2011 14.4.3

View 3 Replies View Related

Copy And Paste Table Into String In HTML Format

Dec 22, 2008

I want the same affect as copy a table and pasting into the email. So i guess i need to copy a range and save as html in a string. It seems like it would be simple since its just copy and paste into the email, but i've been unable to find anyting on it.

View 3 Replies View Related







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