i have a list of about 50,000 songs
my layout is like so...
artist1 song1 disc#
artist1 song2 disc#
artist1 song3 disc#
artist2 song1... you get it.
what i want to do is reformat it so it looks like this
artist1
song1 disc#
song2 disc#
song3 disc#
artist2
song1 disc#
so the artist and song could be in the same column with the song indented or if someone knows another way that works too.
I have a time series chart ( dates on x axis , with the data values on y axis ). It is a line chart. So column A has the dates, and column B has the values. Both have length 100. Now, column C can be considered to be the "flag" column. If a cell in Column C has the flag set to true , then the corresponding date/data values in the chart needs to be highlighted or formatted differently
I tried playing around with conditional formatting of x-axis but could not make any progress. Do I need to create a new data series with the C column?
Also given that there are 100 date values, I dont want all x-axis points to be listed on the chart. I guess excel by default shows the date points as the user increases/decreases size. But I dont want the specifically formatted date points to be skipped .
I am trying to create conditional formating of a cell based on the value of another cell whish is linked to another sheet. I an using the "The formula is =$AA$30=1" where the cell AA30 is the cell linked to the other sheet. the problem is I have to do thiss for approx 200 cells ie =$AA$30=1, =$AB$30=1, =$AA$31=1, =$AB$31=1 --- =$AZ$42=1. Do I have to enter the formulea for each cell or is there a way to automate this?
View 2 Replies View RelatedI have a workbook where Sheet A is a Master Order Form. Employees will input the quantities they need in Column C. The sheet will be otherwise protected. Sheet B is a "printable" order form that only contains the rows from Sheet A where the Quantity on Sheet A is not blank and is >=1. This will allow me to print sheet B with no blank rows for the items I don't need.
I found the following formula which claims to be able to do what I want:
=IF(ROWS($1:1)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($1:1),C:C,0)))
I can't get it to work correctly across two sheets.
I've attached Sample of spreadsheet for review : HDForm_Test01.xlsx
I run an online store where all the product details are handled by a .CSV file and naturally all the information has to be exact or it won't work at all especially UPC codes and product codes. However whenever I open the .CSV file to edit anything it auto formats several bit of information including several UPCs and product codes that begin with one or more 0's. This among many other problem I have had are all caused by excel's auto formating "tool" and I find it of absolutely no use. Is there any way at all to disable this "tool" so it will stop messing with my data every time I need to make a product change? (I have tried using the '0 trick but when I save the file and open it again later all the '0s are gone again)
I am running Excel 2007 on Windows Vista SP1
I have an excel document that has one columb (I) of cells that changes color depending on a specific date (columb h) . The cells that have conditional formating to change the row color based on where it is ( =MOD(ROW(),2)=1), stays blank and the proper color until columb A is filled in. The others show up red. How would I code/format the lines that aren't included in the conditional formating to always show white when no value is entered in columb a.
View 9 Replies View Relatedcan i format the cells that i have made grey for this photo to automatic put : between numbers i put in like the time.example if i would put in a cell 1200 than it would be like 12:00 is that possible i have been through all the time formats in the format cells options but i always get this result ###.
the other ? is can i take the S away when i have formated cells to show kilograms so it would be like 550 kg instead of 550 kgs ?
I need to replace a "." with a "," to stay in the right format, by using VBA.
if i make a macro to do it, it will work for almost all cells but for a few it doens't work!
Then, if i select the cell with mouse and replace "." with a "," it works normaly.
My question is why it doesn't work for a few fields if all the data have the same format?
I have h26 and h27 both can generate a number
if h26 is a number i need f26 to formate in dollors
if h27 is a number i need f26 to be general formate
in the cells h26 and h27 they will already be in that format how can i get them to go to f26 like that
******** ******************** ************************************************************************>Microsoft Excel - PROTOTYPE2207.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF26=
EFGH26s1*$1.0027***1Weekly Stats*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have excel 2000 and conditional formating has only 3 options. Is it possible to use a formula so that if a critria matches, the cell chagnes colour?
View 9 Replies View Relatedi have the following table.
A1A2A3A4116747001169470011706300117075011707501170710511707100
the formatting which is in A1 I want that in A4 I will automatically appear like above (at the moment i do this formating manaually)
I have a column with addresses and I need to separate it into columns, the main problem is that sometimes the listing has house number sometimes it does not some time it has town but, some time it does not and the post code is not separated from the county - if no house number then that column is to remain empty.
Eg; (rows 1-2-3)
Bull Corner, Rainham Road South, Dagenham, Essex RM10 8YT
Bernard Road, Romford, Essex RM7 0HX
420, Eastern Avenue, Gants Hill, Ilford, Essex IG2 6NS
Need to enter them into the right columns:
House number - Road - Town - County - Post Code
how to use the conditional format tool, but now the company i designed the spreadsheet for, wants me to implement a change. I'm attacking a copy of the spreadsheet so you can see the problem more easily. Now what I've been asked todo is the following.
1. When the stock quantity (Column H) goes below the re-order level (Column K), they want the entire row to change font colour from blue to red. Now I know how i can change the colour of one cell, like ive done in the example, but I'm not sure how to change the entire row colour. If this can be done with conditioning formating then great, but if not then i'm stuck, and relying on your generosity in helping me out.
i have 2 columns the first is the transaction number and second column is the description
i want to make that all even transaction number will highlight the whole row... how do i make that with conditional formatting? or are there other alternatives?
I am using this formula in a Conditional Formatting
=VLOOKUP(G1,Dies5PE,1)>0
Where "Dies5PE" = a named range
it doesnt work.. if I change the"G1" to a "$G1", everything in the range changes to the conditional format.
I have a large X-Y-axis table with about 200 entries in it. There are 6 different entries possible. I now want to give each possible entry in this table a color for making it easier to read.
I could write a formula in conditional formating, so that it works for 3 colors. The problem is now that i can make this only for 3 different colors in conditional formating of excel. Is there a possibility for making this for 6 colors?
I have two columns from which criteria must be satisifed. column A denotes gender (m or f) and column B denotes a number of points. I want to sum those males who achieve >4 in one cell and those females who achieve >4 in another cell.
View 3 Replies View RelatedI want to set up formula in conditional formating so that when:
$AF6 = "CA" the color is red
or
if $AF6 = ("GA","ME","PR") then the color is blue
what I came up with is Condition 1
= ($AF6 ="ca") [which will turn the cell red]
Condition 2
="OR($AF6={""GA"",""ME"",""PR""}, "")" [which doesn't do anything]
is there a formula that i can use to check if a text in a cell is in bold?
i.e something like :
=isblock(A2)
to return true if the text in A2 is in bold
I have a spreadsheet
B4:B193 - place
D4:D193 - value
F4:F193 - name
H4:H193 - place
J4:J193 - value
L4:L193 - name
N9:N93 - place
P9:P93 - value
Q9:Q93 - name
What i am looking for is this:
If value in cell in column D is >=90 then relevant cell in column B, D and F background green
If value in cell in column D is less =80 then relevant cell in column B, D, and F background dark blue
If value in cell in column D is less =70 then relevant cell in column B, D, and F background light blue
If value in cell in column D is less =50 then relevant cell in column B, D, and F background orange
If value in cell in column D is less
This thread:
http://www.mrexcel.com/forum/showthr...itional+format
Asks almost exactly the question I have, but I can't make heads or tails out of the answers, I'm guessing becasue I have Excel 2003 and not 2007 as mentioned in the replies.
In column "A" I have a number; column "B" the formula =A1 copied on down and in column "C" either a 1 or 2 or a 3.
If the number in column "C" is a 1 then the font in the same cell in Column "B" should be black, 2 Blue and 3 Red.
I'm thinking this should be very simple, but so far it's not turning out that way. )-:
I would like to format a cell so when i enter a six digit number, such as 123456 it would appear starting with a QW then the year, month and day in the format of yymmdd and them the six digit number so it would appear as QWYYMMDD123456, or since today is 01/09/09 is would be QW090109123456
I would like the yymmdd field to be taken from the main location where the date is entered and the QW can be in another field so when I go to another blank field and enter a six digit number, for example 123456 it will be changed to QW090108123456. I hope you can follow this and that I explained it in a matter that is understood.
I have been trying to perform a conditional format using a date value.
Example:
Condition 1 =IF(L14
Can this code be simplified? I currently have this code repeated 15 times, I need to color the ranges yellow and then unlock them. So for now I am repeating the code below for range ("C19:N26") and then 14 other ranges.
And if you have suggestions on cleaning up the code below please let me know, I would like to know the proper/most efficient way to write code.
Sub FormatingBudgetFiles()
'Colors each Forecast cell yellow that the end user can change
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
With ActiveWindow
Range("c14:k14").Select
End With
Is it possible to format cells so that inputed tel. numbers are all have the same format and not just appear the same, regardless of how they were typed in?
i need it so the column can be sorded by phone #.
if its possible to run conditional formating on graphs, I can do this fine on the actual data
i.e. Have a bar chart with a number of bars all the same colour and just wanted to have the bars change to red if they drop below a certain percentage.
only running excel 2000
I often get spread sheets to work with that have a non visable/printable character (') in front of them. I haven't been able to find a way to get rid of them other than editing each cell and removing it. I am sure there must be a way. I have attached a small sample of the cells in question.
View 3 Replies View Relatedhow I would be able to change the formatting in a userform listbox to Number format (i.e #,###.0). I am currently pulling data from an access database to populate an excel Userform listbox but the numbers come in as Text. I would like to change this to a Number formate.
View 9 Replies View RelatedI have a spread sheet that I am trying to format the data from. It contains five columns, 2 of which are important for this formating.
-Column A is Account name, and there can be a varying number of accounts with the same name, as well as a varying number of different account names (they are sorted so all similar accounts are together)
-Column E is an indicator/used for a calculation, either 0 or 1.
I need to do two things ....
Is there an easy way to change from A to B ??, see the attach. This is just an small picture of the big one
View 3 Replies View Related