Alternate Colors In Table With Table Style When String Value In Column A Changes
Feb 25, 2014
When I add a table in Excel, I can choose from Table Styles, different styles that set one color for even rows and another color for odd rows.
Is there a way to say to this Table Style that assign one color for rows that have the same string in column A in consecutive rows and another color when the string in colum A changes?
I mean,
If A1= XYZ, A2=XYZ assign blue to row 1 and 2.
If A3:A9=FTG assign green to rows 3 to 9.
If A10:A13=LLKF assign blue again to rows 10 to 13.
If A14:A22=WUR882 assign green again to rows 14 to 22.
and so on.
View 2 Replies
ADVERTISEMENT
Mar 29, 2007
I have a data table and want to create six charts. In theory pivotcharts would be the way to go. However every time I refresh the charts they revert to excel's default chart and all my colour scheme and nice layouts are lost.
View 3 Replies
View Related
Feb 19, 2014
I'm trying to find a way to alternate between two colors (white, grey, for example) in a stacked columns plot. Each stacked column should begin with a grey layer then white, then grey and so on. These plots will be created and changed frequently so I cannot manually do this every time.
View 1 Replies
View Related
Jun 11, 2014
I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name
1/01/2014
2/01/2014
3/01/2014
6/01/2014
7/01/2014
8/01/2014
9/01/2014
10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
View 1 Replies
View Related
Jun 10, 2013
I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?
View 3 Replies
View Related
Apr 30, 2014
I have some excel sheets that are formatted like the following:
COMPANY | TOTAL | R | G | B
company1 | 10 | 255 | 000 | 000
company2 | 20 | 000 | 255 | 000
company3 | 30 | 000 | 000 | 255
...
and so on...
My question is that I would like to have a macro that runs on this basic file and creates a bar graph with the data. Then it utilizes the RGB values in the columns to change the specific bar for that row. So setting the r, g, b as variables corresponding to the columns in the sheet. Also there isn't a preset number of rows in the files.
[URL]
View 13 Replies
View Related
Oct 5, 2011
There is pivot table for some reason the last column (YEAccts), which sums all the other columns values is exluding the 1st column for some reason. This "YEAccts" shows up fine in the pivot table "field list". And I followed the source for this and it's a table in a different tab that pulls data from an access query connection.
The name of the column that is summing up the rest of the columns is "YEAccts", but for the life of me I can't seem to find where this column is in the source table tab or even in the access query where the data is being pulled from.
How to find this "YEAccts" column? Also, why would it exclude not summing up the data in the first column?
View 2 Replies
View Related
Mar 13, 2013
Table 1
January-12
February-12
March-12
Table 2
Sr. No
Name
Dep
Lates
CL / SL
AL
Lates
CL / SL
AL
Lates
CL / SL
AL
[Code].....
View 1 Replies
View Related
Jul 15, 2014
I have a large table that I want to reconstruct. For simplicity sake, let's just says it's 3 rows (excluding headers) by 3 columns.
Item Description
1/1/2014
1/2/2014
1/3/2014
Cheese Burgers
2
3
4
Hot Dogs
5
12
6
Beverages
2
5
3
I want to reconstruct it so that the column headers become values in the table. The table headers are dates, in this case, if that gives clearer picture. So the new table would have 9 rows, (3 rows of data, excluding the header times four columns).
Item Description
Date
Quantity
Cheese Burgers
1/1/2014
2
Hot Dogs
1/1/2014
5
[Code] ....
The above example is sorted by date but I would be indifferent if it's sorted by the Item Description.
Is there an easy way to do this? Pivot possibly? Again, my data table is large: 36 rows x 181 columns. Using the copy/paste/transpose feature is pretty impractical.
View 3 Replies
View Related
Nov 5, 2008
I want to accomplish something like this but slightly different:
[url]
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
View 6 Replies
View Related
May 2, 2013
I have two Tables, Table1 and Table2, in a single Excel spreadsheet. Table1 is our master log, and Table2 contains only Provider Names, Contact Names, Phone Numbers, and Email Addresses. I need to compare column K from Table1 with column AT from Table2. Whenever Excel finds a perfect match, I need Excel to copy AW:AY to AB:AD. For each value in column AT, there will be several identical matches in column K.
I have tried to implement vlookup() and index(match()), but cannot figure either one of them.
Table1 is almost 1500 rows long, while Table2 is not quite 80.
View 3 Replies
View Related
Jun 5, 2014
I'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?
I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.
i.e.
A B C D
territory 1 12345 54321 11222
territory 2 22222 33333
territory 3 44444 55555
and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.
View 3 Replies
View Related
Mar 19, 2014
I have a list of parts in a table. I am manually copy/pasting this list to another sheet in my workbook and then using DATA→Remove Duplicates to get a shortened list. Is there a way to automate this process?
In the attached workbook I want the yellow cells to auto-fill for me.
I get a lot of lists in the format on the left hand side so I want excel to automatically format them for me so that I can use the output on the right.
List Sorting Help.xlsx
View 1 Replies
View Related
Aug 29, 2007
I currently have a table with a range of headings (row & column), and the necessary data for it. On a new worksheet, I have a table with only a few of the headings, and I was wondering if there was a macro that would automaticlly match the headings of the new sheet with the other table, and fill in the ncessary dat, as on the other sheet.
View 3 Replies
View Related
Sep 18, 2008
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
View 9 Replies
View Related
Dec 23, 2013
I'm trying to get excel to check a cell against a table of numbers and if it finds any of them to replace it with the correct number.
Basically if the cell contains any of the numbers in the "Do Not Use" column then it should display the corresponding entry from the "Replace With" column?
see attached spreadsheet. Cells in orange are where I want the formula to go...
Test2.xls
View 7 Replies
View Related
Aug 4, 2009
I have a list of location names that may be repeated within the list. I also have possible blank cells within the list. I would like to filter them automatically into another table without having to use the autofilter or any macros to keep things simple.
How could i do this by removing blank cells in the new table and having only uniquely listed names ie get rid of any duplicates.
View 9 Replies
View Related
Oct 6, 2008
I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,
Standard Kitchen
Standard Master Bath
Standard Hall Bath
Optional Kitchen
Optional Basement Bath
First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.
View 3 Replies
View Related
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
Oct 26, 2012
Is there any way to modify the 1st style under the Light category? I like it because it's the most simple but it won't allow me to Modify or Duplicate. Is there a work around to duplicating/copying it? I've tried manually creating a new style but I can't get it to get the lines fills correctly.
View 2 Replies
View Related
Jun 12, 2013
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
View 2 Replies
View Related
Feb 1, 2010
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
View 14 Replies
View Related
Mar 14, 2014
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
View 2 Replies
View Related
Apr 14, 2009
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
View 3 Replies
View Related
Mar 27, 2012
I have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.
Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.
View 1 Replies
View Related
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
Sep 8, 2009
Data Table including-
List of Identifying Code Numbers for customer invoices
Multiple repetitions of individual Identifying Code Numbers in list
Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.
- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.
i have tried combining Max and Large functions with Vlookups etc.
View 9 Replies
View Related
Apr 26, 2012
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
View 3 Replies
View Related
Dec 17, 2012
I have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets:
ie:
asset no
description
received by
day
[Code].....
View 2 Replies
View Related
Jun 2, 2013
I created a lookup table that works quite well. It even has if statements in the LookUp Formula. However, I have to update the table it pulls the information from each day. I wind up recreating the range each time because the table always has more rows each time. Is there a way I could just paste the table in each day and not have to change the range? The columns never change.
View 9 Replies
View Related
