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?
I want for my set of data. The attached .xls is pretty straight forward: the first column is a list of people (identified by their customer number) and the second column is the URL they visited.
Since many people went to multiple pages, there are dupes between the two columns, but all of the rows are unique. What i am looking for is a table that shows as a header the URL (just one) and then the list of people that went to that URL under the header. So it's really just one column of information. It seems like a perfect task for a pivot table.
I sometimes import data from an ERP Program to Excel. I want to write a Macro which will make the imported data more presentable (For example: draw borders for all the cells, give the heading a fill color etc. etc.). To do this I need to select the heading row. The problem is that each time there are a different number of columns.
How do I program the macro so that it will color all of the header? I saw a macro that does this on a pivot table and I want to do thae same on a regular table.
It is possible to make a table in the header in excel like word? If yes, how? I think my question can be answered her, but the links don't work anymore
http://ozgrid.com/forum/viewthread.php?tid=2952
http://ozgrid.com/forum/viewthread.php?tid=4391
I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook
Sub Format()
Dim VarFileName As String
Dim VarPath As String
Dim VarSavein As String
Dim wsheet As Worksheet
VarSavein = Sheets("sheet1").Range("C2").Value
VarFileName = Sheets("sheet1").Range("A2").Value
VarPath = Sheets("sheet1").Range("B2").Value
Workbooks.Open VarPath & VarFileName
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:= Array(1, 2), TrailingMinusNumbers:=True..........................................
Perhaps I missed something in the basics of Excel as to how a table's column header is formatted.Also, my date format is dd/mm/yyyy
When using a basic formula, such as below, in A1 and Cells A2 and B2 have the dates 1/01/2013 and 2/01/2013, this code works fine.
VB : =If(A2<=Today(),"Yes";"No")
BUT, when you convert those two dates to a table, the formula with post No.
However, if you make another formula as show below, the correct day will come up.
VB : =Text(A2,"dddd")
When auto filling the dates in the row, without a table, the dates increase per day.
When auto filling the dates in the row, with a table, the dates increase per year. (And yes, even when 3+ dates are inserted, highlighting those 3 and then autofill.)
Why does a column header change the format and to what format is it changed to?
Why do some formulas output accurate date based on the date in the column header and others do not?
Why do the dates increase per day without a table and per year with a table?
I have this VBA code running in cell A1 which gives you a yes/no message box when you try to change the selected cell. I am trying to have the Target.Address = 'Specific Header Cell in Table' but I am not able to develop the code to do so. Here is the code:
[Code] .....
The main reason I have this message box is to prevent the user from changing the cell in the table because the titles in the tables are linked to macros. If they are changed, the macros will not run. Also, I am aware there are codenames for the excel sheets in a workbook. If there are codenames for the header titles in an excel table, I could link my macros to the codenames. As a result, I would not need this macro for the message box.
I have a excel worksheet which i am exporting into a word document through a macro labeled "Go". Basically it will query the excel sheet and the word document i have, will take the values and put them in the correct placing.
My problem is...
I know that excel will use the first row as the table headers, but what if i want it to start at like row c? and use that row as table headers. How would i go about doing that?
I am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer.
What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that.
So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.
I have a list of S/N with Pass/Fail result next to it. A specific S/N can appear several times in the list:
SN...Result
-------------
111...FAIL
111...FAIL
111...FAIL
111...PASS
222...PASS
333...FAIL
333...FAIL
When creating a pivot table of SN vs. Result (count) I get:
****|FAIL...PASS
-------------------
111..|...3.....1
222..|..........1
333..|...2
I want to get the FAIL count of SN in which PASS > 0 (First two rows)
BUT I can not figure out how I can filter based on the values of the pivot table itself (rather than the values of the original list). When I try to use 'Result' field as report filter, I can not get it as a Column label (and vice-versa). In this case I get:
I get:
****|PASS
------------
111 | ...1
222 | ...1
Desired:
.......|..FAIL..PASS
-------------------
111 |...3...1
222 |........1
Desired (alternative):
......|..FAIL
-------------
111 |...3
222 |...0
I have a table which is extracted from a database using 2 tables.
1 Header and 1 details table.
I want to display the latest date in column E.
As Column A,B,C is the header table and column D,E is the details table.
Can this be done in vba or a pivot?
I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.
eg: sheet 1
Name
Cell
Region
John
111-2222
[Code] .......
The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.
I have a table showing accesssibility of given fruits in some particular shops. What I need is to list the fruits available in every shop right after the shop's name, but skipping any empty spaces (i.e. unavailable fruits. See the example, it's manually entered though).
View 2 Replies View RelatedExcel 2003
I have a table with a header row of scores 1-10 in B4:K4.
In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.
What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...
This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?
Is there a way to link a header or a portion of a header to a specific cell?
View 14 Replies View RelatedI've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.
in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.
In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.
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.
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 RelatedI 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 RelatedEvery time I make a pivot table I change the formatting ie column width but when I press refresh it goes!
I want to covert XML file to format of a table (excel).
View 2 Replies View RelatedI 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...............................
formatting the sub-row header of the table also changes one of the columns.
View 6 Replies View RelatedI 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 RelatedI 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.
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 RelatedI'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
how to change the orientation of values in data table values (x axis) by 90 degree in the attached sheet
View 3 Replies View RelatedCan we get the Calender format when i use drop down and select and date from the calander table.
View 2 Replies View RelatedI 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.
